Import Excel Spreadsheet to Database Tables

  • Jeff Moden

    SSC Guru

    Points: 994648

    peterhe (2/18/2010)


    This is too much to our customers.

    Heh... been there and done that so very much understood. It's amazing that we let some folks have a computer.

    Thanks for the feedback, Peter, and now I understand the "why" of the article much more clearly.

    --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
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

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

  • JGay 2041

    SSC Enthusiast

    Points: 142

    Peter,

    1 - Excel won't load file - problem resolved:

    * The file I used to make the xml was a csv.

    * I resaved the csv to an excel xlsx

    * saved to xml spreadsheet 2003

    * opened with excel

    2 - Invalid Excel Worksheet. No data in the worksheet - problem resolved:

    * The xml spreadsheet saved from csv spreadsheet had a different worksheetname

    * worksheetname was 'abc' and Sproc was looking for 'Sheet1'

    3 - New Issue

    * Created a new xml spreadsheet from xlsx spreadsheet with 10 rows & 117 columns of data

    * Worksheet name is 'Sheet1' and Sproc code is: @WorkSheetName=N'Sheet1',

    * The new error message is:

    The column '0' was specified multiple times for 'pvt'. at line 11

    Msg 50000, Level 16, State 1, Procedure uspImportExcelSheet, Line 504

    The column '0' was specified multiple times for 'pvt'.

    Thanks,

    JG

  • peterhe

    SSChampion

    Points: 11362

    JGay,

    I am sure what caused the error, maybe duplicated columns (headers) in the spreadsheet. Can you run the SP in debug mode and check the output?

  • JGay 2041

    SSC Enthusiast

    Points: 142

    Peter,

    1 - running debug. In debug SQL is one line, I split here for readability

    INSERT dbo.TestTable

    (

    ABC_NAME_ADDITIONAL_INFORMATION,

    ABC_NAME_ADDITIONAL_INFORMATION2,

    ABC_NAME_ADDITIONAL_INFORMATION3,

    ABC_NAME_ATTENTION,

    ABC_NAME_ATTENTION2,

    ABC_NAME_ATTENTION3,

    ETC...

    )

    The column names are long, but different. Is there a length max?

    Running the scroll out to the end of the SQL: INSERT dbo.TestTable(.... the column names just 'stop' - not all the column names are listed. This file has 117 column names - Is there a limit?

    ColumnID 117 ColumnName Status ExcelColNum 0

    ColumnID 118 ColumnName Suffix ExcelColNum 117

    ColumnID 119 ColumnName ABC_NAME_ATTENTION ExcelColNum 0

    Should ColumnID and ExcelColNum - match?

  • peterhe

    SSChampion

    Points: 11362

    JGay 2041 (2/19/2010)


    Peter,

    1 - running debug. In debug SQL is one line, I split here for readability

    INSERT dbo.TestTable

    (

    ABC_NAME_ADDITIONAL_INFORMATION,

    ABC_NAME_ADDITIONAL_INFORMATION2,

    ABC_NAME_ADDITIONAL_INFORMATION3,

    ABC_NAME_ATTENTION,

    ABC_NAME_ATTENTION2,

    ABC_NAME_ATTENTION3,

    ETC...

    )

    The column names are long, but different. Is there a length max?

    Running the scroll out to the end of the SQL: INSERT dbo.TestTable(.... the column names just 'stop' - not all the column names are listed. This file has 117 column names - Is there a limit?

    ColumnID 117 ColumnName Status ExcelColNum 0

    ColumnID 118 ColumnName Suffix ExcelColNum 117

    ColumnID 119 ColumnName ABC_NAME_ATTENTION ExcelColNum 0

    Should ColumnID and ExcelColNum - match?

    The Columns in your table should have the same name as the headers in the spreadsheet. If ExcelColNum is 0, it means the column does not exist in the spreadsheet.

    If you need to name the columns differently in your table, you can call the SP by INSERT ... EXEC theSP ... format

  • JGay 2041

    SSC Enthusiast

    Points: 142

    Peter,

    That was my next question.

    If the xml spreadsheet does not contain all the columns that exist in the table, the error will occur - yes?

    The xml spreadsheet has 80 columns that match the table, but the extra 37 that the table has is what is throwing the error - yes?

  • peterhe

    SSChampion

    Points: 11362

    JGay,

    Both are yes.

    Since those 37 columns are nullable, the SP should ignore them. So I think you found a bug. Thanks.

    The fix is pretty simple. You can find the following code block, and add the new line to remove those columns from importing list.

    IF @DataTableExists=1

    BEGIN

    -- Check whether the data table has columns that is NOT NULL and is not defined in the Excel sheet

    IF EXISTS(SELECT 1 FROM #Columns c WHERE c.ExcelColNum=0 AND c.IsNullable=0)

    BEGIN

    RAISERROR(N'Some non-nullable columns defined in the table [%s] does not exist in the Excel worksheet [%s].',16,1,@OutputTableName,@WorkSheetName);

    END

    -- Add this NEW line

    DELETE #Columns WHERE ExcelColNum=0;

    END

    Add

    DELETE #Columns WHERE ExcelColNum=0;

  • JGay 2041

    SSC Enthusiast

    Points: 142

    Peter,

    No joy. That ran the Sproc without error, but it inserted all NULL values in every column of the table - nothing imported from the xml spreadsheet. Looks like the 5th select, see below, has issue.

    Results tab in debug mode;

    1st and 2nd select

    - ExcelColNum 0

    3rd and 4th select column headers: RID, Data, CellIndex, RowID

    - Data column displays the data from the xml sheet

    5th select column headers: ColumnID, ColumnName, ExcelColNum, DataTypeName, IsNullable

    - have these values: 119 Suffix 117 varchar(max) 1

    6th select column headers: all column names from table

    - Data in columns are all NULL

    Sproc Code:

    IF @DataTableExists=1

    BEGIN

    -- Check whether the data table has columns that is NOT NULL and is not defined in the Excel sheet

    IF EXISTS(SELECT 1 FROM #Columns c WHERE c.ExcelColNum=0 AND c.IsNullable=0)

    BEGIN

    RAISERROR(N'Some non-nullable columns defined in the table [%s] does not exist in the Excel worksheet [%s].',16,1,@OutputTableName,@WorkSheetName);

    END

    DELETE #Columns WHERE ExcelColNum=0;

    END

  • JGay 2041

    SSC Enthusiast

    Points: 142

    Peter,

    Thanks for putting the @Debug code in the Sproc. It has come in handy. I have a hunch for a fix, will post on Monday and send you the script to the hotmail account as mentioned in the comment section.

    JG

  • femc

    SSC Rookie

    Points: 29

    Hi. Just a quick question. Does anyone knows if this solution will work with a file that has more than 256 columns? I have tried many other methods of loading before, and once I reached 256 or more columns in Excel, I have not been able to load the file to SQL.

    Thanks again...

  • JGay 2041

    SSC Enthusiast

    Points: 142

    Peter,

    It's been a long day. I think I found a solution that will work for my XML sheets into existing tables.

    keeping all your suggestions:

    (1) - Comment section: -- Temp Table (lines: 297-301)

    add: AND e.is_identity=0 AND e.system_type_id<>189;

    (2) - Comment section: -- Physical Table (Lines: 305-309)

    add: AND e.is_identity=0 AND e-system_type_id<>189;

    In the receiving table we have 1 PK and 2 FKs so instead of adding the suggested line, I:

    chg to: AND e.max_length < 0;

    (3) - Section where RAISERROR reads ('Some non-nullable columns (Lines: 363-366)

    add: DELETE #Columns WHERE ExcelColNum = 0;

    Running the code:

    In your SQL: ImportExcel.sql Line 314 is: IF @FirstRowIsHeader=1

    Running the Sproc with these params:

    EXEC dbo.[uspImportExcelSheet]

    @ExcelFileName=N'C:\Conversion\Source\ETL\Feeds\SalesRowCnt10.xml',

    @WorkSheetName=N'Sheet1',

    @OutputTableName=N'dbo.Sales',

    @FirstRowIsHeader=1,

    @Debug=1

    SELECT * FROM Sales

    Yields:

    A table full of NULLS

    In my xml spreadsheet, the first row is the header row, So param: @FirstRowIsHeader must be 1.

    Back to the SQL: Changing IF @FirstRowIsHeader=1 to IF @FirstRowIsHeader=0, running the same execute statements above, yields:

    ---YES--- Exactly what I was looking for. 10 rows/117 columns of xml spreadsheet data inserted into an existing table on the database without the header detail in the 1st row.

    Testing with the 13K+ rows/117 columns of xml spreadsheet data yields: Nice! Works great!

    I also tested it leaving @FirstRowIsHeader=1 in SQL and changing the param @FirstRowIsHeader=0, this resulted in the header row added as part of the data - not good.

    Thanks!

    JG

  • peterhe

    SSChampion

    Points: 11362

    femc,

    I tried on an excel with 274 columns, it worked fine.

    JG,

    I did test the scenarios when I gave you the necessary changes in the previous posts. I tested again today, it worked fine. I created a table with an identity columns, three columns not in the excel, and four columns in the worksheet. The columns in the excel were imported with data, and only those three columns not in excel with NULL value. So I am not sure what's wrong in your side. Anyway, if your changes works for you, it is fine.

  • mgkirsch

    Newbie

    Points: 5

    Has anyone successfully tried to get the xquery section to work instead of using pivot?

  • celticfire63

    SSC Veteran

    Points: 202

    Stored Procedure removed? The link to the stored proc is reporting that it cannot be found. Would it be possible to repost?

    Thank you!

  • peterhe

    SSChampion

    Points: 11362

    Please use the link in the "Resource" section at end of the article.

Viewing 15 posts - 46 through 60 (of 70 total)

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