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 [image] Sheet 2: Read data from row 7 to row 15 [image] Knime question.xlsx (14.4 KB) Thanks in advance. Pete

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

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 [image] 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

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). [image] Thanks in advance. Pete

Hi Joel, This is great, thanks so much. I’ll review the workflow and will report back :slight_smile: Thanks. Pete

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

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. [image] Table Splitter The Table Splitter splits the input table at the row that matches a given condition. The part of the table that occurred before the matching row is forwarded t…

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

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

Done ! [image] Improve Table splitter node : wildcare, regex pattern or other search options Feedback & Ideas 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

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: [image] Required [image] 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

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 : [image] 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

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

KNIME Analytics Platform

JPollet October 10, 2024, 10:46am 10

Done !

Joel