Find row in table based on a text value and count rows in the table

[taylorpeter55]

Hi all,

I have an excel workbook with multiple sheets. There is a table in each of the sheets that has the same starting (anchor) point but the starting row and also the number of rows in each table can be different per sheet. Example of sheets below and test data in attachment.

Is there a way to find the anchor point (the word Anchor highlighted in green) and then dynamically read the number of rows the table? I need to read the full table area (i.e. from col.A to col.Q.

I could hardcode the number of rows each table will have but i’m conscious that the number rows on each sheet each month could change.

Sheet 1: Read data from row 3 to row 7

Sheet 2: Read data from row 7 to row 15

Knime question.xlsx (14.4 KB)

Thanks in advance.

Pete

[JPollet]

Hello @taylorpeter55,
If one can assume that the area is delimited by Anchor and Average and that there is only one area of interest in a sheet, this workflow can work

excel_area.knwf (68,3 Ko)

The main part defines rowindex min (for Anchor) and max (for the last Average) for the row filter.

Hope this will be useful.

Best,
Joel

[taylorpeter55]

Hi Joel,

Thanks very much for your response.

Unfortunately the version of the row filter node that you are using is only available in version 5.3 and above and my company is not using this version yet (and i’m unable to upgrade).

Are you able to replace these with the deprecated version of the row filter? If not, please can you tell me the configuration of the 4 MISSING Row Filter nodes that you used in the screenshot below please?

I’m hoping that I can replace the missing row filters with the row filter node that I have available (as circled).

Thanks in advance.
Pete

[JPollet]

Here is a version with the old Row Filter. I add a more general treatment in case of more than one block of data in a sheet (see sheet topic 3 in the Excel file in the data folder for the workflow).

excel_area_1.knwf (113,4 Ko)

To have a valid workflow, I make it doing something : here writing a table (in the data folder too) for each blocks and sheets. Of course this has to be adpated especialy if all the blocks have the same specifications.

Note that I use some Missing value node to propagate the fusionned cell in Excel.

Best,
Joel

[taylorpeter55]

Hi Joel,

This is great, thanks so much.

I’ll review the workflow and will report back

Thanks.
Pete

[JPollet]

You’re welcome !
There is a Math Formula node in the workflow and it’s doing nothing (+0) : Ieft it because it is necessary to adjust row index and row number. There are the same with the old Row filter but are differing by one with the new node.

Joel

[takbb]

You may also be interested in the Table Splitter node, introduced with KNIME 5.x which may be useful for part of this requirement, as it allows you to split a table at a row defined by a value in a column.

Table Splitter

[JPollet]

Thanks @takbb : I just discover this node ! Do you know if the search pattern can be a regex ?

Joel

[takbb]

Hi @JPollet . You’re welcome! Unfortunately I don’t think that’s an option… maybe one for feedback/ideas as it would certainly make sense to allow regex/wildcards

[JPollet]

Done !

Improve Table splitter node : wildcare, regex pattern or other search options

Hello, It seems to me that the documentation of Table splitter is unclear for the type of pattern available. It would be convenient to have (like other filter ou splitter nodes) the opportunity to select at least wildcare or regex or any other matching criteria options associated to data type (less than, greater than etc for numbers). Best, Joel

Joel

[taylorpeter55]

Hi Joel,

Thanks very much for the workflow - it’s been a really big help!

I have another very similar case but where there are two tables on the same page and I need to concatentate them.

Current:

Required

At this moment i’ve hardcoded the columns (i.e. Col1 to6, Col7 to 13 but i was wondering if there is a way of finding the word Trade (as starting column of each table) to split them and then concat back together?

Thanks,
Pete

[JPollet]

Hello !
If the tables look like the ones provided (especially only two blocks of data and no other block of data above), this workflow can work :

The idea is to transpose and the analysis made before can work again and transpose back.

excel_area_bis.knwf (51,0 Ko)

I use the Table splitter node suggested by @takbb

Best,
Joel

[JPollet]

I just see this thread : Extract rows based on variable position

The topic is close.

Joel

[taylorpeter55]

Hi Joel,

Thanks very much for this! This is pretty much an exact match to what i need to achieve

Thanks for all of your help!

Pete