Csv Binaries import only importing first column

[eerba]

Hello,

I'm trying to import several csv files using the binaries option in power query.

everytime i try to import it only imports the first column and the data shows as "error".

there are many columns with data.

the file is formatted as follows:

first set of rows contains report title and report information

second set of rows contains the data with several columns

last row contains "*** End of Report ***" line

anyone know what might be causing this?

thanks in advance.

[LarsSchreiber]

Hello @eerba,

I am not sure to understand what you are meaning by "binaries option". When I import a csv file named "Mappe1" using the GUI, Power Query creates the following M script in the backend:

= Csv.Document(File.Contents("C:\Users\LS\Desktop\Mappe1.csv"),[Delimiter=",",Encoding=1252])

Is this helpful for you? If not please describe your problem a bit more. Thanks.

Regards,

Lars

[eerba]

Hi Lars,

thanks for your reply.

i'm using the Combine Binaries button in the query editor.

when it combines the files, only column 1 is return.

each source file has 56 columns with data.

here is the original file and query editor result:

source filequery editor

[pqian]

The CSV you combined has headers and is irregular (not every row has the same number of columns), so PBI did not detect how to split the columns.

Thus you will need to split them manually. Here's what you need to do:

1. (Optional) delete the changed type step (it's not doing anything)

2. Click on the little icon on the top left of the table, and choose "Skip rows", you want to skip the top 17 rows of header info to get to the table

3. Right click on column1, select "Split columns by delimiter...", then follow the dialog to split by comma. Now the dialog should auto detect how many columns there are.

Become the headers will repeat for the next file, you'll now end up with a table with the right number of columns but jagged rows (some rows come from the headers). You need to filter those out by removing nulls.

Alternatively, instead of skipping rows, you can get to the structure part of the data by filtering on rows that contain commas. Looks like your header info don't have any commas in them.

[LarsSchreiber]

Hi @eerba,

when you click into a cell with an error (beside the word error) you propably will get the message, that there are more columns than expected, right?! When you open you csv file in a text editor, does the first row have as many delimiters, as the row number 17 has?! Somehow Power Query has problems to examine, how many columns it has to handle.

Regards,

Lars

[eerba]

Yes, youre right. is there a way to remove the first rows in the editor and then re load the data?

i'll try stripping the top rows of the first file that loads and see if i can trick power query into loading all the column.

thanks for your help!