CSV Reader: too few data elements problem

[ewhulbert]

I am having some issues with the CSV reader trying to read a CSV output from an ERP system.

I get the warning “Didn’t get any values for columns with indexes #46, 47, 49, 51, 52, 53. Please verify column types.” This warning confuses me because I only have 20 columns in the data.

Then it fails to execute with the error: too few data elements (line: 2 (1637)

When I enable short lines I get the same warning, but it does execute. Though for some reason it gets rid of my column headings and puts in a ton of blank columns at the end.

Is there anything I can do about this? I am trying to build a tool that I can leave with a client and having to rename 19 columns and drop 30 or so others seems like an opportunity for errors down the line.

Thanks in advance,
Eric

[ScottF]

Hi @ewhulbert -

Have you tried using the File Reader node in place of the CSV Reader node? It’s a little bit better about handling data with inherent inconsistencies.

If you’re still having trouble, feel free to post a short snippet of your dataset, if you can. Then maybe we can work through the parsing issues together. Even an anonymized version of the data with similar formatting would probably work, if there are confidentiality issues.

[mlauber71]

Sometimes with more complicated files the R package Readr might help.

R - Readr - read CSV files

Error with File Reader node while running in a loop

I had a similar problem where there were some ‘dirty’ CSV files out of a large number of files and they were too large to just ‘edit’ them in Notepad++ (and that also was not a very nice process). I built a workflow using the R package readr, which seems to me more tolerant towards problematic CSV files and gives you some control how to handle mismatching quotes and so on. You might have to toy around with the settings of the read_delim function. [46] kn_example_import_csv_r_readr.knwf (52.…

String to document Error

The text file looks a bit challenging. Large lines with various special characters, text with commas, quotation marks and then , as column separator … what could possibly go wrong … but as usually Readr saves the day … (hopefully) [image] [image] kn_example_r_readr_string_document.knwf (396.3 KB)

[ewhulbert]

Thanks gents. I had tried the file reader and it was not working. It would either put everything as one line or just give an error. But then I fixed a different problem that csv reader was having, a random " in a variable, and now file reader works great. I had no re-checked file reader after that fix before asking, rookie move there.

I figured out some of the problem with the extra columns too. There appears to be an error where part of a row is duplicated and it extends to a couple of extra unnamed columns.