查询Excel时，出现这样的问题？

Question

使用此
SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0', 'Data Source=C:\Documents and Settings\Administrator\桌面\2007.xls;Extended Properties=Excel 8.0')...tableinfo$
查询时，若Excel某列为数字时，那么查询出来的是，NULL，不知道有没有办法，解决.

Answer 1

SELECT * FROM OPENDATASOURCE
('Microsoft.Jet.OLEDB.4.0', 'Data Source=C:\Documents and Settings\liang\桌面\test1.xls;Extended Properties=Excel 8.0')...sheet1$F1                                                    F2                                                    F3                                                    F4                                                    F5                                                    F6                                                    
----------------------------------------------------- ----------------------------------------------------- ----------------------------------------------------- ----------------------------------------------------- ----------------------------------------------------- ----------------------------------------------------- 
NULL                                                  NULL                                                  13.0                                                  14.0                                                  15.0                                                  16.0
NULL                                                  NULL                                                  21.0                                                  22.0                                                  23.0                                                  24.0
NULL                                                  NULL                                                  29.0                                                  30.0                                                  31.0                                                  32.0
NULL                                                  NULL                                                  37.0                                                  38.0                                                  39.0                                                  40.0
50.0                                                  60.0                                                  70.0                                                  80.0                                                  NULL                                                  NULL
90.0                                                  100.0                                                 110.0                                                 120.0                                                 NULL                                                  NULL
130.0                                                 140.0                                                 150.0                                                 160.0                                                 NULL                                                  NULL
170.0                                                 180.0                                                 190.0                                                 200.0                                                 NULL                                                  NULL
210.0                                                 220.0                                                 230.0                                                 240.0                                                 NULL                                                  NULL(9 row(s) affected)

Answer 2

SELECT * FROM OPENDATASOURCE
('Microsoft.Jet.OLEDB.4.0', 'Data Source=C:\Documents and Settings\liang\桌面\test1.xls;Extended Properties=Excel 8.0')...sheet2$F1                        F2               F3                      F4                                                    13.0                     14.0              15.0                    16.0
21.0                     22.0              23.0                    24.0
29.0                     30.0              31.0                    32.0
37.0                     38.0              39.0                    40.0
(4 row(s) affected)
我測試過了，不會有空的呀

Answer 3

我使用的是Excel２０００
不会是这个问题吧.而且输入的数字是１２３３３　这样的，没有小数点.

Answer 4

可能是格式问题
select * OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=D:\教务处工作\高一年级(07级)\200708分班.xls;HDR=YES;IMEX=1','SELECT * FROM [sheet1$]')加IMEX=1;添加一行格式数据，在导入时排除这条数据，或导入后删除这条数据即可

Answer 5

--假定出问题的列是B21、问题出在EXCEL中，在SQL Server中不能解决；
2、另插入一列，在单元格中：=TEXT(B2*1,"000000")；
3、将原来的单元格的内容用新值替换即可。

Answer 6

to:wgzaaa() ( ) 信誉：100 服务器: 消息 156，级别 15，状态 1，行 1
在关键字 'OPENROWSET' 附近有语法错误。

Answer 7

sorry,加from ,并确认是sheet1还是Sheet1,大小写敏感

Answer 8

select * from OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=C:\Documents and Settings\Administrator\桌面\2007.xls;HDR=YES;IMEX=1','SELECT * FROM [tableinfo$]')--搞错贴了,试试这个

Answer 9

你查看一下你的excel中的数据是不是有的带绿色小三角（此单元格中的数字为文本格式或者前面有撇号）， 有的没有；如果同一列中的数据有这种情况，就会出现部分数据存在，部分为null。同一列中数据必须同一数据类型。

Answer 10

感谢各位
to :wgzaaa() ( ) 信誉：100 
使用你提供的，OK，请教具体是什么原因．

Answer 11

HDR=YES;是否确定第一行作为列名
IMEX=1 ;是否将数据体全作为字符型，当不用它时，在用openrowset的时候会分析每列数据的前几行，如果大部分是数字，将确认此列为数字，字符的自然就为空，其它列一样

Answer 12

鸟所说基本正确，imex有混合意思，意即将数据类型不一致时，采用文本类型，如一致还用原来类型