How can I extract data or extract a table dynamically using UIPath?

[shikumar]

Actually, I wanted to create a workflow where I need to extract tables from an xlsx file and that too dynamically, I mean it should not extract only the specified rows/columns but should extract the table where it end upto x rows.
Also is it possible to identify which table to extract if there are multiple tables in a single xlsx sheet?

[supermanPunch (Arpan)]

@shikumar

I need to extract tables from an xlsx file and that too dynamically

@shikumar

Also is it possible to identify which table to extract if there are multiple tables in a single xlsx sheet?

Hi @shikumar ,

It is possible to data present in a single sheet into multiple tables/Datatables. But we would need to find the pattern that marks the start and end of the tables data.

If you could provide us with a Sample Input data and how you would want the output to be represented, we could work towards providing a suitable solution for your case.

[Anil_G (Anil Gorthi)]

@shikumar

If they are formatted as tables then we can pass the table name d ry extracting…

And if its single sheet then also we can extract the table present on it

But if tables are multiple and multiple sheets are there then yes we have to identify a pattern or so …for the sheets and also the range to be extracted…as specified a sample input and what needa to be extracted will help

Hope this helps

Cheers

[shikumar]

So, below would be the format of the input file that have tables in it.

As, you can see there are two tables in the sheet 1 and just above the table, table name is given(table-1) and same goes for 2nd table.
Now, what I want from the flow is that it should extract only the tables from the sheet including their headers(highlighted in blue) only. First it should extract 1st table and store it in a datatable and same for the second table,coz later I will be storing these two tables in different excel files separately.
Like below is the expected output
For Table 1:

For Table 2:

The main thing here is that there maybe several rows in one table so it should extract every row until table 1 ends and then store it in a datatable and after that it should go to the next table.

Hope this would explain the matter in hand.

Also attaching the sample excel format below.
sample.xlsx (12.2 KB)

[Anil_G (Anil Gorthi)]

@shikumar

You can follow below steps…

1. Use find/replace activity and search for the table name …you would get the cell address
2. Add 1 to the cell address to get the first cell of the table and pass that value in read range activity which would read the table till end System.Text.RegularExpressions.Regex.Match(cellvalue,"[A-Za-z]+").Value + C(int(System.Text.RegularExpressions.Regex.Match(cellvalue,"\d+").Value) +1).ToString this is to add one to cell value returned and use in read ramge activity range field

Repeat same for each table you have

Hope this helps

Cheers

[supermanPunch (Arpan)]

@shikumar

,coz later I will be storing these two tables in different excel files separately.

Hi @shikumar ,

If the Ultimate goal is to get the Different tables data into different excel sheets, then we can use the Keyword of Each Table (TABLE) to identify each tables present and get the indices of the row which matches that word, then we should be able to use Skip and Take methods of the datatable to get that particular table data.

However, at this point, the Datatables will have a different column names but if written to the excel sheet without headers then it should write with the required header names.

Check the workflow below and let us know if this satisfies you requirement. Maybe there are other ways, but let us know what exactly you’re looking for if this doesn’t work for your case :
Excel_ExtractTable_BetweenKeywords.zip (13.1 KB)

[shikumar]

Can we edit this process in a way that it would take two different table name as a keyword. Like if i have a Table names “Table 1” and other one with name “2nd Table”, so is there a way it would work this way?

[supermanPunch (Arpan)]

@shikumar

Like if i have a Table names “Table 1” and other one with name “2nd Table”,

@shikumar ,

Yes. We could make it work, however there would be some changes to be done in the workflow.

But is it not possible to only rely on the TABLE keyword as it is present in both the Table names ? Is it not a Unique name identifier for the tables ?

[shikumar]

No, Actually What is happening that when i am running that flow using my actual input file, the headers of the first table contains the same keyword as the table name so I think that is why i am getting only the data of first table without its headers. But I need those headers in my file as well.
Any solution for that?

[shikumar]

Thanks Anil for the solution.
I would really appreciate If you could prepare a sample process for the same.
Thank you.

[supermanPunch (Arpan)]

@shikumar

the headers of the first table contains the same keyword as the table name so I think that is why i am getting only the data of first table

@shikumar ,

Is it possible to provide what are the actual Table Identifiers (Keywords) and their column names ? Is the full Keyword name also present in the table columns ?

As a slight modification, if the Identifiers are unique and there are no other values present in the same row as that of the identifiers, then we can collect the identifiers beforehand and store it in an Array. Then use it to find the required positions of the tables.

Check the modified workflow below :
Excel_ExtractTable_BetweenKeywords.zip (13.2 KB)

Let us know if you already do know the identifiers of the table beforehand itself. Else we would need to rely on some other keywords (Maybe the Column Names)

[Anil_G (Anil Gorthi)]

@shikumar

Check this.Modified the logical a little

ReadTables.xaml is what you need to open

PepProcess - Copy (10).zip (17.6 KB)

Cheers