a solution

Hi,

I know this issue exisits in DTS but needs to check still is in SSIS, Also you guys may have a better solution for it.

Issue: When I try to import a column from excel which has data like A,B,C,D,E,4,5 in the destination table has the data type as varchar it imports only A,B,C,D,E and 4 & 5 as nulls. How to fix this.

Set the Excel connection Extended Property, IMEX=1.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1294377&SiteID=1|||Thanks, it worked. Appreciated.|||

Imex=1 worked for all the cells except few, this column has data like below

07AWA38V36 0717062042 715018020 07BCB01R17

even in this column some has been imported correctly and few are imported like 7.15E+...

Note: My desination column data type in Varchar.

|||Make sure that Excel doesn't have any formatting for the problem cells or the problem column.|||In the above example for the second row the data was formatted as text becasue it has the leading Zero where as the third is not formatted becuase it doesnt have any leading zero. the issue was in the third row coverting as 7.15E+.. while running the SSIS|||

yes, i′ve got just the same problem.

incredibly it didn′t happen the first time i runned the dts, but now..

|||

..and i just got it..

I selected the whole worksheek, converted the cells into numeric type (format/cells/numeric) and saved. Then i converted the cells into General type again, saved and runned the dts. And it works now.

Better not to use Text types when it happens. When you′ve got a Text type it doesnt work properly, and if you change to General => it doesnt work either. But if you had Numeric types and change to General then it works.

|||

When I change this to Numeric then I will loose the leading zero in the 2 & 3 row.

|||

? no, you wont..

It′s just an excel fail, i doesnt catch properly that there′s a number, not a text, when you had a text previously