Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Create SQL table from an Excel spreadsheet Expand / Collapse
Author
Message
Posted Friday, July 18, 2014 2:49 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, October 8, 2014 3:02 AM
Points: 124, Visits: 332
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
Post #1593948
Posted Friday, July 18, 2014 3:02 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 4:00 PM
Points: 1,105, Visits: 3,539
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
Post #1593952
Posted Saturday, July 19, 2014 6:04 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, October 8, 2014 3:02 AM
Points: 124, Visits: 332
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?
Post #1594345
Posted Saturday, July 19, 2014 9:08 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:45 PM
Points: 35,342, Visits: 31,875
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1594356
Posted Sunday, July 20, 2014 8:11 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 1:48 PM
Points: 1,917, Visits: 19,578
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
Post #1594398
Posted Sunday, July 20, 2014 9:56 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:45 PM
Points: 35,342, Visits: 31,875
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1594403
Posted Monday, July 21, 2014 2:26 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, October 8, 2014 3:02 AM
Points: 124, Visits: 332
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
Post #1594488
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse