I am unable to read the excel in the requirement format for example attached the sheet.
In the sheet there are two table’s i was able to read the first once as its “static” shown below how ever recognising Column as a header is also been worked upon.
However i am unable to read a dynamic table highlighted below in the same sheet as there is possibility of having more row which cant be predicted.
There is an Extended Excel Activities package that you can download that will allow you to work with tables of varying size. Try following these steps:
Click the download link under ‘Package History’ for version 1.0.3
Locate the downloaded .nupkg file and move it to C:\Program Files (x86)\UiPath\Studio\Packages
Within UiPath Studio, go to the Package Explorer (see screenshot below)
Install the Excel.Extended.Activities package
Restart Studio
At this point, the package should be included in your Activities library. Try using the Get Last Row activity and store the result in a variable. Then you can use this variable to select your range.
I had a similar process that used a single excel sheet and needed to create 2 tables of varying length. I posted a question about it here: Extract beginning portion of data table only
I went about it by doing the following:
Read range on excel file and get the entire sheet into a single datatable variable.
Find the first blank row after the 1st excel table & store as an integer variable “N” - this helped me know where to separate the tables.
Clone the original table from step 1 into table1 and table2 datatable variables using an assign activity
Use the datatable method .take and .skip to separate into 2 tables. To do this, assign RowsToAdd = datatable.AsEnumerable().take(N-1) or .skip(N+1). This should be of variable type IEnumerable Then use a for each to iterate over RowsToAdd and use the invoke method activity on the table1 and table2 variables using the ImportRow method
However, your case might be a bit easier. It looks like the first table you’d like is always going to be in cells A3:B8, correct? If so, then you should do the following.
Read range on excel specifying range A3:B8 and that the data does not have headers
Read range on excel specifying range A10:F10000 (or however many will definitely be more than the most rows you’ll ever have). Make sure to check the box that data contains headers
Use a .Select statement on table from step 2 to get Blank rows
Use a for each datarow on the datarow collection from step 3 and delete them
See the attached sequence describing my 4 steps. I left it within your overall “for each file in directory” structure, but I would recommend separating that out. You should use directory.getfiles and save as a list of string, then make each file it’s own transaction either through orchestrator or through other means. This will make it much easier to follow your workflow as it gets larger instead of containing everything within a single for each loop.
A couple changes I’d point out that I made as well:
You don’t need to use a Word Application Scope when changing objects to string.
You don’t need to use an assign activity to change the directory.getfiles items to strings. I just changed the For Each TypeArgument to string, as that is what is natively returned from directory.getfiles anyways.
I used a Read Range activity instead of containing it within the Excel application scope. This way the computer that runs the robot doesn’t need to have Excel installed. Go ahead and keep it within the application scope if you want to export the Excel variable, or need to use activities that require it to be in the Excel application scope such as execute macro.
No need to use the “output data table” activity. That is already done as part of the Read range activity
ashokkarale
MD_Farhan1
Ajay_Mishra
postwick
Dheerendra_vishwakarma
Anil_G
chandreshsinh.jadeja
Gautham_Pattabiraman
vrdabberu
aravindbalineni123