CSV without Carriage Return Importing as Single Column

S

SteelReyn

New member
Joined
Mar 1, 2016
Messages
32
Reaction score
0
Points
0
Hello - I tried importing a CSV file with Power Query, but it comes in as a single row with hundreds of columns. A simple double-click of the same file from Explorer opens the csv in Excel with no issues, 3 columns and a few hundred rows. When I open the file in Notepad++, I can see that each line ends with a line feed only. There is no carriage return before the line feed. Is that the cause of the problem? If so, is there a way to tell Power Query that the line feed alone indicates a new line?

I have a workaround of forcing the file to open as text, then splitting the single column, removing double quotes, etc., but that seems like a lot of extra for something that Excel is able to natively recognize and handle.

Thanks!

E: just realized that the title indicates a single column, but that's only with the workaround. A regular import as CSV results in the single row problem mentioned above.
 
Last edited:
Sorry for the late reply here.

To deal with this, import the file as a text file in Power Query. Then what you need to do is:

  • Right click Column1 and split by delimiter.
  • Choose a Custom delimiter
  • Choose Advanced Options
  • Choose "Split using special characters"
  • Change "Insert special character" to Line Feed

You will probably need to then go to Transform --> Transpose to flip it back into a single column.
 
Thanks, Ken. That's basically the workaround solution. If I open the CSV, it automatically opens as a single row with multiple columns. Once I change the source to text, I've got one column with multiple rows. I can then split that column by comma, promote the headers, and everything looks good.

It's obviously not that difficult, but I wanted to make sure I wasn't missing something more direct, i.e., telling the CSV version to look for a line feed only.
 
Ah, yes. My preference would be to just flip it to the txt format, then split it up. That's essentially what Power Query usually tries to do automatically anyway.

('d agree that Power Query should probably be a bit smarter about line feeds vs carriage returns.)
 
You must log in or register to reply here.
Back
Top