CODEWITHSUNDEEP
excelwindowscsvformattingdecimal
Sven
Sven

Reputation: 59

How to read in decimals with dot-seperator correctly with Excel?

I am trying to read in a CSV file in Excel which contains decimals with several decimal places. My procedure:

  1. Click Data upper Tab
  2. Click From Text/CSV
  3. Choose my file that I want to read in
  4. Click Load

If I open the csv with a text editor, the file looks as follows:

Label pre-annotated,Label self-annotated,Begin pre-annotated,End pre-annotated,Begin self-annotated,End self-annotated,Difference Begin,Difference End
P,P,0,3.36998958333333,0,3.36998958333333,0,0.0
P,P,5.50998958333333,5.85998958333333,5.50998958333333,5.85998958333333,0.0,0.0
P,P,6.37998958333333,6.67998958333333,6.37998958333333,6.67998958333333,0.0,0.0
P,P,6.80998958333333,7.80998958333333,6.80998958333333,7.80998958333333,0.0,0.0
P,COND1,10.3299895833333,10.36996875,10.3299895833333,10.517009268921914,0.0,0.14704051892191394

where the decimal places start with a dot. However, after loading it into Excel the dots are not recognized and the table looks as follows:

Table loading in, numbers are not formatted correctlty

I tried changing the Excel decimal separator to dot and comma, but it did not help. Even when changing decimal separator to dot in Windows, it does not work.

Any help would be appreciated.

Upvotes: 2

Views: 2767

Answers (2)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60224

In PQ, you need to change type using locale (english-US in this case).

You can find this on the right click drop-down menu after you've selected one or more columns.

PQ will then interpret the separators accordingly.

The MCode for your example would be:

#"Changed Type with Locale" = Table.TransformColumnTypes(#"Promoted Headers", {{"Begin pre-annotated", type number}, {"End pre-annotated", type number}, {"Begin self-annotated", type number}, {"End self-annotated", type number}, {"Difference Begin", type number}, {"Difference End", type number}}, "en-US")

Note the "en-US" for the optional culture argument of Table.TransformColumnTypes function

Upvotes: 1

Sven
Sven

Reputation: 59

As comments have pointed out, this seems to be a regional issue with separators. Changing the separators in the Windows system or in Excel did not help with this problem.

The workaround that I found was adding SEP=, as the first line to my CSV file in a text editor.

Upvotes: 3

Related Questions