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>
頁:
[1]