Comparing two Excels from multiple sheet

Help Activities
,
1

Hello, I’m quite new with UiPath. I’d need to do a comparison of two separate Excel files that has multiple sheets which names may differ from time to time. I know how to compare two different Excel files, but the issue is with the sheets. I tried to use the For each Excel sheet activity to get the sheet name into variable, which it would then use for reading the sheet content into database. That works fine with the first Excel, but then reading the second and having the data for processing is the issue. How is the correct way to set this up?

2

Hi @heidi.michelsson1

Welcome to community. İ am trying to describe correct way to do this below.

  1. Use the For Each Excel Sheet activity for the first Excel file to extract the sheet names into a list (SheetsList1).
  2. Repeat the same for the second Excel file to extract the sheet names into another list (SheetsList2).
  3. Use a For Each activity to iterate through the SheetsList1. Inside the loop, check if the current sheet exists in SheetsList2 using an If condition:
    SheetsList2.Contains(currentSheetName)
  4. If matches; Use Read Range activity to read data from the corresponding sheets in both Excel files into DataTables (DT1 for File 1 and DT2 for File 2). And then you can compare two datatable.

Hope this helps :slight_smile:
Regards.

3

Thanks for helping me out!! I haven’t gotten very far yet, because my automation gets stuck in ‘Using Excel file’ part :frowning: I wonder if that’s because the Excel is quite large and has multiple sheets in it? I saw some discussion on the forum on that topic, where someone had done some VB coding to resolve that. I’m not familiar with VB, so I’d like to stuck with easier solutions…

4

Never mind about that Excel getting stuck issue; I managed to resolve that by killing Excel process before starting the wf :slight_smile: Now back to trying some more.

5

@tolga.kamci Thanks a lot for your help. I’ve progressed, but I’d need help with the output Excel. For some reason I can’t get the values there correctly. It should save the name of the sheet and the line from the sheet that differs from the other Excel sheet. I’ve read the the contents into datatables and tried to compare them. The process stops with an empty output Excel sheet. Here’s what I have. What is wrong? →

The image shows a nested "For Each Row" workflow in a data table automation, where rows from two data tables are compared, and matching results are written to an Excel file if a condition is met. (Captioned by AI)
The image shows a nested "For Each Row" workflow in a data table automation, where rows from two data tables are compared, and matching results are written to an Excel file if a condition is met. (Captioned by AI)700×962 54.6 KB

6

Hi @heidi.michelsson1 , what’s the yardstick for DT comparison?

7

@sudster It’s a monthly report of certain values which need to be compared to similar report of previous month and then create a new report of changed values containing the sheet name and row where the different values exist.

8

@heidi.michelsson1 , since the data is from 2 different months, what would be a common field?

9

Hi @heidi.michelsson1

You shoud use this syntax below with Assign activty:

dtInput.AsEnumerable.Except(dtInput2.AsEnumerable,Datarowcomparer.Default).CopyToDataTable

Here is an example for you:

image
image986×682 31.4 KB

This syntax finds the difference for rows that exist in first datatable(dtInput) and not exist in second datatable (dtInput2). Then you can apply the same logic vice versa.

Hope this helps.

Regards.

10

@sudster The report has multiple sheets that has different data. The idea was to first collect the sheet names and then compare the sheet content based on sheet name. The rows that differ should be reported.

11

@tolga.kamci Thanks! I’d need to find values that are different though; they’re not missing from the datatables.

12

@tolga.kamci Sorry to bother you once more, but I get null value to my datatable in the assign activity with this solution after all - even though I did it like this :frowning: What could be the reason for this?

13

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