Compare 2 datatables with specific columns and write data to new excel

Help Activities
, ,
1

Greetings all i have a situation where i need to compare 2 excel spreadsheets in 2 excel files with particular column names such as account number and case name and need to write down the uncommon data into the new excel sheet it will be great if some one helps out thankyou

2

Hi,

Can you share specific input and expected output?

Regards,

3

Hi @kambhampati_manisridhar

Try this-

  1. Drag and drop the “Excel Application Scope” activity to your workflow and provide the file path of the first Excel file as the input.
  2. Within the Excel Application Scope, use the “Read Range” activity to read the data from the first Excel file and store it in a DataTable variable (let’s say dt1).
  3. Repeat steps 1 and 2 for the second Excel file, storing the data in another DataTable variable (let’s say dt2).
  4. Use the “Filter Data Table” activity to filter dt1 based on the specified column names (e.g., ‘account number’ and ‘case name’) to extract the relevant data.
  5. Similarly, filter dt2 using the same column names.
  6. Use the “Join Data Tables” activity to join dt1 and dt2 based on the specified column names. Select the join type as “Full Outer” to include all rows from both tables.
  7. Add a “Filter Data Table” activity after the “Join Data Tables” activity to filter the joined table. Specify the condition to filter rows where any of the specified column names is empty. This will extract the uncommon rows.
  8. Finally, use the “Write Range” activity to write the uncommon data (output of the “Filter Data Table” activity) to a new Excel file.

Thanks!!

4

Hi @kambhampati_manisridhar

Follow the below steps to achieve the output:

  1. Read Range Workbook activity:
    Input: File path of Excel File 1
    Output: dataTable1
  2. Read Range Workbook activity:
    Input: File path of Excel File 2
    Output: dataTable2
  3. Filter Data Table activity:
    Input: dataTable1
    Output: filteredDataTable1
    Condition: ColumnName1 = “account number” And ColumnName2 = “case name”
  4. Filter Data Table activity:
    Input: dataTable2
    Output: filteredDataTable2
    Condition: ColumnName1 = “account number” And ColumnName2 = “case name”
  5. Merge Data Table activity:
    Input1: filteredDataTable1
    Input2: filteredDataTable2
    Output: uncommonDataTable
  6. Filter Data Table activity:
    Input: uncommonDataTable
    Output: uniqueRowsDataTable
    Remove Duplicate Rows: True
    Columns: ColumnName1 (the column that uniquely identifies each row)
  7. Write Range Workbook activity:
    Input: File path of the new Excel file
    Sheet Name: Specify the sheet name
    DataTable: uniqueRowsDataTable
5

Hi so actually the requirement is that there will be 2 excel files which contains multiple sheets from that i need to select 2 sheets which are ineligible notices and upcoming settlerments those two should be compared with previous months sheet with using column names as account number and case name and then need to filter the uncommon data and write it to new excel sheet for some reasons i can’t share the files now

6

Hi i have tried it but it is not working iam just getting column names as the output

7

Hi thanks for the reply but my requirement is that i need to compare 2 excel sheets from 2 excel files out of those 2 sheets which have multiple columns in them from which i need to compare only case name and account number as column the column position is not fixed and after comparing those 2 columns i need to filter uncommon data and write it to new excel file

8

Hi @kambhampati_manisridhar

When you use Filter Datatable activity irrespective of the number of columns you have in the excel your datatable will get filtered with whatever columns you want. And you need to use Filter Datatable under For each row in datatable activity. Still facing the same issue kindly share the input excel files and the requirment. I will get the solution for same.
Regards,