SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Problem in uploading excel data to SQL using SSIS
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Sundar75
Starting Member

5 Posts

Posted - 06/21/2007 :  08:37:27  Show Profile  Reply with Quote
Hi,
Actually while loading data from Excel source to SQL destination I’m facing one problem that source column in excel say Customer_Id contains both Numeric and Alphanumeric type data. While loading this data to SQL, some times values are populated as NULL in SQL instead Customer_Id.


I set nvarchar as Datatype for Customer_Id column in SQL.


I would appreciate if I get solution for the same.



--Sundar

rmiao
Flowing Fount of Yak Knowledge

USA
7266 Posts

Posted - 06/21/2007 :  11:56:08  Show Profile  Reply with Quote
You need to change data type in excel.
Go to Top of Page

Sundar75
Starting Member

5 Posts

Posted - 06/22/2007 :  05:17:07  Show Profile  Reply with Quote
quote:
Originally posted by rmiao

You need to change data type in excel.




Hi,
I tried with data type text in excel.
Go to Top of Page

nr
SQLTeam MVY

United Kingdom
12543 Posts

Posted - 06/22/2007 :  06:10:39  Show Profile  Visit nr's Homepage  Reply with Quote
I've had this problem before but it was for a manual type load (don't nkow why I tried with SSIS) so I could just do it via access. And changing the datatype in excel didn't help nor did setting the first value to character.
I usually do this sort of thing with a macro or generating insert statements in excel.

Would be interested to hear a solution.


==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11749 Posts

Posted - 06/22/2007 :  06:21:51  Show Profile  Visit spirit1's Homepage  Reply with Quote
this apparently happens because excel takes first 8 rows to determine each column's data type.
so if in your first 8 rows contain only numeric it will be numeric.
i've read this a long while ago in an excel newsgroup.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
Go to Top of Page

Hommer
Aged Yak Warrior

766 Posts

Posted - 06/22/2007 :  14:09:31  Show Profile  Reply with Quote
Spirit is right.

There is kb article on ms site that talks about this.

If in your data there are enough row (more than 8 rows) of character data, you may try to sort it that they come up on top.

Another trick is to save the .xls to a .csv file and use a different driver to import.

Finally, there is always possibility of using a script in the transformation step to cast/validate data on its way in. For instance, dealing with the leading zeros.
Go to Top of Page

nr
SQLTeam MVY

United Kingdom
12543 Posts

Posted - 06/24/2007 :  17:54:55  Show Profile  Visit nr's Homepage  Reply with Quote
There's a registry entry you can change to get it to sample more rows - but I don't think it solves this problem. Didn't when I tried it.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

Sundar75
Starting Member

5 Posts

Posted - 06/25/2007 :  04:59:22  Show Profile  Reply with Quote
quote:
Originally posted by spirit1

this apparently happens because excel takes first 8 rows to determine each column's data type.
so if in your first 8 rows contain only numeric it will be numeric.
i've read this a long while ago in an excel newsgroup.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp





Yes, I know this but my excel contains more than 1000 rows of mixed data(Numeric & Alphanumeric)


--sps
Go to Top of Page

Sundar75
Starting Member

5 Posts

Posted - 06/25/2007 :  05:07:03  Show Profile  Reply with Quote
quote:
Originally posted by Hommer

Spirit is right.

There is kb article on ms site that talks about this.

If in your data there are enough row (more than 8 rows) of character data, you may try to sort it that they come up on top.

Another trick is to save the .xls to a .csv file and use a different driver to import.

Finally, there is always possibility of using a script in the transformation step to cast/validate data on its way in. For instance, dealing with the leading zeros.




Yes, based on first 8 rows of data type that will take that i know. But my case Excel contains more than 1000 rows of mixed type data(Numeric & Alphanumeric).

Without changing to .csv is there any possibility for this?

I tried with casting option too...

--SPS
Go to Top of Page

Sundar75
Starting Member

5 Posts

