cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper I
Helper I

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!

1 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. 

View solution in original post

7 REPLIES 7
Frequent Visitor

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.

Resolver II
Resolver II

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

 

image.png

 

 

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: 

example csv.PNG

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.

@Gazzer Unfortunately, it didn't help. 

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. 

View solution in original post

Hi @Liism

 

Glad to hear you got there in the end.

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Microsoft Ignite

Microsoft Ignite with Arun Ulag

Featured Session: Drive Data Culture with Power BI- Vision, Strategy & Roadmap. Register here https://myignite.microsoft.com #MSIgnite #PowerPlatform #Power BI ​

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors