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

1
votes

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.

2
excelwindowscsvformattingdecimal
Change the datatype of column to Number – Sam Varghese
Does changing the data type help you overcome this problem? – Sam Varghese
@SamVarghese This only added .00 add the end of the numbers, as apparently excel recognizes the number as an integer, not a float. – Sven
(imho) Your regional setting contribute to this (it recognize , as decimal separator & not .). Try tweak on that (in your PC) or do the import @ Excel online instead. – p._phidot_

2 Answers

2
votes

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.

0
votes

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