select*
fromopenrowset('Microsoft.Jet.OLEDB.4.0'
,'Excel8.0;HDR=YES;IMEX=1;DATABASE=d:folderexcel.xls',Sheet1$)
-------NOTE------
SettingIMEX=1tellsthedrivertouseImportmode.Inthisstate,theregistrysettingImportMixedTypes=Textwillbenoticed.Thisforcesmixeddatatobeconvertedtotext.Forthistoworkreliably,youmayalsohavetomodifytheregistrysetting,TypeGuessRows=8.TheISAMdriverbydefaultlooksatthefirsteightrowsandfromthatsamplingdeterminesthedatatype.Ifthiseightrowsamplingisallnumeric,thensettingIMEX=1willnotconvertthedefaultdatatypetoText;itwillremainnumeric.
YoumustbecarefulthatIMEX=1notbeusedindiscriminately.ThisisIMPORTmode,sotheresultsmaybeunpredictableifyoutrytodoappendsorupdatesofdatainthismode.
ThepossiblesettingsofIMEXare:
0isExportmode
1isImportmode
2isLinkedmode(fullupdatecapabilities)
【Select data from an Excel sheet in MSSQL】相关文章：
★ sqlserver、Mysql、Oracle三种数据库的优缺点总结
★ sqlserver和oracle中对datetime进行条件查询的一点区别小结
★ Sql Server、Access数据排名的实现方法(例如：成绩排名)
★ Sql Server 和 Access 操作数据库结构Sql语句小结
★ SQL Server不存在或访问被拒绝问题的解决第1/3页