Issue while importing CSV file

[Anonymous]

Hi,

I am facing a strange issue while importing a csv file into Power BI. The delimiter of the csv file is “,” (comma), whereas there are few column values with decimal numbers (for example 0,5). In Finland, the decimal numbers are represented as “0,5”, “0,75” etc. The rows with whole numbers are distributed in their respective columns whereas the rows with decimal numbers are not able to distribute in their respective columns.

I am importing thousands of csv files from a folder by transforming a sample file. The issue is only happening to the 1 csv file where I have replaced date from 2020-06-01 to 2020-05-01. Do you have suggestions, how can  I solve this issue ?

   Thank you in advance!

[Anonymous · Accepted solution]

Hi,

Thank you for helping with this issue. As I mentioned that the problem is occured only with 1 csv file. So I found out that there was no issue with the file settings. Therefore I opened the problemetic csv file into notepad(open with notepad) and the data was represented something like below :

"2020-05-01,ABC,28948349049,260,true,""0,5"",12840098"
"2020-05-01,DEF,3627287129,205,true,""0,5"",12840098"
2020-05-01,XYZ,1682732804,205,true,1,12840098
2020-05-01,teuuey,628728260,16,true,1,12840098
2020-05-01,kslkapwo,632872899,265,false,0,12840098

I removed the "" from 1st 2 rows of the data and again saved the csv file. This workaround actually resolved my issue.

[v-diye-msft]

Hi @Anonymous 

1. When importing a csv file, you automatically get a step "Changed Type".

In this step,you need to add a culture that uses a decimal point instead of a decimal comma, e.g. (in the formula bar):

= Table.TransformColumnTypes(#"Promoted Headers",{{"Textfield", type text}, {"Number field", type number}, {"Integer field", type text}}, "en-US")

[Fowmy]

@Anonymous 

Can you share a sample file to test after removing confidential data if any?

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube, LinkedIn