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


Import Excel Spreadsheet to Database Tables


Import Excel Spreadsheet to Database Tables

Author
Message
JGay 2041
JGay 2041
Grasshopper
Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)

Group: General Forum Members
Points: 22 Visits: 117
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
peterhe
SSC Eights!
SSC Eights! (892 reputation)SSC Eights! (892 reputation)SSC Eights! (892 reputation)SSC Eights! (892 reputation)SSC Eights! (892 reputation)SSC Eights! (892 reputation)SSC Eights! (892 reputation)SSC Eights! (892 reputation)

Group: General Forum Members
Points: 892 Visits: 451
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
JGay 2041
Grasshopper
Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)

Group: General Forum Members
Points: 22 Visits: 117
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
JGay 2041
Grasshopper
Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)

Group: General Forum Members
Points: 22 Visits: 117
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
femc
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 150
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
JGay 2041
Grasshopper
Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)

Group: General Forum Members
Points: 22 Visits: 117
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
peterhe
SSC Eights!
SSC Eights! (892 reputation)SSC Eights! (892 reputation)SSC Eights! (892 reputation)SSC Eights! (892 reputation)SSC Eights! (892 reputation)SSC Eights! (892 reputation)SSC Eights! (892 reputation)SSC Eights! (892 reputation)

Group: General Forum Members
Points: 892 Visits: 451
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
mgkirsch
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 8
Has anyone successfully tried to get the xquery section to work instead of using pivot?
celticfire63
celticfire63
Grasshopper
Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)

Group: General Forum Members
Points: 20 Visits: 73
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
peterhe
SSC Eights!
SSC Eights! (892 reputation)SSC Eights! (892 reputation)SSC Eights! (892 reputation)SSC Eights! (892 reputation)SSC Eights! (892 reputation)SSC Eights! (892 reputation)SSC Eights! (892 reputation)SSC Eights! (892 reputation)

Group: General Forum Members
Points: 892 Visits: 451
Please use the link in the "Resource" section at end of the article.



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