Nbrs Stored as Varchar being converted - XLS to SQL2000
-
The column is PathId which can have numbers that are 10 whole nbrs plus
a decimal and 6 nbrs after the decimal, or it could have 9 whole nbrs plus
a decimal and 3 nbrs after the decimal. Some data has 5 nbrs after the decimal. For ex, 2007882139.031 is the nbr in XLS and after the DTS
in the varchar(50) column, it is 2007882139.0309999. The XLS column
was set to general. I tried Balaji's advise from the following post:
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=19&messageid=179955#bm179973
where I highlighted the column in XLS and did Data, Text To Columns,
Delimited, Tab, General, Finished, Saved the XLS file and reran the DTS PKG - I got the same results as described above - it seems to be rounding
some of the decimal data.
Thanks for your help!
-
Try checking out this link - it refers to nulls, but it may work for your problem also. http://www.sqldts.com/default.aspx?254
Sandi
-
Sandi,
I should have mentioned that I'm using a modified package which has the Excel connection with the extended properties set to ';IMEX=1' (which is option 2 in the article that you reference). I had done this to solve another problem I was having with data in another column (one value is 1501 and the other value is D51SEVO and the D51SEVO value was being set to null).
A coworker suggested I should put the single quote in each cell for the PathId column, because I guess the single quote tells it to treat the data as text? Luckily many of the records had the same value in PathId, so it was easy to paste the data with the single quote in all the cells.
When I ran the DTS package, the data looks good, it no longer rounds the decimals in the PathId field.
Thank you for your help!
-
Glad you got it working
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply