SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Create SQL table from an Excel spreadsheet


Create SQL table from an Excel spreadsheet

Author
Message
pnr8uk
pnr8uk
SSC Veteran
SSC Veteran (223 reputation)SSC Veteran (223 reputation)SSC Veteran (223 reputation)SSC Veteran (223 reputation)SSC Veteran (223 reputation)SSC Veteran (223 reputation)SSC Veteran (223 reputation)SSC Veteran (223 reputation)

Group: General Forum Members
Points: 223 Visits: 488
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
Neil Burton
Neil Burton
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3415 Visits: 10174
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
pnr8uk
pnr8uk
SSC Veteran
SSC Veteran (223 reputation)SSC Veteran (223 reputation)SSC Veteran (223 reputation)SSC Veteran (223 reputation)SSC Veteran (223 reputation)SSC Veteran (223 reputation)SSC Veteran (223 reputation)SSC Veteran (223 reputation)

Group: General Forum Members
Points: 223 Visits: 488
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
Jeff Moden
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86402 Visits: 41098
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
J Livingston SQL
J Livingston SQL
SSCertifiable
SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)

Group: General Forum Members
Points: 5466 Visits: 35417
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/2014 22/07/2014 15/07/2014
123456789 987654321 654789321
987654321 654789321 123456789


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
Jeff Moden
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86402 Visits: 41098
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/2014 22/07/2014 15/07/2014
123456789 987654321 654789321
987654321 654789321 123456789


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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
pnr8uk
pnr8uk
SSC Veteran
SSC Veteran (223 reputation)SSC Veteran (223 reputation)SSC Veteran (223 reputation)SSC Veteran (223 reputation)SSC Veteran (223 reputation)SSC Veteran (223 reputation)SSC Veteran (223 reputation)SSC Veteran (223 reputation)

Group: General Forum Members
Points: 223 Visits: 488
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search