an Excel sheet with alphanumeric text and some of the cells are numeric.
Some of the cells contain numbers like 12345.6 and when DTS is done
importing it into a field that is nvarchar the results are
"12345.600000000001". I have tried:
1. Changing the format of the Excel column to text
2. Using the formula =text(a1,0) which only truncates the .6
3. Using the formula =t(a1) which will remove some numeric representations
4. Exporting the sheet to CSV or TXT first which will not enclose the cell
contents with ""
5. Beating the computer with a nine iron
None of these options work. Any idea anyone?
Don VonderBurgFormatting the cells as text after the data are there won't help.
Copy the cells to another location which is PREformatted as text, then
copy the copy back onto the original cells and try again. :)
On Thu, 13 May 2004 22:03:51 GMT, Don.Vonderburg@.nospam.com wrote:
>I am having a problem importing an Excel spreadsheet. I have a column in
>an Excel sheet with alphanumeric text and some of the cells are numeric.
>Some of the cells contain numbers like 12345.6 and when DTS is done
>importing it into a field that is nvarchar the results are
>"12345.600000000001". I have tried:
>1. Changing the format of the Excel column to text
>2. Using the formula =text(a1,0) which only truncates the .6
>3. Using the formula =t(a1) which will remove some numeric representations
>4. Exporting the sheet to CSV or TXT first which will not enclose the cell
>contents with ""
>5. Beating the computer with a nine iron
>None of these options work. Any idea anyone?
>Don VonderBurg|||Never thought of that one. Thank you.
Don|||Hi,
I vaguely remember that when you import the Excel file through DTS
that you can set the data type somewhere. Perhaps that helps.
When you look at the numbers that are wrongly imported in the Excel
formula bar, do you see the error as well? I guess when this is the
result in an Excel calculcated cell you may expect these rounding
errors. Remember that in Excel you never actually see the underlying
value. All values are always displayed using some kind of a display
mask. You can use the round function in Excel to round your results.
That should take care of it.
Just to make you aware of another way to pump your data into the
database. I wrote an addin for Excel called SQL*XL. Its goal is to
remove these hassles from the end user. You can use SQL*XL to get data
from the database into Excel or to pump data from Excel into the
database. It even lets you change retrieved data in Excel and post the
changes back.
If you are interested, have a look at SQL*XL at www.oraxcel.com
Best regards, Gerrit-Jan Linker
Linker IT Consulting Limited
www.oraxcel.com
No comments:
Post a Comment