Reading MS Excel file from SQL Server 2005

问题:

I need to read a Microsoft Excel 2003 file (.xls) from a query in SQL Server 2005, and then insert some of that data into some tables. Reading the file and then using its data is not a problem in itself, but I found that, for a column, sometimes I get a NULL value instead of the value that's shown in the Excel file. To be more specific: This column is always just one character long, and it can contain any one digit from 0-9, or the letter 'K'. It's when the column contains 'K' that the query gives me a NULL value. My assumption is that, since the first few rows contain numbers as the values of this column, the query assumes they will always be numbers, and when it finds a letter it just turns it into NULL.

I tried changing the format of the cells in the Excel file to text, and using CAST and CONVERT (not at the same time) on the value to try to make it a varchar, but it does nothing.

回答1:

That looks like an older OLE DB driver for Excel. Not that it doesn't work--you can still "query" the spreadsheet with it. Maybe try something newer:

SELECT * FROM   
OPENROWSET('Microsoft.ACE.OLEDB.12.0',        
    'Excel 12.0 Xml;HDR=YES;Database=C:\File.xls',        
    'SELECT * FROM [Sheet1$]') 

You'll need an updated ODBC driver on the SQL Server (make sure to get the appropriate 32 vs 64 bit version).