Get data based on specific text

[aalnabelsi]

Hello everyone,

How can I extract data from multiple sheets if the table position is different sometimes, but I know that the column I need will contain a specific texts at the top and at the bottom, so I want to get the rows between them plus the data in next 3 columns.

[ScottF]

Hi @aalnabelsi and welcome to the forum.

For best results, please post an example of both your input data, and what you expect your output to look like. Your description is a bit vague. Also, if you have an example workflow showing what you’ve already tried, you could upload that as well.

This topic may be useful for you:

How to Best Ask For Help on the Forum

How to Best Ask for Help Before we go into this subject: first, and absolutely foremost: always search before posting your question. Your question may have already been posted and answered in the forum. Searching can help you save loads of time and effort! Ok, with that out of the way, let’s go: How can you write a post that is most likely to get the help you need? You might think that logically, a good title for your post would be “Help with an error”, right? Well… not exactly. Put yourself …

[aalnabelsi]

Thanks @ScottF for your reply.

I will try to make it clear now with excel samples I uploaded in Excel (inputs/output). I have over 200 files and some files have multiple sheet from where I need to extract the data.

There is no standard format for those tables I am trying to consolidate . The only thing it might help is to identify some fixed titles (text) to use them as a reference to extract the data I need

Input
150. Bulk AAAAAA -DXXM050.xlsx (51.4 KB)

In file DXXM050 I added some comments explaining what I need to get the result and the same will be implemented on the other files

159. Bulk AAAAAA -DXXM030.xlsx (51.7 KB)

Output
Result.xlsx (22.2 KB)

I used the module below to get the data between the rows I need, but I still have add manually the column number where my reference is exist, but I want to automate the full process, so KNIME can find the tables I need to extract the data. Plus I need to know how to duplicate one cell in a new column.

Sorry for the complexity and if I am still not clear, but I can provide more details if required, and if there is no solution let me know please.

Thank you

[ScottF]

Based on a quick scan of your problem, it seems quite complex - mostly because you can never be certain of the format of the incoming table. I realize that’s not your fault, but if possible I would encourage who ever is providing you the data to standardize their formatting.

If that can’t be done, you are likely going to need to do a bit of scripting using something like the OpenPxyl package in Python to search for and identify rows and columns of interest. @mlauber71 has several sample workflows dealing with that package on the KNIME Community Hub that may be of use to you. (Maybe you are already doing this data search in the Java Snippet above, but since you only provided a screenshot, I’m not sure.)

Sorry that I can’t provide more useful feedback for you.

[mlauber71]

@ScottF

f that can’t be done, you are likely going to need to do a bit of scripting using something like the OpenPxyl

@aalnabelsi I think you could do this with KNIME nodes by identifying the ‘blocks’ in the Excel Sheet and then applying the rules you described. The secret is in marking the start and possible end of the table/bock and then filling the empty lines with the missing values node as described here:

Move head rows to a column

@willy_oracle typically you would mark all lines with a Counter. Then identify the ‘headers’ with a rule engine [IF Col1 LIKE “Head*” => Counter] and keep that (starting) row in a separate column. Then you fill the missings with the previous value so a block would emerge marking the data table: [image] So now you have the blocks marked an can use a Table Row To Variable Loop Start – KNIME Hub or Group Loop Start – KNIME Hub to do something with the blocks (export them delete lines with missing…

Please also note the linked threads there.

The sheets themselves might be identified by reading the sheets and selecting the ones that have the name part you described.

Read Excel Sheet Names

Since these seem to be common questions I think about writing a blog about them.

[aalnabelsi]

Thanks @ScottF. I am working to create a new template with standard format to simplify the process. For now I am trying to find a way to consolidate the old files (over 200 files)

[aalnabelsi]

Thanks @willy_oracle for your help.

The start point will be finding the block contains “AA CONTRACT NO.” Java Snippet node I shared can find the first and last blocks but I still have to specify the column. I want it find the blocks without specifying the column. I will look at the details you shared and see if I can find a solution.

Here is what I included in Java Snippet

if (c_B.equals(“AA CONTRACT NO.”)) {
out_start = ROWINDEX;
} else {
out_start = null;
}

if (c_B.equals(“TOTAL AA UNIT COST”)) {
out_end = ROWINDEX;
} else {
out_end = null;
}