Compare datas in the 2 excel files and arranged it in 1 excel file so that users can view the sets of the 2 different datas easily

Help Studio
, ,
1

i want to ask how to cpmare 2 different sets of data in 2 excel files and make them into 1 file so that people can see and compare the 2 datas more clearly and easier

2

Can you tell little bit in detail what exactly do you want to do ?

3

If your scenario is have two Excel files, each containing dates in a specific column, and you want to compare them to find matches or discrepancies:

Step-by-Step Guide:

  1. Read Data from Excel Files
  • Use Excel Application Scope or Read Range activities to read the two Excel files into DataTables.

plaintext

File1: "Sheet1", Column A (Date column)
File2: "Sheet1", Column A (Date column)
  • Example:
    • DT1 for the first Excel file.
    • DT2 for the second Excel file.
  1. Extract the Date Columns (Optional)

If you only need to compare a specific column (e.g., “Date”), use Filter Data Table or Linq queries to extract just that column.

Example (Using Linq):

vb

ListOfDates1 = DT1.AsEnumerable().Select(Function(row) CDate(row("Date"))).ToList()
ListOfDates2 = DT2.AsEnumerable().Select(Function(row) CDate(row("Date"))).ToList()
  1. Compare Dates

You can use loops or Linq to compare the dates. Here’s an approach using Linq for common or different dates:

  • Find Matching Dates:

vb

MatchingDates = ListOfDates1.Intersect(ListOfDates2).ToList()
  • Find Dates Only in the First File:

vb

OnlyInFile1 = ListOfDates1.Except(ListOfDates2).ToList()
  • Find Dates Only in the Second File:

vb

OnlyInFile2 = ListOfDates2.Except(ListOfDates1).ToList()

4. Write the Results (Optional)

Use Write Range activity to write the comparison results into a new Excel sheet.

Alternative: Using For Each Row Loop

  1. Use two For Each Row activities (nested) to compare each date from one DataTable with the other.

Example:

vb

For Each row1 in DT1.Rows
    For Each row2 in DT2.Rows
        If CDate(row1("Date")).Equals(CDate(row2("Date"))) Then
            ' Perform action if dates match
        Else
            ' Perform action if dates do not match
        End If
    Next
Next

Handling Date Formats

  • Ensure both Excel files store dates in a comparable format. Use CDate or DateTime.ParseExact if necessary.

Example of parsing:

vb

parsedDate = DateTime.ParseExact(row("Date").ToString(), "MM/dd/yyyy", System.Globalization.CultureInfo.InvariantCulture)

Error Handling

  • Add Try-Catch blocks to handle format issues or mismatches.
  • Use Log Message activity for debugging outputs.
4

So example i have 2 excel files like one of them is annual data and another one is quartedly, i want to compare them and collect those datas into a single excel file so that the client is able to read the datas in the excel file and compare them with ease