Read range between texts

[E.T.S]

Hello,

I would like to read range from the text ‘Support’ until the text ‘Total’ like this:

I would like the bot to only read from Support ill the word Total and nothing else underneath or above

Please advise cheers.

[mkankatala (Mahesh Kankatala)]

Hi @E.T.S

You can use the Find\Replace value activity. Give the Total in the Value to find field, Select the Find option in the Operation dropdown, Create a variable in the Found at Field to store the range where value exist in the excel, let’s call the variable name as ValueFound.

→ Drag and Drop the Excel Process scope activity, insert the Use excel file activity inside of it.
→ Inside Use excel file activity insert the Find\Replace value activity.
→ After that use the Read Range activity to read the excel with a specific range.
→ In the Read Range activity, give the range as below

Excel.Sheet("Sheet Name").Range("A1:"+ValueFound.toString)

This will helps to read the specific range.

Hope it helps!!

[ashokkarale]

Hi @E.T.S,

Here is the solution you were looking for.

Output:

Code:
Demo.xlsx (9.8 KB)
Excel Work.xaml (16.5 KB)

Thanks,
Ashok

[E.T.S]

Hello,

Thank you for your reply.

I am getting the following error with this above solution:

Cheers

[E.T.S]

Hello,

Thank you for your reply. I am unsure how to use the read range for a specific range as the data will not always be a specific range - it will always be between the two texts

Cheers

[ashokkarale]

@E.T.S,

Click on Imports tab at the bottom of the workflow and add System.Text.RegularExpressions namespace.

Thanks,
Ashok

[E.T.S]

Hello,

Thank you for your reply.

When I do the above I get the following:

Cheers

[ashokkarale]

@E.T.S,

Refer this attached project
DynamicSheets.zip (967.5 KB)

Just ignore the other workflows. Excel Work is for you.

[E.T.S]

Thank you!

The starting and ending row is found which is great - however when assigning this to a variable the starting row changes:

[ashokkarale]

@E.T.S,

My bad. I didn’t noticed the regex.

Add + symbol to regex for strStartRow like this.

Thanks,
Ashok

[postwick (Paul)]

Just Read Range everything and then For Each Row in Data Table through the data, using an If to check whether the Support value has been encountered and the Total value has not - at which point you’ll just add the row to a new datatable. When the Total value is encountered just use a Break to stop the loop. When it completes you’ll have a new datatable with just the data you want.

[mukesh.singh]

Read all the data in data table and get the row index of Support and Total keyword, then you delete the row number greater than the row index of "Total " and less the "Support " keyword

Option 1:
Push the data in DB and and delete the record by adding the primary key using the same logic

Option 2:
Here’s the C# code to achieve what you described, @E.T.S it is Dynamic logic, it is just an example you have to create something like this

    int supportIndex = -1;
    int totalIndex = -1;

    // Find row indexes for "Support" and "Total" keywords
    for (int i = 0; i < table.Rows.Count; i++)
    {
      string value = table.Rows[i][0].ToString().Trim(); // Assuming keyword is in first column (adjust if needed)
      if (value.Equals("Support", StringComparison.OrdinalIgnoreCase))
      {
        supportIndex = i;
      }
      else if (value.Equals("Total", StringComparison.OrdinalIgnoreCase))
      {
        totalIndex = i;
        break; // Stop searching after finding "Total"
      }
    }

    // Check if keywords found and indexes are valid
    if (supportIndex == -1 || totalIndex == -1 || supportIndex >= totalIndex)
    {
      
      return;
    }

    // Delete rows between supportIndex (exclusive) and totalIndex (inclusive)
    for (int i = totalIndex; i >= supportIndex + 1; i--)
    {
      table.Rows.RemoveAt(i);
    }
  }
}

[E.T.S]

Thank you that works!