Forum Discussion
CSV file issue
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!
8 Replies
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.Thank you very much!!!!
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".
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:
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.
