Select data from an Excel sheet in MSSQL

select*
fromopenrowset('Microsoft.Jet.OLEDB.4.0'
,'Excel8.0;HDR=YES;IMEX=1;DATABASE=d:\folder\excel.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) (责任编辑：admin)