DTS vs Excel numeric conversion -- sql server (alternate)

Don.Vonderburg NO[at]SPAM nospam.com
5/13/2004 10:03:51 PM

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

Ellen K.
5/14/2004 12:09:50 AM

Formatting 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. :)

[quoted text, click to view]

Don.Vonderburg NO[at]SPAM johnstonesupply.com
5/20/2004 1:39:58 PM

Never thought of that one. Thank you.

Don

arjan20032001 NO[at]SPAM yahoo.co.uk
6/20/2004 11:27:18 AM

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

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server (alternate)

sql server (alternate) : DTS vs Excel numeric conversion