从Excel导数据到sql server出现NULL的问题

今天一同事在用SQL Server的OPENROWSET函数从Excel中导数据到SQL Server时出现中文数据变成NULL，其语句是这样的：

select a.* from OPENROWSET(’Microsoft.JET.OLEDB.4.0′,
‘Excel 8.0;Database=c:\students.xls;’,
‘SELECT * FROM [sheet1$]‘) as a

执行结果如下：

学籍管理 F2 F3
—————————– —————————— ————————————————-
NULL NULL NULL
1.0 1234.0 23.0
2.0 1235.0 24.0
3.0 1234.0 25.0
4.0 1235.0 26.0
5.0 1234.0 27.0
6.0 1235.0 28.0
7.0 1234.0 29.0
8.0 1235.0 30.0
9.0 1234.0 31.0
10.0 1235.0 32.0
11.0 1234.0 33.0
12.0 1235.0 34.0
13.0 1234.0 35.0
14.0 1235.0 36.0
NULL NULL NULL

以上是测试数据

帮忙查了一些资料，原因是这样的：

当通过OLEDB来读取Excel数据时，默认情况是将注册表中HKLM\Software\Microsoft\Jet\4.0\Engines\Excel\TypeGuessRows的值（该值默认是8）作为判断数据类型的行数，根据这些行数中最常被发现的数据类型来决定数据类型。如果有限制，将由下列顺序来决定数据类型：Number, Currency, Date, Text, Long Text 。如果遇到的数据不符合字段的推测的数据类型时，它会作为 Null 值返回。在导入时，如果一个字段有混合数据类型，整个字段将根据 ImportMixedTypes 设置来转换。

对于上述例子，根据上面的规则，把数字类型作为主要的数据类型进行导入，而中文数据不符合该推断的数据类型，所以返回NULL。

那么如何解决该问题呢？

看上面粗体红色部分，如果把读取数据的模式设置成导入模式，那么整个字段就会根据ImportMixedTypes指定的类型来转换。在OLEDB中，可以指定连接字串扩展属性来指定模式，这个属性就是IMEX，意义如下：

0 is Export mode
1 is Import mode
2 is Linked mode (full update capabilities)

所以指定IMEX=1即可解决问题：

select a.* from OPENROWSET(’Microsoft.JET.OLEDB.4.0′,
‘Excel 8.0;Database=c:\students.xls;IMEX=1′,
‘SELECT * FROM [sheet1$]‘) as a

学籍管理 F2 F3
—————————– —————————— ————————————————-
姓名 序号 年龄
1.0 1234.0 23.0
2.0 1235.0 24.0
3.0 1234.0 25.0
4.0 1235.0 26.0
5.0 1234.0 27.0
6.0 1235.0 28.0
7.0 1234.0 29.0
8.0 1235.0 30.0
9.0 1234.0 31.0
10.0 1235.0 32.0
11.0 1234.0 33.0
12.0 1235.0 34.0
13.0 1234.0 35.0
14.0 1235.0 36.0

OLEDB for Excel连接字串还有另外一个扩展属性HDR（Header Row），表示是否将第一行当作栏位。

附：

1.OLEDB for excel参数说明

win32 Msexcl35.dll 的位置。这个完整路径在安装时就已确定。
TypeGuessRows 被检查数据类型的行数。根据在选择中最常被发现的数据类型来决定数据类型。如果有限制，将由下列顺序来决定数据类型：Number, Currency, Date, Text, Long Text 。如果遇到的数据不符合字段的推测的数据类型时，它会作为 Null 值返回。在导入时，如果一个字段有混合数据类型，整个字段将根据 ImportMixedTypes 设置来转换。被检查的行数缺省值是 8。

ImportMixedTypes 可以被设置成 Majority Type 或是 Text。如果设置成 Majority Type，混合数据类型的字段将会在导入时改变成占主导地位的数据类型。如果设置成 Text，混合数据类型的字段将在导入时改变成 Text 的数据类型。缺省值是 Text。
AppendBlankRows 在增加新数据前，添加至 3.5 版或 4.0 版工作表末端的空白行数。例如，如果 AppendBlankRows 设置成 4，Microsoft Jet 将在增加包含数据的行之前添加 4 行空白至工作表末端。此设置的整型值范围从 0 到 16；缺省值是 01（追加一附加行）。
FirstRowHasNames 指示表的第一行是否包含字段名的二进制值。01 的值指示在导入期间将从第一行取得字段名。00 的值指示在第一行中没有字段名；字段名显示为 F1、F2、F3等等。缺省值是 1。

2.参考文档

http://support.microsoft.com/default.aspx?kbid=194124

http://www.5axx.net/sql/html/dahowchangingexcel.htm