Ignore carriage return in .csv file imported from sharepoint.

[msciarrino]

Hello Everyone,

I have .csv files that are stored on a share point. I am able to pull the file into PowerBI/Power Query without issues. The problem I am having is that some of the lines in the .csv files have a new line internal to the data. How can I ignore the new line in my data while still pulling the data from sharepoint?

Currently, without fixing this, it splits my data into 2 rows at the carriage return causing errors in my dataset.

I found this related topic, but I couldn't figure out how to apply it to items being imported from sharepoint.

https://community.fabric.microsoft.com/t5/Desktop/Carriage-Returns-Splitting-Data-to-a-new-row-since...

Thank you

[christinepayton · Accepted solution]

If you connect to it locally following what they did in your link as a local CSV file, then edit the path in the query editor to the SharePoint path, I'd expect it to keep whatever settings you used originally. This video shows how to replace the path with the correct URL (it's a bit tricky to get in the UI): https://youtu.be/2HZ8l23RXnc?si=VdgdNFmBlK4x8lVV 

[msciarrino · Accepted solution]

For those who are still looking for an easier answer. I just found this post that did exactly what I was looking for without having to load the file in manually first.

https://community.fabric.microsoft.com/t5/Power-Query/Line-Break-CSV-Files/td-p/1429160

[christinepayton · Accepted solution]

If you connect to it locally following what they did in your link as a local CSV file, then edit the path in the query editor to the SharePoint path, I'd expect it to keep whatever settings you used originally. This video shows how to replace the path with the correct URL (it's a bit tricky to get in the UI): https://youtu.be/2HZ8l23RXnc?si=VdgdNFmBlK4x8lVV 

[msciarrino]

Thank you this did work as you suggested.

I have multiple .csv files that I was pulling in for one table with the file names as the differientiator. Any ideas on how to get this to work without doing it manually one by one? I can do this process for the 3 files I have now but in the future its going to get annoying real quick.

Thanks again!

[christinepayton]

Are you combining the CSVs? This is what the SharePoint folder connector is for if so. If you need to change the path reference for the folder connector, there's a link in the first video description to do that too (it's a bit more complicated than a single file).