如何将数据从 Excel 电子表格导出到 Sql Server 2008 表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3474137/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to export data from Excel spreadsheet to Sql Server 2008 table
提问by Loser Coder
I want to import data from an Excelfile - assume Excel 2003 / .xls - to Sql Server 2008.
我想从Excel文件导入数据- 假设 Excel 2003 / .xls - 到 Sql Server 2008。
Have tried adding a linked server to the JET OLE DB Access driver, and of course it fails on the 64-bit machine. But when I try to drop the linked server while experimenting, there's another error saying that the linked server already/still exists!
曾尝试将链接服务器添加到 JET OLE DB Access 驱动程序，当然它在 64 位机器上失败了。但是当我在试验时尝试删除链接服务器时，还有另一个错误说链接服务器已经/仍然存在！
I have also tried changing the Excel driver to 32-bit (regedit tool) but am not sure if it's doing anything, same error is coming up!
我还尝试将 Excel 驱动程序更改为 32 位（regedit 工具），但不确定它是否在执行任何操作，出现相同的错误！
Some more details: Say Table1 in Excel file has 5 columns. I want to map to Database.dbo.Table1 with 5 columns again, but with different names in table. Is there a way to do even do such an import?
更多细节：假设 Excel 文件中的 Table1 有 5 列。我想再次映射到具有 5 列的 Database.dbo.Table1，但表中的名称不同。有没有办法做这样的导入？
回答by marc_s
From your SQL Server Management Studio, you open Object Explorer, go to your database where you want to load the data into, right click, then pick Tasks > Import Data.
在 SQL Server Management Studio 中，打开对象资源管理器，转到要将数据加载到的数据库，右键单击，然后选择任务 > 导入数据。
This opens the Import Data Wizard, which typically works pretty well for importing from Excel. You can pick an Excel file, pick what worksheet to import data from, you can choose what table to store it into, and what the columns are going to be. Pretty flexible indeed.
这将打开导入数据向导，该向导通常非常适合从 Excel 导入。您可以选择一个 Excel 文件，选择要从中导入数据的工作表，您可以选择将数据存储到哪个表中，以及列将是什么。确实很灵活。
You can run this as a one-off, or you can store it as a SQL Server Integration Services (SSIS) package into your file system, or into SQL Server itself, and execute it over and over again (even scheduled to run at a given time, using SQL Agent).
您可以一次性运行它，也可以将其作为 SQL Server 集成服务 (SSIS) 包存储到您的文件系统或 SQL Server 本身中，然后一遍又一遍地执行它（甚至计划在某个时间运行）给定时间，使用 SQL 代理）。
Update:yes, yes, yes, you can do all those things you keep asking - have you even tried at least once to run that wizard??
更新：是的，是的，是的，你可以做所有你一直问的事情——你是否至少尝试过一次运行那个向导？？
OK, here it comes - step by step:
好的，它来了 - 一步一步：
Step 1:pick your Excel source
第 1 步：选择您的 Excel 源
Step 2:pick your SQL Server target database
第 2 步：选择您的 SQL Server 目标数据库
Step 3:pick your source worksheet (from Excel) and your target table in your SQL Server database; see the "Edit Mappings" button!
第 3 步：在 SQL Server 数据库中选择源工作表（来自 Excel）和目标表；请参阅“编辑映射”按钮！
Step 4:check (and change, if needed) your mappings of Excel columns to SQL Server columns in the table:
第 4 步：检查（并在需要时更改）Excel 列到表中 SQL Server 列的映射：
Step 5:if you want to use it later on, save your SSIS package to SQL Server:
第 5 步：如果您想稍后使用它，请将您的 SSIS 包保存到 SQL Server：
Step 6:- success! This is on a 64-bit machine, works like a charm - just do it!!
第 6 步：- 成功！这是在 64 位机器上，就像一个魅力 - 去做吧！
回答by Michael Geller
There are several tools which can import Excel to SQL Server.
有多种工具可以将 Excel 导入 SQL Server。
I am using DbTransfer (http://www.dbtransfer.com/Products/DbTransfer) to do the job. It's primarily focused on transfering data between databases and excel, xml, etc...
我正在使用 DbTransfer (http://www.dbtransfer.com/Products/DbTransfer) 来完成这项工作。它主要专注于在数据库和 excel、xml 等之间传输数据......
I have tried the openrowset method and the SQL Server Import / Export Assitant before. But I found these methods to be unnecessary complicated and error prone in constrast to doing it with one of the available dedicated tools.
我之前尝试过 openrowset 方法和 SQL Server 导入/导出助手。但是我发现与使用可用的专用工具之一相比，这些方法是不必要的复杂且容易出错。
回答by Nikita G.
In SQL Server 2016 the wizard is a separate app. (Important: Excel wizard is onlyavailable in the 32-bit versionof the wizard!). Use the MSDN pagefor instructions:
在 SQL Server 2016 中，向导是一个单独的应用程序。（重要提示：Excel 向导仅在32 位版本的向导中可用！）。使用MSDN 页面获取说明：
On the Start menu, point to All Programs, point toMicrosoft SQL Server , and then click Import and Export Data.
—or—
In SQL Server Data Tools (SSDT), right-click the SSIS Packages folder, and then click SSIS Import and Export Wizard.
—or—
In SQL Server Data Tools (SSDT), on the Project menu, click SSIS Import and Export Wizard.
—or—
In SQL Server Management Studio, connect to the Database Engine server type, expand Databases, right-click a database, point to Tasks, and then click Import Data or Export data.
—or—
In a command prompt window, run DTSWizard.exe, located in C:\Program Files\Microsoft SQL Server0\DTS\Binn.
After that it should be pretty much the same (possibly with minor variations in the UI) as in @marc_s's answer.
之后它应该与@marc_s 的答案几乎相同（可能在 UI 中略有不同）。