Posted - 06/25/2007 :  05:09:46  Show Profile  Reply with Quote
quote:
Originally posted by nr

There's a registry entry you can change to get it to sample more rows - but I don't think it solves this problem. Didn't when I tried it.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.




Yes , you are right(I think this Registry setting change would work)
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22713 Posts

Posted - 06/25/2007 :  06:20:08  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
Just curious if openrowset will have the same problem
Try this
http://sqlteam.com/forums/topic.asp?TOPIC_ID=49926

Madhivanan

Failing to plan is Planning to fail

Edited by - madhivanan on 06/25/2007 06:22:52
Go to Top of Page

bcave
Starting Member

2 Posts

Posted - 07/27/2007 :  15:53:15  Show Profile  Reply with Quote
Anyone found the answer to this one? I have been battling the same problem today too. It is not due to missing data/column data type mistake. I have data in my first few rows for this particular column.

I am getting only some of the rows of data imported.

I just noticed it and I am going to have to go back over several files worth of data to see if it happened previously. It seems pretty unreliable.

I am importing from an Excel workbook via SSIS. The data looks like this:


sec_050.xls	3	p00001	10"	HFG
sec_050.xls	4	p00001	1	HHL
sec_050.xls	5	p00001	1	HHL
sec_050.xls	6	p00001	12	HFG
sec_050.xls	7	p00001	2"	SG
sec_050.xls	8	p00001		
sec_050.xls	9	p00001	10/12	HHL
sec_050.xls	10	p00001	4	HHL


The first time around in the 4th column it did pick up the data in the rows that had non-numerics in them (eg: The '10"', '2"', and '10/12' ) and reported the rest of the values for the column as null.

I have updated my reg key (noted above) to get the first 256 rows, but this was not the issue because I didn't have the proper symptoms(tried it for grins, anyway).

I tried formatting the entire sheet as general, as text, using varchar data type in SQL instead of nvarchar, etc, etc.

I was able to get the numeric-only values for column 4 to come through, but this was at the expense of the rows of data that had the non-numerics. They did not come through.

I cannot convert to tab delimited and import because of the double-quote in the values. SSIS doesn't like the text delimiter also being inside the value/data.

I am going to try and remove the double-quote, but that still doesn't explain not picking up the values that are formatted as '10/12'

Any ideas?

Go to Top of Page

bcave
Starting Member

2 Posts

Posted - 07/27/2007 :  16:11:17  Show Profile  Reply with Quote
madhivanan,

OPENROWSET to a new table produced the same omissions.

All of the numeric data was retrieved, but no values containg other characters were imported.

very odd.
Go to Top of Page

karuna
Aged Yak Warrior

581 Posts

Posted - 07/27/2007 :  18:18:30  Show Profile  Send karuna a Yahoo! Message  Reply with Quote
quote:
Originally posted by bcave

madhivanan,

OPENROWSET to a new table produced the same omissions.

All of the numeric data was retrieved, but no values containg other characters were imported.

very odd.



Try openrowset with IMEX=1

[EDIT]
INSERT INTO TBLA
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=D:\testing.xls;HDR=YES;IMEX=1',
'SELECT * FROM [SheetName$]')
[/EDIT]

Edited by - karuna on 07/27/2007 18:21:34
Go to Top of Page

Weezy
Starting Member

USA
3 Posts

Posted - 12/06/2012 :  09:23:47  Show Profile  Reply with Quote
In the connection properties for Excel, I was able to get this to work by trying a variation on what karuna suggests by adding IMEX=1 in the extended properties:
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\paychex$\payrate.xls;Extended Properties="EXCEL 8.0;HDR=YES;IMEX=1";
Go to Top of Page

Law2rence
Starting Member

USA
6 Posts

Posted - 12/11/2012 :  08:53:20  Show Profile  Reply with Quote
I tried with data type text in excel.




Edited by - Law2rence on 12/11/2012 08:54:08
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000