Merge CSV files with different columns and settings

[stevelp]

I’m trying to merge a dozen different CSV files from one folder into a single file. I’m using a method I’ve used before, as shown below:

However, when I try to run this, I get an error. The error I get depends on whether I’ve checked the “ignore spaces and tabs” box in the basic settings of the File Reader node. When I have it checked, I get this error:

Execute failed: Too many data elements

When I have that box unchecked, I get this error:

Execute failed: For input string: “233times” In line 2 (Row0) at column #27 (‘Follow Link Ratio’).

I have tried importing each of these files separately in the File Reader node without using the variable, and it seems that some files preview better with that box checked, and some do not.

The files all come from the same system, and the first 20 columns are identical, but the files do not all have the same number of columns. Some may have 25, some may have 30. I’m only interested in the data that’s in a couple of the first 20 columns.

In case it might be helpful, here are all of the other settings that I have configured on the File Reader Node.

Maybe there’s a setting I could adjust using a flow variable so that all of the fields are brought in as string values or something? Or a way for me to only import the first 10 columns?

[AnotherFraudUser]

Hi stevelp,

I think the following two settings in the “Loop End” Node should help you with the changing columns

Example Workflow:
csv_reader.knar (14.9 KB)

This should fix at least the difference in the number of columns

[AnotherFraudUser]

*if that does not fix your problem - maybe you can give two example files (without real data) to check?

[stevelp]

Thanks for the idea. I tried that, with no luck. I think it’s having an issue putting string values in the same column location as integer/double columns. Here are a couple files with some made up data. I need the data in the first 21 columns, but nothing after that.
Sample Files.zip (7.1 KB)

Here’s the workflow I’ve made for it so far: Sample_Different CSV files.knwf (13.5 KB)

[mlauber71]

From my experience the use of the R package Readr can help with ‘messy’ csv files. You could force all columns to be imported as string for example and deal with formats later.

Force R’s readr to import all columns of a CSV file as strings – mlauber71

[HansS]

Hi @stevelp

What if you use a CVS Reader node instead Sample_Different CSV files_cvs_reader.knwf (52.4 KB) ?

gr. Hans

[AnotherFraudUser]

Hi stevelp,

like mlauber71 already wrote - as far as I can see the example files works fine when using the csv reader.

In case you want to enforce that you do not get the different types for the same column names you can change the “Scan limit” in the config to 0 (then all columns are read in as String)

Then you could cast the columns to your desired format afterwards

[AnotherFraudUser]

*did you use the line reader for a particular reason?
Maybe that can be done in the csv reader as well

[mlauber71]

@AnotherFraudUser

In case you want to enforce that you do not get the different types for the same column names you can change the “Scan limit” in the config to 0 (then all columns are read in as String)

I did not know that, thank you.

[stevelp]

Thanks for the suggestion @mlauber71. I’ve tried to use this R package reader, but I’ve never used it before and I’m not sure exactly what I’m doing. I get the following errors when I try to use it.

ERROR R Snippet            3:20       Execute failed: Error in R code: 
Error: there is no package called 'readr'
Error: could not find function "read_delim"

Any ideas on what I’m doing wrong?

Also, it looks like this is designed to read the last modified file in a folder. I presume it would be able to be put into a loop so I could read all files in a folder as well, right?

[stevelp]

Thanks @HansS, apparently I didn’t put enough of the issues in my sample files. My actual dataset also has line breaks in quoted strings in the CSV files, which gives me the following error when using the CSV reader.

Execute failed: New line in quoted string (or closing quote missing).

So, I’m dealing with different columns after the first 20 or so, as well as line breaks in quoted strings. I haven’t been able to find a solution that fixes both of those issues yet. I’m attaching 2 new sample CSVs that also have line breaks in quoted strings.
Sample Files 2.zip (7.2 KB)

[mlauber71]

@stevelp you could take a look at this example if this is what you want to do

Force R’s readr to import all columns of a messy CSV file as strings - allow...

[mlauber71]

@stevelp

Error: there is no package called ‘readr’

You would have to install R and the package in order to use this node.

Install R alongside KNIME on Windows and MacOS
(general description, about Rserve slightly outdated, use the other links)

Install R alongside KNIME on Windows and MacOS

Over the last months I have collected a few entries about the installation of R alongside KNIME that contain some maybe useful hints how to overcome certain obstacles while installing them. I want to bring them together here. (If you are looking for a basic guide about how to install R in the first place you might look here or here). A few maybe unnecessary remarks upfront. Yes it takes some effort to get KNIME and R up and running but in the end you will be rewarded with the whole world of pos…

RServe 1.8.6 on MacOSX

Installing Rserve 1.8-6 on macOS 10.15. (Catalina)

@s_rupp, this is a known issue and we have a script for that. Gonna send you the script via PM tomorrow morning. We are working on a proper docu for the R extension that links that script. Best Mark

RServe 1.8.6 on Windows

Compiling Rserve 1.8-6 on Windows 7 (64bit)

Because I never found this advice anywhere I thought I’d leave it here to potentially help others: tl;dr On Windows 7 (64bit), try compiling the Rserve package like this: install.packages("Rserve",, "http://rforge.net/", type = "source", INSTALL_opts = "--no-multiarch")) The longer version: After updating the R installation that I was using with Knime from version 3.4.4 to 3.6.1, some R nodes in my workflows started failing. I quickly found that with R >= 3.5.0 you need the latest Rserve 1.8…

How I finally got R working in KNIME on Windows 10

Hi everyone, a few months back I struggled to get R working with KNIME, and in the end gave up, but on getting myself a new laptop I thought I’d try again… And with a fair amount of effort I finally succeeded! In the hope it may save someone else some time, below are the steps that worked for me. I’d also like to say the following topic really helped me along the way: Install R alongside KNIME on Windows and MacOS Steps: Install R. This is available from www.r-project.org and as of Apr 2020…

R and Rtools

how to import tables from .docx documents via R snippet

Not exactly sure what you mean by installed by both R and RStudio. Typically you should only have one R installation with one library. You could try and check your version’s library with this comand: .libPaths() The error messages hint that you do not have the relevant package docxtractr installed. And you will have to install RServe in the latest version. One thing I had success wit recently is described here: After you have done that you could tell RStudio to find the Rtools necessary to…

[stevelp]

@mlauber71 - Thanks for the clarification. I’m going to install R and see if I can get this all to work. I’ve been curious about dabbling in R in the past, and this is a nice excuse to start.

However, I currently build workflows for a remote team that executes them on their computers, and I’d love to find a solution that doesn’t require me to install R on all their different machines. If anyone else has any other ideas on this, I’m all ears.

[HansS]

Hi @stevelp

I took another look and made “a small adjustment” to my flow above. I used the File Reader node. But I selected for a delimiter: none . After reading the sample files, I used a Cell Splitter node (split by , ) , and for me it looks quite ok… Sample_Different CSV files.knwf (56.8 KB)

gr. Hans