RPA Fridays #1 - Basic excel data manipulation (step by step)

You can watch the webinar here. Or you can read about this topic here:

RPA Fridays #1 - Basic Excel data manipulation

Assignment

Employees submit their travel expenses using an Excel file with standardized format.
image
The goal is to create one file from all the input files with this format:
image

How to do it?

image Read Range | Output - from empty output file creates DataTable variable with structure ready to input the output data
image Assign | Files - creates array of file paths - Directory.GetFiles(“files\TELs”,“TEL*.xlsx”)
image For Each :arrow_down: - file in array of files…
image Read Cell - store employee name and employee ID in string variables (empl_name, empl_id), Read Range | expenses - store the last column as Datatable (empl_expenses)
image Assign | summary counter - variable of data type Int32 to store the sum of expenses for each employee/file
image For Each Row In Data Table - loop through data table of employee expenses and add to variable total (count sum of expenses)
image Add Data Row - adds to empty DataTable (from 1st activity) new row with data. Using properties: ArrayRow: {empl_id.ToString,empl_name,total.ToString}
:arrow_up: end of 1st For Each
image Append Range | export output - (can be also Write Range activity) outputs the data to a result table

Do you have some problems replicating this workflow? Let me know!

Download this workflow here:

RPA_Fridays_01.zip (103.7 KB)

Happy automation! :robot: :smile_cat: