Read & add new row for new employees in the excel sheet

Help StudioX
, , ,
1

Hi, I’m using studioX and I can’t find a way to do this problem.
I want to add the new employee from masterlist.xls to mylist.xls
automation must add new row to my excel sheets (mylist.xls) if the employee is not existing in my sheets.
attach is my sample excel files
masterlist.xlsx (8.6 KB)
mylist.xlsx (11.1 KB)

2

Hey! I’m not very familiar with StudioX but you should be able to do it like this:

  1. Use the “Excel Process Scope” and “Use Excel File” activities from the “Excel” category to open masterlist.xls and mylist.xls.
  2. Use the “Read Range” activity from the “Excel” category to read the data from both worksheets into two separate DataTable variables. Select the appropriate range for each worksheet and specify the variables to store the output.
  3. Use a “For Each Row” activity from the “Data Table” category to loop through each row in the masterlist DataTable. Set the DataTable property to the masterlist DataTable variable.
  4. Use the “Filter Data Table” activity from the “Data Table” category to check if the current row from the masterlist DataTable exists in the mylist DataTable. Set the DataTable property to the mylist DataTable variable. In the properties panel, use the “Add Filter” button to add a condition that matches the values in the columns that uniquely identify an employee.
    5- If the filtered DataTable has no rows, then the employee does not exist in mylist.xls. Use the “Add Data Row” activity from the “Data Table” category to add the employee’s data to the mylist DataTable. Set the DataTable property to the mylist DataTable variable and specify the values for the new row in the ArrayRow property. Be sure to specify the correct column mappings in the properties panel.
  5. Once all rows have been checked, use the “Write DataTable to Excel” activity from the “Excel” category to write the updated mylist DataTable back to mylist.xls. Set the DataTable property to the mylist DataTable variable and specify the range to write to in the Range property.

Let me know if something doesn’t work and I’ll help you out!

3

masterlist
mylist

but the automation must identify which department the employee belongs.
I’ve attach an image above.

Thank you for your response.

4

Hi,

Can you try the following sample?

image
image994×2764 175 KB

NewBlankTask20230409-2.zip (78.2 KB)

Regards,

1 Like
5

This works perfectly!

Thank you again Yoichi-san for your response regarding my problem.
Hurray!

1 Like
6

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.