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.

Answer 2

I would try like this with VBA (not tested) :

Sub ImportCSVFile()
    Dim xFileName As Variant
    xFileName = Application.GetOpenFilename("CSV File (*.csv), *.csv", , "Choose CSV", , False)
    If xFileName = False Then Exit Sub

    Dim wS As Worksheet
    Set wS = ThisWorkbook.Sheets.Add
    Dim rG As Range
    Set rG = wS.Range("A1")

    Dim QT As QueryTable
    With wS
        Set QT = .QueryTables.Add("TEXT;" & xFileName, rG)
        With QT
            '''Preserve initial format
            .PreserveFormatting = True
            '''Select the delimiter
            .TextFileParseType = xlDelimited
            .TextFileCommaDelimiter = True
            '''Choose refresh options
            .RefreshStyle = xlInsertDeleteCells
            .RefreshOnFileOpen = False
            .RefreshPeriod = 0
            .SaveData = True
            '''Import the data
            .Refresh BackgroundQuery:=False
        End With 'QT
        '''Force the formatting
        Call .Columns("1:3").Replace(".", ",")
    End With 'wS
End Sub