从SQL Server 2005读取MS Excel文件 [英] Reading MS Excel file from SQL Server 2005

问题描述

我需要从SQL Server 2005中的查询中读取Microsoft Excel 2003文件（.xls），然后将一些数据插入到某些表中。读取文件然后使用其数据本身不是一个问题，但我发现，对于一列，有时我得到一个NULL值，而不是Excel文件中显示的值。更具体来说：此列总是只有一个字符长，它可以包含0-9的任意一个数字或字母K。当列包含'K'时，查询给我一个NULL值。我的假设是，由于前几行包含数字作为此列的值，查询假定它们将始终为数字，当它找到一个字母时，它只会将其变为NULL。

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.

我尝试将Excel文件中的单元格的格式更改为文本，并使用CAST和CONVERT（而不是同时）对该值进行调整，以使其成为一个varchar，但不会执行任何操作。 / p>

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.

推荐答案

这看起来像一个旧的OLE DB驱动程序的Excel。不是它不起作用 - 您仍然可以使用它查询电子表格。可能尝试一些新的东西：

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$]') 

您需要在SQL Server上更新ODBC驱动程序（请确保获得相应的32对64位版本）。

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

这篇关于从SQL Server 2005读取MS Excel文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！