troubles with delimited files

  • Thread starter Thread starter shank
  • Start date Start date
S

shank

I receive orders via CSV files. Below is one of the troublemakers. If you
notice, the sku# is 9185 and the 16 digit following ends in 9185. Great! No
matter if I open as CSV, TXT, or cut-n-paste, then opt to delimit into
columns, the 16 digit number will always resolve to 9990000000009180 which
is a mismatch. Also, with the cell not wide enough, it displays as 9.99E+15.
All other similar 16 digit numbers would display as 999E+15 and delimit
without issue.

0140160852,John Doe,222 daley
st,,,boston,MA,03125,US,555-555-5555,CG,,9185,9990000000009185,2,10626315001,1,Y,UOL,,,store

What am I doing wrong here?
thanks!
 
Import the file as a .txt using the Data Import Wizard. On the third
panel of this you can specify how you want each field of data to be
treated, and for that field you will have to set it to Text rather
than General.

Hope this helps.

Pete
 
I did. However, that slows the process way down. Anyway to get Excel to
"assume" text on any particular fields for given file types? I supposed that
would be a script thingy. If ext "xyz" is opened make field 4 text... etc.

thanks


Import the file as a .txt using the Data Import Wizard. On the third
panel of this you can specify how you want each field of data to be
treated, and for that field you will have to set it to Text rather
than General.

Hope this helps.

Pete
 
Pete is correct, but to clarify *why* this works - Excel will only keep
15 significant digits of a number (this is well documented), so it is
actually storing all the way up to ...918 and then 'fills in' the last
digit with a 0 when it displays. If it was 20 digits long, you would see
5 zeroes at the end.
This is a common problem with serial numbers. I've also seen people
complain that it does the same with credit card numbers, although the
prospect of people storing whole CC numbers in Excel is more frightening
than the problem the mismatch causes.
 
Back
Top