Extract Data from multiple excel files with multiple sheets into one master sheet

Help Studio
, , , ,
1

Hello,

I have the following ask at hand and would like some help tackling it

Description

  • I have excel files with performance metrics for each employee in a uniform template (the template is not a typical tabular format, it looks something like the attached)
  • Each department employees are stored in a single folder (i.e. Folder Name: Department 1, has say 20 files corresponding to 20 employees)
  • The employee excel files consists of multiple sheets and I need data from all sheets to be extracted.
  • I want to create a workflow that goes through all folders and all files and extracts the required data points about each employee and puts all data in one single master sheet
  • Output template should include all the fields in all the sheets of the excel files

I really appreciate your help

Thank youEmployee sheet.xlsx (13.0 KB)

2

hi
1.open the excel file(for each excel file in the folder)
2. get the count of the sheets in the excel file.
3.loop it sheet by sheet while appending excel data into one master excel file while renaming the sheet
4.then next file do the same
4.

3

@noor.akroush - Please find the starter help here…

image
image811×718 45.6 KB

  1. For Each Folder (Type Argument = String)= Directory.GetDirectories(“YourMasterFolderpath”) =>

  2. For Each file (Type Argument = String)=Directory.GetFiles(EachFolder," *.xls *") -Remove the space before the *

  3. Excel App scope = ![image|274x82, 75%] (upload://cXqLXF6BX0vXLcY4aqHr5OOrCY.png) - Save the output workbook as wb

  4. For Each Sheet (Type Argument = String)= wb.GetSheets

  5. Read Range = EachSheet = Save it to datatable called dt

  6. Check for Master output file exists using if condition…

In the workflow, feels free to add your conditions based on your requirement…

4

Hi Prasath, thank you for your response. This has worked well to combine all the sheets in one sheet. I am a beginner to UI Path, so if you can also help me with how to select only certain cells/ranges from each sheet to be appended that would be great ((skipping empty columns and rows as well as some unneeded parts of the template). Appending all the sheets is taking a very long time

Also, is there a way to transpose some of those fields? As seen in the picture ideally the output should be transposed so that we have two fields “Target” and “Value”
image

The end goal is to have 1 line of data in the output per workbook sheet (so if one excel file has 3 sheets it will have 3 corresponding data rows in the output