请教关于sql自动按条件总表拆分问题

xchjh

请教关于自动按条件总表分割的问题

http://club.excelhome.net/dispbbs.asp?boardid=2&replyid=1457902&id=341467&page=1&skin=0&Star=2

    以上例子用sql语句将总表按某列内容拆分成同一文件夹下的几个独立的分表，还有合成总表，非常好用，可是遇到了以下问题：

    我发觉将信息表中的B列换成数字比如 5 后在分表文件中会没有呢？而是文本是就有。请大家帮忙看看是啥原因？  先谢谢了。

tvrDh7ot.rar (18.33 KB, 下载次数: 76)

2008-10-15 21:22 上传

点击文件名下载附件
请教关于sql自动按条件总表拆分问题

[此贴子已经被作者于2008-10-15 21:30:58编辑过]

xchjh

请教关于自动按条件总表分割的问题

http://club.excelhome.net/dispbbs.asp?boardid=2&replyid=1457902&id=341467&page=1&skin=0&Star=2

    以上例子用sql语句将总表按某列内容拆分成同一文件夹下的几个独立的分表，还有合成总表，非常好用，可是遇到了以下问题：

    我发觉将信息表中的B列换成数字比如 5 后在分表文件中会没有呢？而是文本是就有。

有这样一个解决办法：将B列格式统一成文本格式，方法如下：

使用“文本分列”命令

　　如果数据排列在单个列中，此方法效果最佳。下面的示例假定数据位于 B 列，首行为第 1 行 ($B$1)。要使用此示例，请按照下列步骤操作：

　　1.选择包含文本的一列单元格。

　　2.在“数据”菜单上，单击“分列”。

　　3.在“原始数据类型”下，单击“分隔符号”，然后单击“下一步”。

　　4.在“分隔符号”下，单击以选中“Tab 键”复选框，然后单击“下一步”。

　　5.在“列数据格式”下，单击“文本”。

　　6.单击“完成”。

　数字现在即转换为文本。

通过以上操作，统一了B列格式，现在再运行分割宏，就可以了。

不知SQL为何要出这样的问题？为何必须要格式一致？

xchjh

Excel数字、文本混合列导入SQL Server出现的问题＆解决办法

Excel文件：

