CSV file issue

[Anonymous]

Hi, 

I'm trying to import a CSV file that is using commas as separator. But once I import the data, then some rows are separated and some not. If I try it in Excel, then it separates all the rows, but not in Power BI. 

I also tried custom separator and then typed ',' there, but it also didn't work. 

What might be the issue here? 

Thank you for the help!

[viacka]

I've just faced that issue as well. Just in case someone still needs the solution: the easiest way so far is to set the QuoteStyle parameter to QuoteStyle.Csv of the Csv.Document function in the Advansed editor of your connection. 

As the documentation states (https://docs.microsoft.com/en-us/powerquery-m/csv-document😞
QuoteStyle: Specifies how quoted line breaks are handled. QuoteStyle.None (default): All line breaks are treated as the end of the current row, even when they occur inside a quoted value. QuoteStyle.Csv: Quoted line breaks are treated as part of the data, not as the end of the current row.

[xmhstat]

Thank you very much!!!!

[Gazzer]

Are you able to take a screenshot of what you see when it fails?

I often have CSV files which include carriage returns within fields and these can cause the rows and columns to get a bit messed up. It might be worth checking this first, although I would expect you to have an issue opening in Excel if this were the case. To check/correct this, open the source item and set Line Breaks to "Ignore".

[Anonymous]

Hi @Gazzer, 

From where can I find the line breaks option, as when I import the file with Text/CSV option, then this is what I get: 

[Gazzer]

You have to go back to the Source step after the initial import.

Go to the Applied Steps section on the right-hand side and click the little gear wheel icon next to "Source" to edit the import settings. It should look a bit like the image in my previous post.

[Anonymous]

@Gazzer Unfortunately, it didn't help. 

[Anonymous · Accepted solution]

The problem was with the file. After downloading it from the source system, I didn't open and save it as a csv file anymore, but just took the file from the Downloads folder and now it works. 

[Gazzer]

Hi @Anonymous

Glad to hear you got there in the end.