Data loading issue from a particular excel column into a table

Question

Post reply

Data loading issue from a particular excel column into a table

Mh-397891

SSCertifiable

Points: 6698
More actions
August 11, 2008 at 2:55 pm

#125509

I am using SSIS Import and Export Wizard for loading the data from Excel into a SQL 2005 database table. The source excel column has values like A, A101,B100,C,12,11,100,2000.
The values: A, A101,B100,C are getting loaded into the SQL table, but the numbers
12,11,100,2000 are getting loaded as nulls into the SQL table. The datatype for that column in the table is varchar(255). Please advice. Thanks!!

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

SrikanthSv Ten Centuries Points: 1197 More actions · Answer 1

Use IMEX =1 in the connection property string of the excel connection

that'll set the mode to import and all the values will be populated.

Regards
Venkat
http://sqlblogging.blogspot.com[/url]

Mh-397891 SSCertifiable Points: 6698 More actions · Answer 2

SrikanthSv (8/11/2008)
Use IMEX =1 in the connection property string of the excel connection
that'll set the mode to import and all the values will be populated.

Please let me know where I can set this property? Thanks for your speedy response.

SrikanthSv Ten Centuries Points: 1197 More actions · Answer 3

When u select the connection and check for the properties you can find a connection string

so add this tag in the connection string value

it'll be like

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=data.xls;Extended Properties=Excel 8.0;HDR=YES;IMEX=1

Regards
Venkat
http://sqlblogging.blogspot.com[/url]

Mh-397891 SSCertifiable Points: 6698 More actions · Answer 4

SrikanthSv (8/11/2008)
When u select the connection and check for the properties you can find a connection string
so add this tag in the connection string value
it'll be like
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=data.xls;Extended Properties=Excel 8.0;HDR=YES;IMEX=1

Great!! It worked, you are the best!!!!

jyoti_bhatt SSCommitted Points: 1950 More actions · Answer 5

I'm having the same problem and still can't get it to work