序号	姓名	内部电话	住址
1	小李	1234	……
2	小王	5678	……
3	小张	2345(国内长途）	……
……	……	……	……

如上结构的Excel文件，用SQL Server的“导入数据”功能来将其导入SQL数据库中。
结果发现在“内部电话”列中，所有带有文字的电话号，被导入后字段值全部为NULL

SQL数据表：

序号	姓名	内部电话	住址
1	小李	1234	……
2	小王	5678	……
3	小张	NULL	……
……	……	……	……

通过在SQL中设置（在导入导出中，目的方选择新建表），发现数据库默认将“内部电话”列识别为float型，因为其新建表对应字段就是float类型的。
看来是SQL认为包含文字的那些电话号码无法转换成数字，所以是无效的数据，从而采用NULL来代替。

通过查询微软网站，发现MS大意如是说：SQL在导入Excel混合数据列的时候，由于数据类型不唯一，导致SQL无法确定数据类型（看来SQL也有犯糊涂的时候）。SQL的应对之道就是统计该数据列的前８行数据中出现最多的类型，并以此类型做为默认类型。而在我的Excel文件中，“内部电话”列的前８行中的确要数纯数字格式的电话号码最多，所以SQL就把这列认为是float型的（为什么不是int型？不解）。至于此列其它格式的数据，SQL的办法是——直接扔了（汗一个。。）

MS原文：

……Excel 不会像关系数据库那样为 ADO 提供有关其数据的详细架构信息。因此，驱动程序必须至少扫描几行现有数据，才能有根据地猜测各列的数据类型。“要扫描的行数”的默认值为八 (8) 行。可以指定从一 (1) 行到十六 (16) 行的整数值，或指定零 (0)，扫描所有现有行。这可通过向连接字符串添加可选的 MaxScanRows= 设置，或在 DSN 配置对话框中更改要扫描的行数设置来完成。

但是，由于 ODBC 驱动程序中存在一个错误，所以目前指定“要扫描的行数”(MaxScanRows) 设置不起作用。换句话说，Excel ODBC 驱动程序（MDAC 2.1 和更高版本）始终扫描指定数据源中的前 8 行，以确定各列的数据类型。

……

使用这两种 OLE DB 提供程序时都应考虑的问题
混用数据类型时应注意的事项

如上文所述，ADO 必须猜测 Excel 工作表或范围中各列的数据类型。（这不受 Excel 单元格格式设置的影响。）如果同一列中既有数字值，也有文本值，会出现严重的问题。Jet 和 ODBC 提供程序将返回占多数的类型的数据，但对于占少数的数据类型，则会返回 NULL（空）值。如果该列中两种类型数据的数量相等，提供程序将优先选择数字型数据，放弃文本型数据。

例如：

•	在被扫描的八 (8) 行中，如果该列包含五 (5) 个数字值和三 (3) 个文本值，则提供程序将返回五 (5) 个数字和三 (3) 个空值。
•	在被扫描的八 (8) 行中，如果该列包含三 (3) 个数字值和五 (5) 个文本值，则提供程序将返回三 (3) 个空值和五 (5) 个文本值。
•	在被扫描的八 (8) 行中，如果该列包含四 (4) 个数字值和四 (4) 个文本值，则提供程序将返回四 (4) 个数字和四 (4) 个空值。

因此，如果列中包含不同类型的值，唯一的解决方法是将该列中的数字值存储为文本，然后在需要时使用 Visual Basic VAL 函数或同等功能的函数将其还原为数字。

作为解决只读数据问题的一种替代方法，可在连接字符串的“扩展属性”部分中使用“IMEX=1”这一设置来启用导入模式。这可强制执行 ImportMixedTypes=Text 注册表设置。但在此模式下，执行更新操作时可能会出现意外的结果。

原文地址：257819: 如何在 Visual Basic 或 VBA 中使用 ADO 来处理 Excel 数据

看到了没有？你想在前８行里多放几行文本的企图也是徒劳的。数字格式的数据一样会被扔掉，尽管数字可以毫无问题的转成文本。

DSN设置那里根本就没给我们提供加“IMEX=1”的地方，这个字符串是给ADO用的，如果你要编程读取Excel中的数据就用得着它了。不过MS说用这样的连接字符串更新时可能会有问题，这点要稍加注意。我们这里只是数据导入，根本不用考虑这一项。

而在Excel中将有问题的单元格单元格格式设为文本这一办法，事实证明，不起作用。
倒是在每个数字前加单引号可以正常导入，可问题是行数太多，不可能一行行手动加单引号。
用VBA或公式加吧，又无法判断每个单元格是数字类型还是文本类型，导致该列所有单元格都被加了引号。
而且这种方式导入SQL数据库后，还得用查询分析器写SQL语句把引号去了。

最后，最简单的一个办法——把Excel文件另存为“文本文件（制表符分隔）”，再用SQL来导入，一切OK了
为什么？因为txt文件里的内容只有文本属性，不像Excel中还有数字、货币、日期等一帮捣乱的类型。

本文选自：http://www.cnblogs.com/frostcity/archive/2008/09/22/1095484.html

这是否是最好的解释呢？

northwolves

连接字符串添加设置imex=1即可

xchjh

QUOTE:

以下是引用northwolves在2008-10-16 8:48:00的发言：
连接字符串添加设置imex=1即可

为什么有效单元格数据不显示出来？
  出现这种情况的可能原因是，默认连接中，数据提供程序根据前面单元格推断后续单元个的数据类型。
  可以通过 Extended Properties 中指定 IMEX=1
   
  “IMEX=1;”通知驱动程序始终将“互混”数据列作为文本读

 注意：
  在网上有很多同志说是加上HDR或IMEX这两个属性值后会报“找不到可安装的ISAM ”的错误。这时解决方法很简单：
  就是把连接串写成如下形式：
   
  Provider=Microsoft.Jet.Oledb.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR=1; IMEX=1;' (注意红色标注的单引号)
   
  而不要写成这样的形式：
   
  Provider=Microsoft.Jet.Oledb.4.0;Data Source={0};Extended Properties=Excel 8.0;HDR=1; IMEX=1;

xchjh

信息表中的B列换成数字比如 5 后在分表文件中会没有呢？而是文本是就有。

QUOTE:

根据版主northwolves解答：
连接字符串添加设置imex=1即可

将分割宏中的这句：

cn.Open "provider=microsoft.jet.oledb.4.0;extended properties=excel 8.0;data source=" & ThisWorkbook.FullName  '打开链接

换成以下：cn.Open "provider=microsoft.jet.oledb.4.0;extended properties='excel 8.0;imex=1';data source=" & ThisWorkbook.FullName  '打开链接

可以实现。

但问题又来了：将信息表中的第二列中的前10行改成数字后，第二列中的其它有数字、有文本，分割后，各表中第二列又只有数字了，而文本又不显示了？

[此贴子已经被作者于2008-10-18 23:59:49编辑过]

xchjh

问题分析
　　产生这种问题的根源与Excel ISAM[3]（Indexed Sequential Access Method，即索引顺序存取方法）驱动程序的限制有关，Excel ISAM 驱动程序通过检查前几行中实际值确定一个 Excel 列的类型，然后选择能够代表其样本中大部分值的数据类型[4]。也即Excel ISAM查找某列前几行（默认情况下是8行），把占多的类型作为其处理类型。例如如果数字占多，那么其它含有字母等文本的数据项就会置空；相反如果文本居多，纯数字的数据项就会被置空。
　　现具体分析在第1节程序代码Extended Properties项中的HDR和IMEX所代表的含义。HDR用来设置是否将Excel表中第一行作为字段名，“YES”代表是，“NO”代表不是即也为数据内容；IMEX是用来告诉驱动程序使用Excel文件的模式，其值有0、1、2三种，分别代表导出、导入、混合模式。当我们设置IMEX＝1时将强制混合数据转换为文本，但仅仅这种设置并不可靠，IMEX＝1只确保在某列前8行数据至少有一个是文本项的时候才起作用，它只是把查找前8行数据中数据类型占优选择的行为作了略微的改变。例如某列前8行数据全为纯数字，那么它仍然以数字类型作为该列的数据类型，随后行里的含有文本的数据仍然变空。
　　另一个改进的措施是IMEX＝1与注册表值TypeGuessRows配合使用，TypeGuessRows 值决定了ISAM 驱动程序从前几条数据采样确定数据类型，默认为“8”。可以通过修改“HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel”下的该注册表值来更改采样行数。但是这种改进还是没有根本上解决问题，即使我们把IMEX设为“1”， TypeGuessRows设得再大，例如1000，假设数据表有1001行，某列前1000行全为纯数字，该列的第1001行又是一个文本，ISAM驱动的这种机制还是让这列的数据变成空。

xchjh

解决方法
　　从以上的分析中可以得知，当某列数据中含有混合类型时，在.NET中使用Microsoft.Jet.OLEDB.4.0来读取Excel文件造成数据丢失是不可避免的，要解决这个问题只能考虑采用其它数据读取方法。
　　在.NET中读取Excel文件的另外一种方法是回到使用传统COM组件，这种方法在很多技术文章或论文中都有涉及，本文不作赘述。需要指出的是，使用COM组件来读取Excel文件数据的效率较低，在作释放的时候有可能碰到不可预知的错误，特别开发Web应用的程序应该慎重使用。

以上观点来自：

http://qkzz.net/magazine/1009-3044/2007/06/837331.htm

另一观点：

之前在使用 OleDb 讀取 Excel 時, 發生有些欄位雖然有值, 但是讀出來卻是 Null 的問題, 上網查了一下, 說是在連線字串中加入 Extended Properties, 將 IMEX 設為 1, 就可以強迫 OleDb 的 driver 將內容當成文字讀取, 以避免發生型別錯誤而回傳 null. 如下

Provider=Microsoft.Jet.OLEDB.4.0;Data
		Source="C:\Data.xls";Extended
		Properties="Excel 8.0;HDR=Yes;IMEX=1;

但是後來發現有些資料可以成功, 有些不行, 但是如果把資料列的順序作一下改變的話(把非數字的資料放到比較前面的幾列), 就又可以順利讀取, 真是怪到極點, 於是再去問了一下 Google, 終於找到問題的源頭... 原來是 OleDb Driver 的問題, 在機碼 HKLM\Software\Microsoft\Jet\4.0\Engines\Excel 有一個 TypeGuessRows 值, 預設是 8, 表示會先讀取前 8 列來決定每一個欄位的型態, 所以如果前 8 列的資料都是數字, 到了第 9 列以後出現的文字資料都會變成 null, 真是聰明得令人哭笑不得..... 所以如果要解決這個問題, 只要把 TypeGuessRows 機碼值改成 0, 就可以解這個問題了!

来自：http://sanchen.blogspot.com/2007/08/imex1-oledb-excel-null.html

[此贴子已经被作者于2008-10-19 0:39:32编辑过]

xchjh

通过以上知识的学习：我认为最保险，最好的解决办法是：2楼的方法：将数字、文本混排的列用2楼的方法转换为文本，然后再分成几个独立的分表。 大家觉得是这样吗？

figfig

建议 typeGUESSROWs 改为0 试试

[此贴子已经被作者于2008-10-19 0:47:39编辑过]