CODEWITHSUNDEEP
c++sqlsql-serverexcelvba
Chris
Chris

Reputation: 1680

Mass data insert into 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.

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.

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:

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?

Upvotes: 2

Views: 1631

Answers (4)

gngolakia
gngolakia

Reputation: 2206

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

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

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

OR can use following

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

Upvotes: 0

Robert
Robert

Reputation: 3074

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

Creating a DTS Package - pre 2005

Creating a SSIS Package - 2005 forward

Upvotes: 2

Jeremy Gray
Jeremy Gray

Reputation: 1418

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

Upvotes: 1

Richard Ozenbaugh
Richard Ozenbaugh

Reputation: 55

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.

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

Upvotes: 1

Related Questions