如何将数据从 Excel 电子表格导出到 Sql Server 2008 表 [英] How to export data from Excel spreadsheet to Sql Server 2008 table

问题描述

我想从 Excel 文件中导入数据 - 假设为 Excel 2003/.xls - 到 Sql Server 2008.

I want to import data from an Excel file - assume Excel 2003 / .xls - to Sql Server 2008.

已尝试将链接服务器添加到 JET OLE DB Access 驱动程序，当然在 64 位计算机上失败.但是当我在试验时尝试删除链接服务器时，还有另一个错误说链接服务器已经/仍然存在！

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!

我也尝试将 Excel 驱动程序更改为 32 位(regedit 工具)，但不确定它是否在执行任何操作，出现同样的错误！

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 文件中的 Table1 有 5 列.我想再次映射到具有 5 列的 Database.dbo.Table1，但表中的名称不同.有没有办法做这样的导入?

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?

推荐答案

从 SQL Server Management Studio 打开对象资源管理器，转到要将数据加载到的数据库，右键单击，然后选择任务 > 导入数据.

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.

这将打开导入数据向导"，该向导通常非常适合从 Excel 导入.您可以选择一个 Excel 文件，选择要从中导入数据的工作表，您可以选择将数据存储到哪个表中，以及列将是什么.确实很灵活.

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.

您可以一次性运行它，也可以将其作为 SQL Server 集成服务 (SSIS) 包存储到您的文件系统或 SQL Server 本身中，然后一遍又一遍地执行(甚至计划到在给定时间运行，使用 SQL 代理).

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).

更新:是的，是的，是的，您可以完成您一直要求的所有事情 - 您是否至少尝试过一次运行该向导?

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:

第 1 步:选择您的 Excel 源

Step 1: pick your Excel source

第 2 步:选择您的 SQL Server 目标数据库

Step 2: pick your SQL Server target database

步骤 3: 在 SQL Server 数据库中选择源工作表(来自 Excel)和目标表；查看编辑映射"按钮！

Step 3: pick your source worksheet (from Excel) and your target table in your SQL Server database; see the "Edit Mappings" button!

第 4 步:检查(并在需要时更改)Excel 列到表中 SQL Server 列的映射:

Step 4: check (and change, if needed) your mappings of Excel columns to SQL Server columns in the table:

第 5 步:如果您想稍后使用它，请将您的 SSIS 包保存到 SQL Server:

Step 5: if you want to use it later on, save your SSIS package to SQL Server:

第 6 步: - 成功！这是在 64 位机器上运行的，就像一个魅力 - 去做吧！

Step 6: - success! This is on a 64-bit machine, works like a charm - just do it!!

这篇关于如何将数据从 Excel 电子表格导出到 Sql Server 2008 表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！