Importing CSV US formatted numbers in Excel with localisation?

Question

I have a .csv file with the following values:

1488201602.653, 8.304700E-04, 3.079498E-03
1488201603.107, 8.677357E-04, 2.856719E-03
1488201821.012, 7.071995E-04, 4.147542E-03

As visible from the snippet, the numbers are in differing format: the first column has a full number, and a period . as a decimal point separator. The second and third columns have numbers in scientific notation, except a capital E is used, and again a period is used for the decimal separator; there are no thousands separator in any of the values.

When I try to import this in a Danish localized version of Excel 2016, what I get is something like this:

So, I'm apparently getting a ton of thousand separators as periods . in the first column, however, if I select the first number, the formula field shows this:

... meaning, the number that was originally 1488201602.653 in the .csv file, now became interpreted as the integer 1488201602653, which is completely wrong.

For the sevcond and third columns, if I select a number, then the formula field shows:

... meaning, the number that was originally 8.304700E-04 in the .csv file, then became 8,30E+02 in the cell, shown as 830,47 in the .csv, which is - again - completely wrong.

How can I persuade Excel to import the data in the .csv file, which in USA or C locale, in its proper numeric values, so they are shown properly under Danish localisation (that is, 1488201602,653 and 8,304700e-04)?

Answer 1

Well, I found a manual way to handle this issue, but it would still be nice to know if there is an automatic one.

First, get and install Notepad++ if you don't already have it.

Then, note that:

• Under US (or "C" language) localization, there is no thousands separator (i.e. it is an empty string, "") - under Danish localization, the thousands separator is period "."
• Under US (or "C" language) localization, the decimal separator is a period "." - under Danish localization, the decimal separator is comma ","
• The Danish localization demands that the E-notation exponent is written as miniscule letter e, not as a capital letter E

Then, open your .csv file in Notepad++, and possibly save it as a copy under a different filename. Then, do the following replacements in this order:

• Search for comma , -> replace with semicolon ; (replace all)
• Search for period . -> replace with comma , (replace all)
• Search for capital E -> replace with miniscule e (replace all)

Then save the file, and import it in Excel. When importing in Excel, remember to specify the semicolon ; as a CSV field separator - and the numbers (at least as per the OP example) should be read-in and interpreted correctly.