Create SQL table from an Excel spreadsheet

  • 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

  • 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

  • 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?

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

  • 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

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

  • 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

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

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