Create SQL table from an Excel spreadsheet

Question

Create SQL table from an Excel spreadsheet

pnr8uk

SSCommitted

Points: 1828
More actions
July 18, 2014 at 2:49 am

#293499

Is there a way using SSIS to create a SQL table based on the headers of an Excel workbook/sheet?
The table will be a staging table and will be created/dropped each day.
I have a spreadsheet xslx which has w/e date as column headings eg. 17/02/2014 | 25/02/2014 etc.,
These change every week so I want to create a new table each week, populate it and load it.
Does anyone know how to do this?
Thanks for any help

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Neil Burton SSC-Insane Points: 22905 More actions · Answer 1

Have a look at this article. It'll certainly point you in the right direction.

On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
—Charles Babbage, Passages from the Life of a Philosopher

How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537

pnr8uk SSCommitted Points: 1828 More actions · Answer 2

Thanks for the reply and that is a good article, however it does not create the table automatically from the Excel spreadsheet. I can't create the table first as the columns will be different periods each week. Any more links or ideas anyone?

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 3

pnr8uk (7/19/2014)
Thanks for the reply and that is a good article, however it does not create the table automatically from the Excel spreadsheet. I can't create the table first as the columns will be different periods each week. Any more links or ideas anyone?

SELECT/INTO FROM OPENROWSET using the ACE drivers. It will take some dynamic SQL after that because the columns are all dynamically named (although that's not an SSIS solution).

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 4

as an example of what may work for you...........

I have a spreadsheet called JLS.xlsx that is always stored in C:\xlimport (C:\xlimport\jls.xlsx)

it has a worksheet named "data" that I want to extract periodically into a SQL table always called [newtable] but the column names change on each import.

the first row of the worksheet represents the column header names to use SQL [newtable]

jls.xlsx example

29/07/201422/07/201415/07/2014

123456789987654321654789321

987654321654789321123456789

SELECT * into newtable FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',

'Excel 12.0;Database=C:\xlimport\jls.xlsx; Extended properties=Excel 12.0', [data$])

SELECT * FROM newtable

users update the spreadsheet with new information and rename the column headings

so drop new table and rerun code and you will pick up the new column headings

DROP TABLE newtable

SELECT * into newtable FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',

'Excel 12.0;Database=C:\xlimport\jls.xlsx; Extended properties=Excel 12.0', [data$])

SELECT * FROM newtable

if needed as part of a larger SSIS solution put the code in an Execute TSQL Task.

Alternatively set up a linked server and use that

EXEC master.dbo.sp_addlinkedserver

@server = N'jlsxlimport',

@srvproduct=N'Excel 12.0',

@provider=N'Microsoft.ACE.OLEDB.12.0',

@datasrc=N'C:\xlimport\jls.xlsx',

@provstr=N'Excel 12.0'

select * into newtable from jlsxlimport...data$

some very good advice on getting data into SQL from Excel can be found here

http://www.excel-sql-server.com/excel-import-to-sql-server-using-distributed-queries.htm

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 5

J Livingston SQL (7/20/2014)
as an example of what may work for you...........
I have a spreadsheet called JLS.xlsx that is always stored in C:\xlimport (C:\xlimport\jls.xlsx)
it has a worksheet named "data" that I want to extract periodically into a SQL table always called [newtable] but the column names change on each import.
the first row of the worksheet represents the column header names to use SQL [newtable]
jls.xlsx example
29/07/201422/07/201415/07/2014
123456789987654321654789321
987654321654789321123456789
SELECT * into newtable FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0;Database=C:\xlimport\jls.xlsx; Extended properties=Excel 12.0', [data$])
SELECT * FROM newtable
users update the spreadsheet with new information and rename the column headings
so drop new table and rerun code and you will pick up the new column headings
DROP TABLE newtable
SELECT * into newtable FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0;Database=C:\xlimport\jls.xlsx; Extended properties=Excel 12.0', [data$])
SELECT * FROM newtable
if needed as part of a larger SSIS solution put the code in an Execute TSQL Task.
Alternatively set up a linked server and use that
EXEC master.dbo.sp_addlinkedserver
@server = N'jlsxlimport',
@srvproduct=N'Excel 12.0',
@provider=N'Microsoft.ACE.OLEDB.12.0',
@datasrc=N'C:\xlimport\jls.xlsx',
@provstr=N'Excel 12.0'
select * into newtable from jlsxlimport...data$
some very good advice on getting data into SQL from Excel can be found here
http://www.excel-sql-server.com/excel-import-to-sql-server-using-distributed-queries.htm

+1000 Graham. Great example.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

pnr8uk SSCommitted Points: 1828 More actions · Answer 6

Thanks Jeff that is brilliant, I was working with the OPENROWSET but this is just what I want. This is part of a wider dynamic SSIS which I may post the solution when complete as it is going to be very re-useable solution.

I mean we all know just what 'oh the headings don't change' and 'it shouldn't change' mean 😉

Thanks again

Paul