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 «««34567»»

Import Excel Spreadsheet to Database Tables Expand / Collapse
Author
Message
Posted Friday, February 19, 2010 8:28 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, September 22, 2014 11:03 AM
Points: 16, Visits: 110
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?
Post #869130
Posted Friday, February 19, 2010 8:35 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: 2 days ago @ 7:18 AM
Points: 531, Visits: 433
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;





Post #869136
Posted Friday, February 19, 2010 8:57 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, September 22, 2014 11:03 AM
Points: 16, Visits: 110
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
Post #869162
Posted Friday, February 19, 2010 4:40 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, September 22, 2014 11:03 AM
Points: 16, Visits: 110
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
Post #869512
Posted Monday, February 22, 2010 7:58 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, September 20, 2010 10:09 AM
Points: 3, 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...
Post #870370
Posted Monday, February 22, 2010 3:23 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, September 22, 2014 11:03 AM
Points: 16, Visits: 110
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
Post #870780
Posted Tuesday, February 23, 2010 7:07 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: 2 days ago @ 7:18 AM
Points: 531, Visits: 433
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.



Post #871108
Posted Tuesday, February 23, 2010 7:47 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, March 2, 2010 10:44 AM
Points: 1, Visits: 8
Has anyone successfully tried to get the xquery section to work instead of using pivot?
Post #871140
Posted Tuesday, February 23, 2010 12:04 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, June 6, 2013 6:35 PM
Points: 9, Visits: 69
Stored Procedure removed? The link to the stored proc is reporting that it cannot be found. Would it be possible to repost?

Thank you!
Post #871380
Posted Tuesday, February 23, 2010 1:19 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: 2 days ago @ 7:18 AM
Points: 531, Visits: 433
Please use the link in the "Resource" section at end of the article.




Post #871425
« Prev Topic | Next Topic »

Add to briefcase «««34567»»

Permissions Expand / Collapse