Extract 2 data tables from one excel file

[Debasis_Nayak]

TestData.xlsx (10.2 KB)

Hi All, I have a excel file where I wanted to extract 2 datatables. The number of rows in the table are not fixed rather updated every month. Any idea how can I extract both the data tables separately.

[NIVED_NAMBIAR (NIVED N)]

Hi @Debasis_Nayak

Try this logic

for reading the first table

1. First use the lookup range to look for the sentence Instruktionen / Instructions:

This activity will return the cell address where the sentence is present. In this case, it is present in A20 cell.
Assume the lookup range actiivty store the cell address in a variable strAddress
First use the Regex to extract the 20 from A20 using the below assign activity.
strIndex= “I”+(Cint(System.Text.RegularExpressions.Regex.Match(strAddress,“\d+”).Value.ToString.Trim)-1).ToString

now in Read range use the below as range

“A12:”+strIndex

This will helps to extract the first datatable

Coming to extraction of second table

To extract the second table, use range as below
strIndex= “A”+(Cint(System.Text.RegularExpressions.Regex.Match(strAddress,“\d+”).Value.ToString.Trim)+2).ToString

Use read range: strIndex

This will read the excel starting from A22 as shown in fig

try this and let me know if it works for u

Thanks & Regards,
Nived N

[Debasis_Nayak]

@NIVED_NAMBIAR

RegularEx

Thank you so much, lookup range works fine.