Missing columns after concatenating three CSV files

KNIME Analytics Platform
1

Hi everybody,

I´m a KNIME beginner trying to merge 3 CSV files. Therefore I wanted to use the ‘Concatenate’ node. Unfortunately it doesn´t work as I imagined.

Each of the 3 CSV files include 5 columns with exactly the same headers. In file no. 1 & 2 every column has at least a few records. In file no. 3 there are two columns without any record.

I concatenated file no. 1 & 2 and the result was exactly as I expected it. Every row of every column of both files was merged. In the next step I tried to concatenate the already merged files 1 & 2 with file no. 3.

The new file (including all 3 files) now consists of every row of every file but two columns are missing. The two missing columns correspond with the two columns of file no. 3 which have no records at all.

Can anybody tell me what the problem is or does have a better solution for my plan of merging three files?

I already searched through this forum but I couldn´t find any topic regarding my problem.

Additional information:

  • I use ‘File Readers’ to read in the files and a ‘XLS Writer’ to create the new file (including all 3 files)
  • I have to create a new CSV file because a XLSX file would be too big to get opened by Excel

Thanks in advance.

You would help me a lot.

2

Have you viewed the output of File Reader for file 3 and checked whether the columns with no data are present there? You might need to change one or more settings of the File Reader node.

Alternatively if the Concatenate node is configured to 'Use union of columns' it should output all the columns even if they are missing from one of the inputs.

3

First of all thanks for the quick response.

Yes I viewed the output of File Reader for file 3 with a XLS Writer. All columns for file 3 are present.

The Concatetante node is configured to 'Use union of columns'.

Furthermore:

- If I open the context menu of the Concatenate node -> 'Concatenated table' every single column is given (none missing)

But in the end in the new file (containing all three files) two columns are missing.

Additionally I used the 'Statistics' node after the Concatenater. The two columns are also missing in the statistics table (although I included every column).

Any other idea?

Thanks in advance.

 

4

I also used the 'Value counter' after the Concatenater for the two missing columns.

'Occurences' (context menu of 'Value counter') for the two missing columns are available.

5

For everyone who may have the same problem:

In my case it was a data type problem.

I had to change the data type of the two empty columns of file no. 3 manually.

It seems that every column you want to concatinate has to have the same data type.

File Reader -> Configure... -> Slider 'Settings' -> Preview -> right click on the concerning column.

6

Try manually changing the column names using the ‘Column Rename’ node instead of changing it from the file Reader. This did the trick for me, when I had a similar issue.

7

I have the same issue.

Concatenating four files from same source. The output report has less columns as I found out that

“Each column has to be same format” - they have to be all “String" or all “Integer”.