Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss
troubles with delimited files
0 views
Skip to first unread message
shank
Nov 23, 2009, 9:55:37 PM11/23/09
to
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.
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!
Pete_UK
Nov 24, 2009, 5:08:41 AM11/24/09
to
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.
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
> st,,,boston,MA,03125,US,555-555-5555,CG,,9185,9990000000009185,2,10626315001,1,Y,UOL,,,store
shank
Nov 24, 2009, 4:24:04 PM11/24/09
to
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.
"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
"Pete_UK" <pash...@auditel.net> wrote in message
news:482970eb-b893-4414...@o13g2000vbl.googlegroups.com...
AdamV
Nov 25, 2009, 4:43:12 AM11/25/09
to
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.
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.
0 new messages