How could I concatenate multiple tabs (20+ tabs) from the same xls file into one single csv/xls file

Hello all,

 

I am a new knime user and I need some gulidience here.

I am working on a excel speadsheet with multiple tabs where only one column from each tab will be useful to my final report.

I know that it is simlple to use xls reader to open each file again then to joine these tabs together. However, it will just be a inefficient as copy and paste really?

Please point me to the right direction.

Many thanks

 

Florence Lai

 

 

 

Florence,

You'll be looking at a sequence of "Read XLS Sheet Names", followed by "Table Row to Variable Loop Start", then the XLS reader parametrised by the "Sheet" flow variable, then your column / row / missing filters and potetential renames, and finally the simple "Loop End" or "Loop End (Column Append)" node.

HTH,
E

P.S.: Done in KNIME 2.12, in which I just needed to do just this task anyhow. KNIME 3.x may use slightly different node names.

Hello there,

 

I did try to use the above setting and it did not work.

I think the problem is all my data are from the same spreadsheet. It would be easy if they are from different spreadsheet but it is not the case.

 

Thanks

 

Florence

 

Florence,

It should work, really, especially with a single spreadsheet. The SHEET_NAME variable should be the one taken from the loop (called "Sheet" by default if taken from the "Read XLS Sheet Names" node), and the XLS_LOCATION could be taken straight from this initial node as well.

When I get to my private PC I'll set up a demo flow for you.

Cheers
E

Hello there,

Many thanks for your help. I have managed to get it work!! Yeah!!

However, at each of my data sheet, samples all have common sample name but different data (so egsample 1 has 5% acid in sheet 1 and sample 1 has 10% sugar i sheet 2). 

At the moment, my new sheet looks loke

sample 1 5% acid

sample 1 10% sugar

I could like my new sheet to look like

Sample 1 5% acid 10%sugar

how could I do that?

Thanks

 

Florence

 

 

 

 

Hi Florence,

Glad you made it in spite of my inability to attach anything... But re your latest question, I suppose using the "Loop End (Column Append)" node instead of the regular Loop End node should give you what you're after.

It's more complex if your sheets systematically alternate between acid and sugar - this could require nesting loops and splitting tab lists.

Cheers
E