| Author |
Topic |
|
Sundar75
Starting Member
5 Posts |
 Posted - 2007-06-21 : 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
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-06-21 : 11:56:08
|
| You need to change data type in excel. |
 |
|
|
Sundar75
Starting Member
5 Posts |
Posted - 2007-06-22 : 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
12543 Posts |
Posted - 2007-06-22 : 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
11752 Posts |
Posted - 2007-06-22 : 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 1980blog: http://weblogs.sqlteam.com/mladenp |
|
|
|
Hommer
Aged Yak Warrior
808 Posts |
Posted - 2007-06-22 : 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
12543 Posts |
Posted - 2007-06-24 : 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 - 2007-06-25 : 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 1980blog: 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 - 2007-06-25 : 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 - 2007-06-25 : 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
22864 Posts |
|
|
bcave
Starting Member
2 Posts |
Posted - 2007-07-27 : 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" HFGsec_050.xls 4 p00001 1 HHLsec_050.xls 5 p00001 1 HHLsec_050.xls 6 p00001 12 HFGsec_050.xls 7 p00001 2" SGsec_050.xls 8 p00001 sec_050.xls 9 p00001 10/12 HHLsec_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 - 2007-07-27 : 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
582 Posts |
Posted - 2007-07-27 : 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] |
|
|
|
Weezy
Starting Member
3 Posts |
Posted - 2012-12-06 : 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
6 Posts |
Posted - 2012-12-11 : 08:53:20
|
I tried with data type text in excel.    |
|
|
|
|
Problem in uploading excel data to SQL using SSIS