Hi, I am new to the KNIME platform hence the basic question.
I am trying to open multiple CSV files from a folder with almost the same schema however I am getting an error.:
I followed the loop file example
ERROR File Reader 3:4 Execute failed: For input string: "08:44 - 13:00" In line 2 (File-1-Row0) at column #34 ('Unpaid Break').
How can I go about it?
CSV Files.zip (102.0 KB)
Test.knwf (14.8 KB)
My best guess is from the description of the File Reader node:
In case this node is used in a loop, make sure that all files have the same format (e. g. separators, column headers, column types). The node saves the configuration only during the first execution.
Alternatively, the CSV Reader node can be used as it checks the configuration in every iteration of a loop.
All your CSV files are different. The File Reader node can’t handle this. Switch to the CSV Reader node, AND configure the Loop End to allow changing table specifications.
The downside here is that you’ll have to modify the RowIDs at a later stage.
So I tried using the CSV Reader.
There is an empty column and I am unable to get it run.
WARN CSV Reader 0:4 Didn't get any value for column(s) with index #8. Please verify column type(s)
What could be the best way forward?
I want to automate this step
Shangri La.knwf (14.3 KB)
I am on mobile right now. But my idea would be to user r readr to import messy CSV files.
You can find examples here:
You would have to install R
There is an empty column and I am unable to get it run.
I don’t think these things are related.
WARN CSV Reader 0:4 Didn't get any value for column(s) with index #8. Please verify column type(s)
You got this warning because the csv files all have empty columns. The best way to deal with that would be to clean up your csv files.
The workflow won’t run because you haven’t configured the Loop End node to allow allow changing table specifications. When I run it, I get a single table output with 6311 rows.
I don’t understand why you still have the Java Edit Variable node and why there’s now a Table Validator (Reference) node with no reference table being piped in.
Oh Great! I removed the Java Edit Node
(I followed the loop example hence, it was a hangover of that process I was trying to emulate and the Table Validator node is also the effect of another similar post; I guess, this is an iteration ; apologies of that!)
Here is what I did.
I got rid of the Java Edit Node and the Table Validator Node.
I added a CSV writer node to write the output and I got the error:
WARN CSV Writer 0:6 Input table must only contain String, Int, or Doubles
I did not get an output. I dont really know how to manage this. I can use some help.
I then changed it to Excel writer node and I got an output (But thats not what I want really. I want a CSV file dump).
All your help so far is much appreciated.Shangri La.knwf (13.2 KB)
@harshadbarge I’ll walk you through my troubleshooting process. Hopefully this is more instructive than me just throwing out what seems like random suggestions.
This error message suggests that there’s something wrong with one of your columns. Basically, the CSV Reader node wants only strings, integers, or double-precision floating-point numbers, so there must be some column that’s misbehaving.
When I open the results of the Loop End node (the last node to complete successfully), I look at the column headers, and I immediately see something strange - there’s a question mark in the Cost Centre column header. What’s going on there?
The numbers all look like integers, so why isn’t this being reflected in the header?
I switch over to the Spec tab to get more details:
Weird. The column type is marked as “Non-Native”.
I scroll down to see what else is in the column:
Now I see that there aren’t just integers in the column. There are strings as well. So the column type should be string.
I recall that this table is a result of concatenating several csv files, and I had to scroll down pretty far to get to values that weren’t ‘200’.
I run one iteration of the loop and see that Cost Center is recognized as integer:
I examine all the csv files in Excel, and piece together the following:
In the first csv file, the values in this column are integers, and KNIME recognizes them as such and sets the column type to integer. But in one csv file, the values are strings, and KNIME has no idea what to do with it since the format changes mid-loop. It resolves this conflict by assigning the column an “unknown” format, and the loop ends successfully.
However, as we saw above, the CSV Reader can’t deal with unknown formats.
A solution is to insert a Column Rename node after the Loop End node before the CSV Writer, telling KNIME to switch the column type to string:
Good luck!
Thank you for the walk through the problem and the solution. I would suggest to change the type not via rename but by to string node if that is possible since the changing of types through rename might result in some strange behaviour:
@mlauber71 thanks for pointing that out. That is indeed strange behavior, I’ll have to keep it in mind for the future.
I’d tried placing a Number to String node after the Loop End node but it wasn’t working because it wouldn’t recognize the Cost Centre column as a number. And I assumed this would be the case regardless of where I put the conversion node.
However, testing it again, it looks like having the Number to String node inside the loop immediately after the CSV Reader does give the desired result, as it converts the Cost Centre integer columns from the first few csv files into strings, and ignores the same column if it’s already been recognized as containing strings.
Cheers!
This is exactly what I needed.
Thank you for the walkthrough.
I need to explore the different nodes available in Knime now. What they are and the functions they do.
This not only solves my problem, it also encourages me to solve further ones a lot better.
This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.
