| Author |
 Topic  |
|
|
Sundar75
Starting Member
5 Posts |
 Posted - 06/21/2007 : 08:37:27
|
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
|
| You need to change data type in excel. |
 |
|
|
Sundar75
Starting Member
5 Posts |
Posted - 06/22/2007 : 05:17:07
|
quote:
Originally posted by rmiao
You need to change data type in excel.
Hi,
I tried with data type text in excel. |
|
|
|
nr
SQLTeam MVY
United Kingdom
12543 Posts |
Posted - 06/22/2007 : 06:10:39
|
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. |
|
|
|
spirit1
Cybernetic Yak Master
Slovenia
11741 Posts |
Posted - 06/22/2007 : 06:21:51
|
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 |
|
|
|
Hommer
Aged Yak Warrior
693 Posts |
Posted - 06/22/2007 : 14:09:31
|
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. |
|
|
|
nr
SQLTeam MVY
United Kingdom
12543 Posts |
Posted - 06/24/2007 : 17:54:55
|
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. |
|
|
|
Sundar75
Starting Member
5 Posts |
Posted - 06/25/2007 : 04:59:22
|
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
|
|
|
|
Sundar75
Starting Member
5 Posts |
Posted - 06/25/2007 : 05:07:03
|
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 |
|
|
|
Sundar75
Starting Member
5 Posts |
Posted - 06/25/2007 : 05:09:46
|
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) |
|
|
|
madhivanan
Premature Yak Congratulator
India
22460 Posts |
|
|
bcave
Starting Member
2 Posts |
Posted - 07/27/2007 : 15:53:15
|
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?
|
|
|
|
bcave
Starting Member
2 Posts |
Posted - 07/27/2007 : 16:11:17
|
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. |
|
|
|
karuna
Aged Yak Warrior
581 Posts |
Posted - 07/27/2007 : 18:18:30
|
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 |
|
|
|
Weezy
Starting Member
USA
3 Posts |
Posted - 12/06/2012 : 09:23:47
|
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"; |
|
|
|
Law2rence
Starting Member
USA
6 Posts |
Posted - 12/11/2012 : 08:53:20
|
I tried with data type text in excel.
 |
Edited by - Law2rence on 12/11/2012 08:54:08 |
|
|
| |
Topic |
|
Problem in uploading excel data to SQL using SSIS
