海量数据插入到SQL Server中?

I've got 8 worksheets within an Excel workbook that I'd like to import into separate tables within a SQL Server DB.

我在Excel工作簿中有8个工作表，我想将它们导入到SQL Server DB中的不同表中。

I'd like to import each of the 8 worksheets into a separate table, ideally, with table names coinciding with worksheet tab names, but initially, I just want to get the data into the tables, so arbitrary table names work for the time being too.

我希望将这8个工作表中的每一个都导入到一个单独的表中，理想情况下，表名与工作表选项卡名称一致，但是最初，我只想将数据导入到表中，因此任意的表名暂时也可以工作。

The format of the data in each of the worksheets (and tables by extension) is the same (and will be identical), so I'm thinking some kind of loop could be used to do this.

每个工作表(以及扩展后的表)中的数据格式都是相同的(并且将是相同的)，因此我认为可以使用某种循环来实现这一点。

Data looks like this:

数据是这样的:

Universe    Date        Symbol      Shares  MktValue    Currency
SMALLCAP    6/30/2011   000360206   27763   606361.92   USD
SMALLCAP    6/30/2011   000361105   99643   2699407.52  USD
SMALLCAP    6/30/2011   00081T108   103305  810926.73   USD
SMALLCAP    6/30/2011   000957100   57374   1339094.76  USD

And table format in SQL would/should be consistent with the following:

而SQL中的表格格式将/应该与以下内容一致:

CREATE TABLE dbo.[market1] (
[Universe_ID] char(20),
[AsOfDate] smalldatetime,
[Symbol] nvarchar(20),
[Shares] decimal(20,0),
[MktValue] decimal(20,2),
[Currency] char(3)
)

I'm open to doing this using either SQL/VBA/C++ or some combination (as these are the languages I know and have access to). Any thoughts on how to best go about this?

我愿意使用SQL/VBA/ c++或某些组合(因为这些是我所知道的并且可以访问的语言)来实现这一点。你有什么想法吗?

4 个解决方案

#1

You could use SSIS or DTS packages to import them. Here are a couple references to get you going.

您可以使用SSIS或DTS包来导入它们。这里有一些参考资料可以帮助你。

Creating a DTS Package - pre 2005

创建一个DTS包- pre 2005

Creating a SSIS Package - 2005 forward

创建一个SSIS包- 2005年前进

#2

For an Excel file (2007 or 2010) with an xlsx extension, I have renamed them to .zip and extracted their contents into a directory and use SQL XML Bulk Load to import the sheets and reference tables. When I have all the data in SQL server, I use basic SQL queries to extract/transform the data needed into designated worksheets. -- This keeps the "digestion" logic in SQL and uses minimal external VB script of C# development.

对于使用xlsx扩展的Excel文件(2007或2010)，我将它们重新命名为.zip，并将其内容提取到一个目录中，并使用SQL XML批量加载来导入表和引用表。当我在SQL server中拥有所有数据时，我使用基本的SQL查询将所需的数据提取/转换到指定的工作表中。——这保持了SQL中的“消化”逻辑，并使用最小的c#开发的外部VB脚本。

Link to SQL Bulk Load of XML data: http://support.microsoft.com/kb/316005

链接到XML数据的SQL大容量负载:http://support.microsoft.com/kb/316005

#3

In SQL Management Studio, right click on a database, then click Tasks, then Import Data. This will take you through some screens and create an SSIS package to import the file. At some point in the process it will ask you if you want to save the package (I would run it a few times as well to make sure it imports your data the way you want it). Save it and then you can schedule the package to be run as a Job via the SQL Server Agent. (The job type will be Sql Server Integration Services).

在SQL Management Studio中，右键单击数据库，然后单击任务，然后导入数据。这将带您浏览一些屏幕并创建一个SSIS包来导入文件。在过程的某个时刻，它会询问您是否要保存包(我也会运行它几次，以确保它按照您希望的方式导入数据)。保存它，然后您可以安排包通过SQL Server代理作为作业运行。(作业类型将是Sql Server Integration Services)。

#4

You can use following script

SELECT * INTO XLImport3 FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0', 'Data Source=C:\test\xltest.xls;Extended Properties=Excel 8.0')...[Customers$]

从OPENDATASOURCE选择*到XLImport3 ('Microsoft.Jet.OLEDB.4.0'， 'Data Source=C:\test\xltest.xls;Extended Properties=Excel 8.0')……[客户$]

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

从OPENROWSET中选择*到XLImport4 ('Microsoft.Jet.OLEDB.4.0'， 'Excel 8.0;Database=C:\test\xltest)。xls”,[客户]美元)

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

从OPENROWSET中选择*到XLImport5 ('Microsoft.Jet.OLEDB.4.0'， 'Excel 8.0;数据库=C:\test\xltest。xls'、'SELECT * FROM [Customers$])

OR can use following

SELECT * INTO XLImport2 FROM OPENQUERY(EXCELLINK, 'SELECT * FROM [Customers$]')

从OPENQUERY选择*到XLImport2(出色的，从[客户$]中选择*)