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】相关文章：
★ 数据库插入数据之select into from与insert into select区别详解
★ sql server中datetime字段去除时间代码收藏
★ datastage server job之dsjob 命令
★ 用户管理的备份（一致性备份、非一致性备份、脱机备份、联机备份）
★ JDBC大批量写入数据到SQLServer2000,记录数大于10000
★ 取随机记录的语句
★ SQL SERVER 与ACCESS、EXCEL的数据转换