When importing CSV files comma separator is stored in some strange place

[fredriknorling]

I have several bi files that has data stored in CSV files first i used comma as separator but then I got the problem that my data contained comma because that is the separtor for deciamls in Sweden.

I changed to semicolon so then that shouldn't be a problem any more.

But then BI started to complain anyway so I need to replace comma for dot and then change the type from text to number.

and when I've done that it shows the decimal as comma.

Anybody know why ? it's a bit risky when decimal numbers is showns as full numbers 67,89 becomes 6789 that isn't good at all

[Grumelo]

The proper CSV format is like this

Column A,Column B, Column C, Column D

hello world,"Hello, I'm happy","Fred, Lorrain","12,1"

Having , in your Data is not an issue as long as it's encapsulated in ""

[danhoff]

this is actually not true... I have a CSV and one cell has this data

,AU,Australia,"Point POINT(-35.31,149.19)","PHARC19, CPACC19",,,,,,LOCBP7,

the highlighted text does not import into on cell/field;  the power bi LOAD simply gets out of whack on that row and does not import it

very odd;  my export of the data is defined to be csv with any comma's in the data encapulsated with ", and the export of the csv file is perfect... it is just power bi cannot handle that

I find that very odd;  I am sure I will fix, but the default behavior of power bi when importing a csv is not so good in this case

[danhoff]

I lied, sorry.

The encapsulated commas work fine.  Allowing a comma in the text.  But I found some other odd characters that even WHEN encapsulated make the data load go nuts (fail).  And it not consistent.  A data load of a cell with line feeds in the text works MOSTLY, but sometimes not.  Such is life, with dirty data.

[LarsSchreiber]

Hi @fredriknorling,

using a dot as separator is nothing I would suggest.

If you cannot use a semicolon because of your BI department, and connat use comma, because it is used as decimal in Sweden (so it is in Germany as well), I would suggest to use something that is hardly used in any other case. Try tu use th pipe "|" (AltGr+<). Or simply take a tab stop. This should should help splitting up lines into columns and shouldn't take you into trouble regarding your data.

Hope that helps,

Regards,

Lars

[fredriknorling]

I meant that I have a semicolon separated file today. but the problem is that columns that has numeric values with decimals that I convert to numbers will display wrong.
34,5 becomes 345

but If I do a textvalue replace to 34.5 and change the value to number then it is displayed as 34,5 and works

[kzmanus]

try this

[LarsSchreiber]

When you go into the Options in Power BI desktop the following way (sorry, only German screenshots)...

... which country do you see there. It seems your configurations are english speeking, so it interprets the comma not as europeans know the comma... 

Lars

[pqian]

The queries editor world obeys the "document culture settings", which is configurable per file by the methods @LarsSchreiber pointed out. Once this culture is set, we should be able to parse strings according to the rules in that culture (i.e., when set to Sweddish, you should see 123,5 been parsed into number type with value = 123.5)

There is, however, a problem with CSV document's auto detection logic. Until recently, the detection logic did not obey this culture settings. So depending on the shape of your CSV file, the source step we generate may contain incorrect configurations (like picking comma as the separator instead of semicolon). 

For now (as of Jan's update), you need to set the document culture first, then import the CSV as usual (without changing delimiters and periods). Then click EDIT and go into the queries editor window. In there, you can verify the delimiters and the types. I suggest you delete the "Changed Type" step and then double click on the Source step, change the delimiter to Semicolon yourself. Then select all columns, and hit "Transform->Detect type" in the ribbon.