Importing long numbers from CSV file

[Guest]

I'm trying to import data from a CSV file, where one of the columns contains
both text and numbers. This column is being imported as a "General" column.

The numbers in the CSV file are like this "6123011162730412"
After the data import, they are shown in Excel as "6.12E+15" or
6123010000000000

Any thoughts on how can this can be resolved would be much appreciated?

Many thanks in advance.

 

[Guest]

Sorry, I forgot to mention that I've already tried renaming from CSV to TXT
without success

 

[Ildhund]

Before you import, change the file extension from .csv to .txt. Then, using
the wizard, you'll be able to specify that the long numbers should be
treated as text.

Excel can only cope with 15 significant figures, so any "number" with more
(like a credit card number) will be truncated one way or another unless you
tell excel to treat it as text.

 

[Guest]

Thanks for the prompt reply Ildhund, but unfortunately that doesn't seem to
work either.

The result displayed AND the ACTUAL value are both 6.12301E+15 now, whereas
previously the ACTUAL value was "6123010000000000". (By actual value, I mean
the value in the formula bar when I click on the cell.)

This may have something to do with the text import wizard because the number
is displayed as "6.12301E+15" in the "Data Preview Pane" at step 2 of the
Wizard.

btw. not sure if this relevant but the "file origin" in Step1 is "437 : OEM
United States", I've also tried changing that to "1252 : Western European
(Windows)" without any success.

Any other ideas that might be worth trying ?

 

[Ron Rosenfeld]

Thanks for the prompt reply Ildhund, but unfortunately that doesn't seem to
work either.

The result displayed AND the ACTUAL value are both 6.12301E+15 now, whereas
previously the ACTUAL value was "6123010000000000". (By actual value, I mean
the value in the formula bar when I click on the cell.)

This may have something to do with the text import wizard because the number
is displayed as "6.12301E+15" in the "Data Preview Pane" at step 2 of the
Wizard.

btw. not sure if this relevant but the "file origin" in Step1 is "437 : OEM
United States", I've also tried changing that to "1252 : Western European
(Windows)" without any success.

Any other ideas that might be worth trying ?

Can you give a sample of the actual data in the file you are importing?

If I set up a file with long numbers; save it as a *.txt file, and then open
the file with Excel 2002, the wizard shows the original data at step 2, and I
can specify text for the type and have it imported correctly.

I wonder if there is something unusual about your data, or the process you are
using.


--ron