Search code examples
excelload

excel import txt file - decimal symbol not recognized

My .txt file contains rows like this:

1,2.10985,1.11142

2,2.10729,1.10968

3,2.08096,1.08162

4,2.07895,1.08044

5,2.07935,1.07885

6,2.08214,1.0822

7,2.07131,1.07032

However, after loading the data via 'Data->From Text/Csv' the columns look like this:

enter image description here

Changing the column type to number did not help.

Also in my control panel, the decimal symbol is a comma.

What can I do to fix this?

EDIT: Here is a screenshot of my Region settings. I can also upload the pictures in Englisch if neccessary. My country is Germany.

enter image description here

Solution

  • With your downloaded file, and setting my Regional settings to German(Germany), I can reproduce your problem.

    You will need to tell Power Query (that's what you are using when you do the Data=>from text/csv operation) that the source file is using US-centric values.

    To do this:

    • When the window opens, instead of Load select Transform Data.
    • When the PQ Editor opens, select Home=>Advanced Editor
    • Change the second line to read:
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}, 
    {"Column2", type number}, {"Column3", type number}},"en-US")
    • Note that we have changed both the data type and added the culture argument at the end
      • This could be done from the UI. I will leave you to explore that.

    The entire code in my Advanced Editor (might be different in yours due to language):

    let
    Source = Csv.Document(File.Contents("C:\Users\ron\Desktop\Invariants_at_QPs.txt"),[Delimiter=",", Columns=3, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}, 
    {"Column2", type number}, {"Column3", type number}},"en-US")
in
    #"Changed Type"

    And a screeshot of part of the results:

    enter image description here