SQL 将excel数据导入SQL Server 2008

Importing excel data into SQL Server 2008

提问by Nhuren

I need to import data from Excel to my database. I need to insert the data from my Excel sheet to an existing table in my database.

我需要将数据从 Excel 导入到我的数据库中。我需要将 Excel 工作表中的数据插入到数据库中的现有表中。

I tried to import data with the help of sql wizard. Firstly I imported it into a temp table then I used an insert query to import data into my destination table. But it does not seem to have worked correctly.

我尝试在 sql 向导的帮助下导入数据。首先，我将其导入到临时表中，然后使用插入查询将数据导入到目标表中。但它似乎没有正常工作。

So, plz suggest me good way of importing the data. And also it would be better if you suggest some good sql script for importing.

所以，请建议我导入数据的好方法。而且，如果您建议一些用于导入的好的 sql 脚本，那就更好了。

回答by Fauzi88

For file excel 2007 version (*.xlsx)

对于文件 excel 2007 版本 (*.xlsx)

INSERT INTO MyTable
SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0;Database=D:\test.xlsx', [Customer$])

For file excel 97-2003 version (*.xls)

对于文件 excel 97-2003 版本 (*.xls)

INSERT INTO MyTable
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=D:\test.xls', [Customer$])

回答by DHoff

I tried using the previous answer on an .xlsxfile (version 14.0.6112.2500 64-bit Microsoft Excel file)

我尝试在.xlsx文件上使用上一个答案（版本 14.0.6112.2500 64 位 Microsoft Excel 文件）

SELECT * 
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\xls_to_sql\xltest.xlsx', [Sheet1$])

I then saved the spreadsheet as an .xls(97-2003 version) and tried again.

然后我将电子表格保存为.xls（97-2003 版本）并再次尝试。

SELECT * 
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\xls_to_sql\xltest.xls', [Sheet1$])

Bot times I got the same error message:

Bot 时间我收到相同的错误消息：

 Msg 7308, Level 16, State 1, Line 1
 OLE DB provider 'Microsoft.Jet.OLEDB.4.0' cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode.

SQL SERVER information:

SQL 服务器信息：

Microsoft SQL Server Management Studio 10.50.1617.0 Microsoft Analysis Services Client Tools 10.50.1617.0 Microsoft Data Access Components (MDAC) 6.1.7601.17514 Microsoft MSXML 2.6 3.0 6.0 Microsoft Internet Explorer 9.0.8112.16421 Microsoft .NET Framework 2.0.50727.5448 Operating System 6.1.7601

Microsoft SQL Server Management Studio 10.50.1617.0 Microsoft Analysis Services 客户端工具 10.50.1617.0 Microsoft 数据访问组件 (MDAC) 6.1.7601.17514 Microsoft MSXML 2.6 3.0 6.0 Microsoft Internet Explorer 9.0.8112.16421 Microsoft .07 操作框架 .87 17 4 4 4

I don't have the Microsoft.Jet.OLEDB.4.0 or at least I don't know how to get it. I also don't know how to run everything in 32-Bit mode if that is the cause of the problem. I would appreciate help running in 32-Bit mode and also downloading and installing Microsoft.Jet.OLEDB.4.0 if I don't have it installed for some reason.

我没有 Microsoft.Jet.OLEDB.4.0 或者至少我不知道如何获得它。如果这是问题的原因，我也不知道如何在 32 位模式下运行所有​​内容。如果我因某种原因没有安装 Microsoft.Jet.OLEDB.4.0，我将不胜感激在 32 位模式下运行以及下载和安装 Microsoft.Jet.OLEDB.4.0 的帮助。

I tried the linked server method I saw posted for SQL Server 2005 but there is no Microsoft.Jet.OLEDB.4.0 option that was mentioned in the tutorial. See http://support.microsoft.com/kb/321686.

我尝试了我看到为 SQL Server 2005 发布的链接服务器方法，但教程中没有提到 Microsoft.Jet.OLEDB.4.0 选项。请参阅http://support.microsoft.com/kb/321686。

回答by DevT

first saved the spreadsheet as an .xls (97-2003 version) format and import them to SQL in temporary file. After import those data make sure that temp table field lengths are similar to original.

首先将电子表格保存为 .xls（97-2003 版本）格式，并将它们导入到 SQL 临时文件中。导入这些数据后，请确保临时表字段长度与原始数据相似。

then use below statement to update orginal table

然后使用下面的语句来更新原始表

insert into orginal (field1, field2)
select field1, field2 from temp

回答by Blanthor

There are three ways I usually do this.

我通常通过三种方式来做到这一点。

1. Use VBA inside the Worksheet. This involves some development work which imho is too much effort, if you are doing this only one time. This is nice if you want to use this worksheet multiple times.
2. Use a combination of macros inside the worksheet to concatenate insert queries which I then paste into SQL Management Studio, or some similar SQL client, and run the inserts.
3. Use the bulk copy command-line tool to copy a CSV file, which I would convert from the worksheet, like this: bcp [dbname].[dbo].[myTableName] in data1.csv -T -SmyServerName -c -t^| > log1.txt

1. 在工作表中使用 VBA。这涉及一些开发工作，恕我直言，如果您只这样做一次，那就太费力了。如果您想多次使用此工作表，这很好。
2. 使用工作表内的宏组合来连接插入查询，然后我将其粘贴到 SQL Management Studio 或一些类似的 SQL 客户端，并运行插入。
3. 使用批量复制命令行工具复制一个 CSV 文件，我将从工作表转换，如下所示： bcp [dbname].[dbo].[myTableName] in data1.csv -T -SmyServerName -c -t^| > log1.txt

回答by Afshin

you did not mention anything about your existing table and keys in table and excel file, so just to give you a push, following command selects all data from xltest.xls file sheet ('customrs')

你没有提到任何关于你现有的表和表和 Excel 文件中的键，所以只是给你一个推动，下面的命令从 xltest.xls 文件表（'customrs'）中选择所有数据

    SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
    'Excel 8.0;Database=C:\test\xltest.xls', [Customers$])

from this point, it's up to your database structure how to import/merge to existing data.

从这一点来看，如何导入/合并到现有数据取决于您的数据库结构。

回答by dbaindc

If you need to import a .xlsx into 64-bit SQL Server, try installing the 64-bit Microsoft Access database Engine.

如果您需要将 .xlsx 导入 64 位 SQL Server，请尝试安装 64 位 Microsoft Access 数据库引擎。

See http://www.microsoft.com/en-us/download/details.aspx?id=13255

请参阅http://www.microsoft.com/en-us/download/details.aspx?id=13255

For example, to import data from c:\data.xlsx, which has a sheet called MyData, then you could use:

例如，要从 c:\data.xlsx 导入数据，其中有一个名为 MyData 的工作表，那么您可以使用：

SELECT *
  FROM OPENROWSET ( 'Microsoft.ACE.OLEDB.12.0'
                  , 'Excel 12.0;database=c:\data.xlsx;IMEX=1'
                  , 'SELECT * FROM [MyData$]')