| Author |
Topic |
|
R2D2RABEAU
Starting Member
13 Posts |
 Posted - 2006-11-14 : 07:45:22
|
| Hi,I am importing data from an excel file but for some reason SQL imports all data except certain data (varchar 10) that are somehow transformed as NULL????How can I fix this problem?Examples:CODE14714814915015115315402EQY -> Will be transformed as NULL!!!40848705EQY -> Will be transformed as NULL!!!05EQYE -> Will be transformed as NULL!!!06BND -> Will be transformed as NULL!!!712Thank you for your help, |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-11-14 : 07:48:23
|
| Beacuse of the first rows are interpreted as INT, the import treats the whole column as INT, hence converting 02EQY to INT will fail and thus inserting a NULL instead.If using OPENROWSET, try setting IMEX to 2.Peter LarssonHelsingborg, Sweden |
 |
|
|
R2D2RABEAU
Starting Member
13 Posts |
Posted - 2006-11-14 : 09:27:37
|
Hi,Thank you for your help!quote:
Originally posted by Peso
Beacuse of the first rows are interpreted as INT, the import treats the whole column as INT, hence converting 02EQY to INT will fail and thus inserting a NULL instead.If using OPENROWSET, try setting IMEX to 2.
My CODE has mixed values like shown in my first post. How can I make SQL accept INT and Varchar type values without it transforming either of them into NULLs?Although I set the column as VARCHAR (which should accept INT too, right?) it is changing the datatype according to the first row inserted?!NB:I am using SQL Server 2000'DTS.Thanks again. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-11-14 : 09:50:12
|
| Not if the target table has INT for that column.Or do you create the table on the fly?Peter LarssonHelsingborg, Sweden |
|
|
|
R2D2RABEAU
Starting Member
13 Posts |
Posted - 2006-11-14 : 09:55:41
|
Hi again,quote:
Originally posted by Peso
Not if the target table has INT for that column.Or do you create the table on the fly?Peter LarssonHelsingborg, Sweden
The Destination table in SQL has already been created with VARCHAR for the column in question; I am importing data from an Excel sheet.Can you thhink of an alternative? |
|
|
|
R2D2RABEAU
Starting Member
13 Posts |
Posted - 2006-11-14 : 10:16:45
|
Hi again,quote:
Originally posted by Peso
If using OPENROWSET, try setting IMEX to 2.
I found something similar to what you suggested:quote:
In the excel connections properties ( visible either thru disconnected edit, or a dynamic property task or thru the API functions ), there is a key called OLE DB Properties -> Extended Properties. Add the setting IMEX=1; to the existing value and your problem should be solved. You will be able to import the entire excel sheet data thru DTS without worrying about missed values. This setting puts the mode of excel as import, and retreives all values as text instead of doing a data type guessing.
Where can I find the OLE DB Properties KEY on Enterprise Manager? (I am tottally new )Thanx a million, |
|
|
|
mahesh_bote
Constraint Violating Yak Guru
298 Posts |
Posted - 2006-11-15 : 07:23:59
|
quote:
Originally posted by Peso
Not if the target table has INT for that column.Or do you create the table on the fly?Peter LarssonHelsingborg, Sweden
Peso,i m importing excel sheet (CSV). the same thing happens with me. I have one column Phone_No with varchar(25). some user has multi-ph no so they give me comma separated ph no. when i tries to import such file. it excludes those multi- ph nos & insert NULL. how to overcome this problem?thanks in advance,Mahesh |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-11-15 : 07:29:14
|
| Are you using OPENROWSET to import? Try to change the IMEX value to 1 or 2.Peter LarssonHelsingborg, Sweden |
|
|
|
mahesh_bote
Constraint Violating Yak Guru
298 Posts |
Posted - 2006-11-16 : 01:31:01
|
quote:
Originally posted by Peso
Are you using OPENROWSET to import? Try to change the IMEX value to 1 or 2.Peter LarssonHelsingborg, Sweden
Thanks Peso, can u explain in details. coz, i couldn't understand. I m filling the Dataset, and the using datatable, datarow, inserting one by one record into table.thanks,Mahesh |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
|
|
|
SQL converts Value into NULL!!