Excel Workbook & Zipped Files

Help Studio
1

I’M Working on a project that has several steps, below are the steps I’m having trouble completing.

  1. I have downloaded several excel files that i need to extract the data from and combine them into one report.

Problem: The files that i download are zipped folders with one excel file inside the file, I need to get each excel file from each individual folder and add them to one excel sheet. I have been using the right click activity to unzip files and then a Excel application scope to work with the files.

Question: Is there a easier way of unzipping multiple files in Uipath? Do i have to unzip each files one by one with the right click extract using the click activitites?

  1. The excel files all have different names but each excel file only has one sheet, I need to extract all the information from that file without having to insert a sheet name.

Problem: I don’t know how to copy the information from the workbook into another excel workbook. I tried the read range activity but it wants me to add the sheet name,i just want to copy the information from each file and combine it into one file without having to tell the robot what sheets, because there is only one sheet in each file so i just need to copy the workbook. I need to combine 27 excel files all into one file without having to add the sheet name. I need all the information from each file. Each sheet name is different for each file. Trying to just extract all the information from the file and write it to another file.

Question: Is there a activity that allows me to just copy the information from the excel file that does not have multiple sheets. Will it allow me to just copy the whole workbook?

2

@NATHAN_MORA

I assume you know how to properly handle the Excel Scope.

.zip files

I found something that might help you (I never tried it). Link below

Convert your one sheet Excel to DataTable

  • Use GetWorkbookSheets (From Excel activities) then take the first element of the resulting array as the SheetName.

  • Now, with obtained SheetName, use Read Range activity with an empty string (“”) as cell range

Merging DataTables

Use Merge DataTable activity. You can chain it: initialise your datatable then loop over Excel files and merge.

Filter DataTable

Use Merge DataTable activity to set only expected Columns in the Output Tab

Dump the DataTable to Excel

Use Write Range activity. Done.

3

I’m still having some trouble with the excel workbooks.

  1. I have three excel sheets i need to consolidate. The three files have all the same attributes they just need to be one file instead of three files.

  2. I only need the information from columns A-I and L-M leaving out J-K

3.The excel files only contain 1 sheet in each file and that sheet name will always be something different then sheet one or sheet two. Most times I wont know what that sheet name is but there is only one sheet in the excel file(one workbook).

Solutions:
For each excel file I followed the following steps

  1. Excel Application Scope
  2. Get workbook Sheet Giving the index a value of zero this way its just takes the data from the sheet without having the sheet name.
    3.Read range A-I and L-M(leaving out J-K don’t need this information)
    4.Excel Application Scope with 2 write range activities for A-1 and L-M, This makes a excel file with only the information i need disregarding columns J-K

I did the above solution’s for all three excel files(which i think is overkill) now that i have three files with the information i need and the information i don’t i need, I now need to consolidate these three files into one file.

If Somebody could write Uipath Example that would be great. I have spent hours on this and I know I’m close I just need to consolidate three excel files that all have the same headers and columns but have different Workbook names. Thanks for the help

5

4

I’ve added to my answer a filter activity to retains only some columns (I didn’t catch that requirement from your original post).

5

I don’t have any issues retaining only the information i want. I just cant figure out how to consolidate the three excel files to one file.