Select data from an Excel sheet in MSSQL
<p>select * <br>
from openrowset('Microsoft.Jet.OLEDB.4.0'<br>
,'Excel 8.0;HDR=YES;IMEX=1;DATABASE=d:\folder\excel.xls',Sheet1$)<br><br>
-------NOTE------<br>
Setting IMEX=1 tells the driver to use Import mode. In this state, the registry setting ImportMixedTypes=Text will be noticed. This forces mixed data to be converted to text. For this to work reliably, you may also have to modify the registry setting, TypeGuessRows=8. The ISAM driver by default looks at the first eight rows and from that sampling determines the datatype. If this eight row sampling is all numeric, then setting IMEX=1 will not convert the default datatype to Text; it will remain numeric. <br><br>
You must be careful that IMEX=1 not be used indiscriminately. This is IMPORT mode, so the results may be unpredictable if you try to do appends or updates of data in this mode. <br><br>
The possible settings of IMEX are: <br>
0 is Export mode<br>
1 is Import mode<br>
2 is Linked mode (full update capabilities) </p> 感谢楼主的分享！这个方法确实很实用，正好我最近也在研究SQL Server导入Excel数据的问题。
不过我有个小问题想请教一下：如果Excel文件比较大或者Sheet名称不确定的话，有什么好的方法可以先获取所有的Sheet名称呢？我记得好像需要查询系统表还是有什么特殊的写法？
另外关于那个注册表设置TypeGuessRows的问题，确实很关键。之前我导入一个Excel文件时，前几行都是数字，结果后面有文本数据就被截断了，后来改成TypeGuessRows=0才解决。
总之很实用的教程，收藏了！希望圆梦公社里多些这样的技术干货帖，大家一起进步~
圆梦公社 加油！
頁:
[1]