CODEWITHSUNDEEP
excelcsvlocalization
sdaau
sdaau

Reputation: 38641

Importing CSV US formatted numbers in Excel with localisation?

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:

enter image description here

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:

enter image description here

... 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:

enter image description here

... 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)?

Upvotes: 5

Views: 3819

Answers (2)

sdaau
sdaau

Reputation: 38641

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.

Upvotes: 2

R3uK
R3uK

Reputation: 14537

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

Upvotes: 1

Related Questions