Question
Infosys
IN
Last activity: 27 Feb 2024 1:37 EST
How to extract multiple tables from excel sheet using Pega Robotics?
How to extract a Dynamic Multiple tables from excel sheet?
I have excel file having 1 sheet containing multiple tables and size of the tables might change (columns will be constant). I want to read all the tables.
Accepted Solution
Updated: 27 Feb 2024 1:37 EST
Pegasystems Inc.
US
Pegasystems Inc.
US
@ShreeHarshaN16839089 There really isn't much of a trick here. This is likely a programming task using the available methods. The connector has an ExportToTable method which you provide a start and end cell as parameters to export those cells as a DataTable object. You will need to write logic to locate the start and end of each table if there are multiple present in one worksheet. You can use the GetCellValue method to come up with that logic. You'd essentially iterate through each cell until you locate the start and end of each table based on whatever logic is required and then use those values as the parameters for the export. If you can provide an example of the Excel file, I could prepare a demo of extracting a couple of the tables.
Infosys
IN
Hi Thomas,
I thought of the same idea, getting the cell address of each table headers (Assuming Table name won't be changed), So that i can extract the table. This logic will work but in my case there are around 40+ tables in the sheet and some of the tables have more than 400+ columns.
So I was Thinking is there any other to identify and extract the tables.
Accepted Solution
Updated: 27 Feb 2024 1:37 EST
Pegasystems Inc.
US
@ShreeHarshaN16839089There is no specific table object in Excel that I am aware of. The only way you would be able to identify what a table is would be to essentially search for the start and end. Depending on how your worksheet is setup, there are probably more efficient ways to search it for the headers than searching cell by cell. Can you provide an example of what you are looking at (obviously obfuscate any proprietary or non-public data)?
@ShreeHarshaN16839089There is no specific table object in Excel that I am aware of. The only way you would be able to identify what a table is would be to essentially search for the start and end. Depending on how your worksheet is setup, there are probably more efficient ways to search it for the headers than searching cell by cell. Can you provide an example of what you are looking at (obviously obfuscate any proprietary or non-public data)?