Import Excel Spreadsheet to Database Tables

Question

Post reply

Import Excel Spreadsheet to Database Tables

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

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

Jeff Moden SSC Guru Points: 1004323 More actions · Answer 1

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.

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)

JGay 2041 SSC Enthusiast Points: 142 More actions · Answer 2

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: 11469 More actions · Answer 3

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 More actions · Answer 4

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: 11469 More actions · Answer 5

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 More actions · Answer 6

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: 11469 More actions · Answer 7

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 More actions · Answer 8

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 More actions · Answer 9

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 More actions · Answer 10

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 More actions · Answer 11

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: 11469 More actions · Answer 12

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 More actions · Answer 13

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

celticfire63 SSC Veteran Points: 202 More actions · Answer 14

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: 11469 More actions · Answer 15

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