Append xls Cell value to all valid excel rows


I get a bunch of log file reports from various stations. The report header looks something like this:

The server name is in cell P2, the row headers are in row 12, and the log rows actually begin after that and go on for another 12000-15000 rows. Unfortuately there is nothing I can do about the formatting, this is what it is.

I get about 5 of these a day from different servers (will grow exponentialy soon) and they all get saved to a central repository where they get fetched by a KNIME workflow which currently starts reading at row 12 to pick up the headersand ignores any blank rows and columns.

What I need to do is make sure that each row of the log file can be linked to a specific server. I have some choices:

  1. Fetch the name from cell P2 and write it into a new column for each row of the log file and then write the updated file to a location before it gets picked up by my existing workflow.
  2. Sort the xls files based on the value in P2 and save them into different folders and then process and write each servers logs to separate folders.

Option 1 would be most efficient, and has many additional benefits for further analysis

If anyone has any idea of how I can get this done in KNIME I would really appreciate some help.





Resolved... There is probably a more elegant way, but until I get a bit more up-to-speed on KNIME this is working.

I added an additional "XLS Reader" module into the loop that reads all the log files, but configured the reader to read only the row that includes the server name. A "Table Row to Variable" module makes the cell value available in the workflow as a variable and then it is inserted it into the records table with a "Variable to Table Column" module.