batch csv import into one big table

KNIME Extensions Text Processing
#1

Dear all,

I would like to combine x numbers of csv-files into one big table file. The csv-files look like that:

csv-file01

"Reference position";"Coverage"
"1";"1603"
"2";"1611"
"3";"1639"
"4";"1742"
"5";"1742"
"6";"1742"
"7";"1742"
"8";"1742"
"9";"1742"

...

 

csv-file 02

"Reference position";"Coverage"
"1";"351"
"2";"358"
"3";"358"
"4";"358"
"5";"358"
"6";"359"
"7";"359"
"8";"359"
"9";"359"

...

csv-file ... and so on until csv-file x

 

Finally, I would like to have a table looking like that:

Reference position Coverage csv-file 01 Coverage csv-file 02 Coverage csv-file ... Coverage csv-file x
1 1603 351 ... xxx
2 1611 358 ... xxx
3 1639 358 ... xxx
4 1742 358 ... xxx
5 1742 358 ... xxx
6 1742 358 ... xxx
7 1742 358 ... xxx
8 1742 358 ... xxx
9 1742 358 ... xxx

 

I got it to batch import all csv file using this workflow:

But I don´t get it to generate the above described data transformation.

Is there a node doing this?

 

Thanks for your help

#2

A solution might look like that. However, there might be smarter ways:

DB Table Selector node

 

#3

I think the Pivoting node will help with your example.

If you add a node to extract the file name during each loop step, the collected results will contain three columns:  Reference position, Coverage, and filename.  Using the Pivoting node, group by the Reference position, pivot on filename and aggregate on Coverage, keeping the first occurrence (since there will only be one Coverage value for each Reference position/filename pair).

 

I used the String Manipulation node to append a column called filename using the expression:

  substr($${SLocation}$$, lastIndexOfChar($${SLocation}$$, '\\')+1)

(This expression takes the path in the Location flow variable and keeps only everything after the last ‘\ ‘ in the path, which should correspond to the input file name.)

The final output looks like this:

Reference position csv_file_01.csv+Coverage csv_file_02.csv+Coverage
1 1603 351
2 1611 358
3 1639 358
4 1742 358

Hope this helps.

Tim