Import of text file - numbers destroyed!

[asperamanca]

Hi,

I've been working with the Excel (2003) text file import wizard for a
while, but I haven't had THIS sort of problem before (that is, I had,
but so far I could always solve it).

I have a text file with tab-delimited columns. There is one header
row, and a lot of data rows. Some columns are text, and some numbers.
Looong numbers, for example 8.20124159208057

The first strange thing was that Excel believed this file originated
from Chinese (simplified) - no idea why. I converted it to unicode,
then that worked fine.

In the preview of the import wizard, all looks fine. All the numbers
are displayed as they look in a text editor. Because I feel generous,
I inform Excel that it should use the dot (.) instead of my default
comma (,) as decimal separator (as you can see in the example above,
the number uses a dot).

But once I hit "Finish" everything goes pear-shaped:
The number I mentioned before becomes 820124159208057, in fact ALL
NUMBERS lose their decimal separator!
What does work is the row and column assignment (in fact, I have
imported many files using the same format and arrangement, without any
problems).

Now I'm sitting on a load of finely minced numbers, and wonder how I
can persuade Excel to keep my decimal separation, the way, in fact, it
showed in the preview.

If anyone out there has a smart idea about this, it would be much
appreciated!

Robert

 

[asperamanca]

Ok, I have a clue. If I manually replace the decimal separators in a
text editor (with the comma, as per locale settings), Excel can
suddenly import the file just fine.

It looks as if the decimal separator setting in the "Advanced" section
of the import dialog is simply ignored.

Robert

 

[David Biddulph]

Are you sure that in the wizard settings you haven't got the dot as your
thousands separator too? If you do, you'll get the symptoms you describe.
Your thousands separator shouldn't be the same as your decimal separator.