cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Liism
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
viacka
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.

Gazzer
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
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

MBAS Carousel

Sign up for our May 4th event!

May the fourth be with you, join us online!

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.