A
Adam J
In Excel workbooks and especially csv files saved from Excel, very long
numbers are forced into a scientific format. The biggest pain with this is
when it comes to FedEx tracking numbers. In Excel, you can either have to
format your entire column, (either as text or as a number with no decimals),
before you paste your data, which isn't such a bad workaround.
But when you save this data as a csv file, all of that formatting gets
stripped out, and the number is forced back into scientific format. The
worst part of all is, that after 15 decimal places, any values are truncated
to 0. FedEx tracking numbers are 22 characters in length, so
"9612019359562110201829" becomes "9612019359562110000000", which is
completely useless for tracking look ups.
If I go to do a BULK INSERT in SQL Server from this csv file, I need to put
some text in each cell so that the FedEx tracking number doesn't get
converted to a scientific format and get rounded down to 0 after the 15th
number. This is an major problem when there are already database triggers in
place that are not expecting the character that I had to insert.
Any sort of option to remedy this in future Excel versions would be awesome,
although it may just be the nature of csv files. (I have also noticed that
csv files strip out leading 0's, for instance, in zip codes...but that's a
whole other topic).
Thanks.
-Adam
----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.
http://www.microsoft.com/office/com...6-5452306ef0d7&dg=microsoft.public.excel.misc
numbers are forced into a scientific format. The biggest pain with this is
when it comes to FedEx tracking numbers. In Excel, you can either have to
format your entire column, (either as text or as a number with no decimals),
before you paste your data, which isn't such a bad workaround.
But when you save this data as a csv file, all of that formatting gets
stripped out, and the number is forced back into scientific format. The
worst part of all is, that after 15 decimal places, any values are truncated
to 0. FedEx tracking numbers are 22 characters in length, so
"9612019359562110201829" becomes "9612019359562110000000", which is
completely useless for tracking look ups.
If I go to do a BULK INSERT in SQL Server from this csv file, I need to put
some text in each cell so that the FedEx tracking number doesn't get
converted to a scientific format and get rounded down to 0 after the 15th
number. This is an major problem when there are already database triggers in
place that are not expecting the character that I had to insert.
Any sort of option to remedy this in future Excel versions would be awesome,
although it may just be the nature of csv files. (I have also noticed that
csv files strip out leading 0's, for instance, in zip codes...but that's a
whole other topic).
Thanks.
-Adam
----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.
http://www.microsoft.com/office/com...6-5452306ef0d7&dg=microsoft.public.excel.misc