How can you loop through and populate data into tables from other excel sheets in specific positions?

Hey everyone,

So I am stuck on how to get a desired output with the data I have and could use some help please. I have 3 Tables that I am working with for this situation.

  1. Summary

  2. IDs

  3. Pass/Fail

The goal is to have all the IDs from table 2 to be populated number Column 2 (Firm ID) in table 1 (Summary). This is to give a list of each ID that is being tested. Next is to populate the columns after Firm ID with data from Pass/Fail.

Pass/Fail holds all the IDs that are missing a specific piece of data, denoted with “N/A”. If there is data present the cell would be “Y” and if it is N/A the cell would be “N”.

Is it possible to pull in this data from the different sheets into Summary using a loop?

Any help is appreciated!

Hi @tschmied

I don’t think your desired output table is possible. In general it’s not good idea to try to use KNIME as if it’s Excel.

You need to visualize a more KNIME-appropriate output table. Once that is done, it’s usually quite trivial to join separate tables into one.

If you need some tips, then you can read the book From Excel to KNIME which will give you some ideas as to how to adjust your thinking.

3 Likes

Thanks @elsamuel I had a feeling that was the case. Appreciate the input!

Hi @tschmied,

I think @elsamuel

is correct that this kind of output is not really optimal for knime.

Hopefully I understood your problem correctly.
You have table ID and Test with the same IDs, and you want to put then into the summary page?
Are the X Text in your Screenshot actually already filled with IDs you want to match?

If you cannot change the output file format for whatever reason, I see the following solutions (depending on your use case)

Easiest:
Write the results to an new Excel sheet and do Vlookups as excel formulas

Solution with native KNIME nodes:
Read in the summary page to knime from row 5 downwards. So you have a “normal” Knime table
Do your matching through the joining node


Rename the columns to column2, column3 etc
Then read the excel a second, this time only up until the start of the id row

Use the concat node row to put the result and excel template together

Finally you can write the result to a new excel or overwrite the previous sheet

Last option:
use POI with the java snippet node to write/read exacly like you want from/to excel. (KNIME to HSSF - Horrible Spreadsheet Format :stuck_out_tongue: )
However this will take a bit of java code - not really hard but maybe not the solution for everyone :slight_smile:
https://poi.apache.org/

2 Likes