Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Issue while importing CSV file

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!

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    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.

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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.

  • Fowmy's avatar
    Fowmy
    Super User
    6 years ago

    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

  • v-diye-msft's avatar
    v-diye-msft
    Community Support
    6 years ago

    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")

    Specializing in Power Query Formula Language (M)
     
    2. A even simpler solution. There is a setting; "Archive - Options and settings - Options - Regional Settings - Locale" which I changed to English (United States).