CSV no data, but start from line 2 works ok

[rjodwyer]

Describe the problem/error/question

Loading in a CSV from a binary file(source email attachment) doesn’t immediately work.

Selecting start line 2 works, but then the header row is missed and rows don’t map to the next steps nicely.

Selecting Header row and start line 2 works, but the first actual data row then becomes the headers which isn’t right.

See further down for screenshot outputs.

No amount of other settings in the Extract from CSV options changes the outputs.

Opening CSV in Excel works fine.

I couldn’t find any similar issues, so apologies if has been addressed elsewhere.
thanks,

What is the error message (if any)?

No errors, nothing in dev console

Please share your workflow

Can’t share the flow as it has private data, and the source is an email, the attachments end up as binary data, and its those I’m telling the Extract from CSV to read. Extract from text works, but then I can’t do anything with the data as its all mushed together.

This is the configured Extract from CSV:

Sanitised CSV(with 50+ more lines in the real thing, but all the same until the totals at the end):

Customer Name,Product Code,Product Description,Quantity,Unit Price,Total Price,Billing Cycle,Invoice Period,Next Issue Date,Notes
"Client","WPS","Server - Server Agent","1","$50","$50","Monthly","01 Mar - 31 Mar","01-03-2024","",""
"Client2","WPU50","User - 50GB","60","10","$600.00","Monthly","01 Mar - 31 Mar","01-03-2024","",""

,,,,EX-GST,$650.00,
,,,,GST,$65.00,
,,,,Total Inc.,$715.00,

Share the output returned by the last node

There is no output whatsoever if I don’t tell it to start at line 2.

Header row enabled and start line 2:

Header row disabled and start line 2:

Information on your n8n setup

• n8n version: 1.27.2
• Database (default: SQLite): default
• n8n EXECUTIONS_PROCESS setting (default: own, main): default
• Running n8n via (Docker, npm, n8n cloud, desktop app): Docker
• Operating system: Ubuntu

[barn4k]

hello @rjodwyer

can you clean the file from the sensitive info and share it? As for me everything is ok with the node and sample CSV file.

Sample file:

h1,h2,h3
body11,body12,body13
body21,body22,body23
body31,body32,body33

workflow:

[rjodwyer]

thanks @barn4k

How do I upload binary file? It comes in from an email attachment in an office 365 mailbox and I use it as a binary file in the workflow.

I have a feeling its related to the line feeds in the CSV file, as I can see them when I convert to text.

Otherwise no idea why line 1 wouldn’t work(trying to use headers), but starting from line 2 does.

The only thing I can think of is a code node to deal with errant line feeds, then put it back into a binary to extract to CSV, but I am no coder, so don’t know where to start on trying that.

[barn4k]

@rjodwyer

How do I upload binary file?

You can download it, open it with a text editor (like Notepad), and share the first 3-4 rows without the sensitive info

[rjodwyer]

thanks, ah yes, then the CSV at the start is exactly that, I just changed the names and removed all the other rows as it seemed to only have an issue with the first couple of lines, starting from line 2 works no issues, its fine to ignore the last 3 as well.

I’m just wondering if I somehow need to use a code node to parse it all out and back in somehow, as I cannot see what is causing it grief, as Excel has no issues opening as CSV.

[rjodwyer]

Hi @barn4k,

A new file came today so I thought I would try it again to see if anything is different. No changes, still cannot parse unless starting at line 2, and then same result as above.

this is the file showing all characters via notepad++:

Still opens with Excel without any issues.

Using Cyberchef CSV to JSON works fine too.

Is it likely some issue with the CSV parser in n8n?

Edit: using the “extract from text file” then copying that result from n8n into a new file also still works normally in excel and cyberchef.

thanks,
Ryan

[barn4k]

I’ve found the issue.

You have a header row with 10 columns. But in the data rows (2+) you have 11 columns, so they do not match.
you can see the issue if you open it with Excel and use the Split into columns option

@Jon that may be something needed for the Extract From File node update

[rjodwyer]

Thanks @barn4k, that would now make sense, they are blank so I wasn’t seeing it.

I can get the CSV into text easily enough by the “extract text from file” so I could then run a regex replace or split against that data as a string, but in the past I couldn’t work out how to get it back into a binary so it would then be processed by the “extract from csv” function.

Is there a simple way to do that as I could likely work around this for now knowing that I need to trim a column off.

[barn4k]

The easiest option is to add a new column to the header.

You will need to replace the 1.csv with the actual file

[rjodwyer]

Thanks @barn4k,

That works really well, do I simply run another external command to garbage collect the file when the process is finished?

many thanks,
Ryan

[barn4k]

Yes, you can do some cleaning stuff after you will get the edited file back in n8n with another Execute Command node.