Forum Discussion

krkarthik_info's avatar
krkarthik_info
Frequent Contributor
9 years ago

How to compare two excel files with improved mechanism?

Hi,

 

I have two excel files (.xlsx) with same number of columns with large amount of rows data (around 45000 rows in each files). I have the logic as mentioned below which takes more time to compare and produce the result.

 

Existing Logic:

1. Get the columns count in both the files. If the same number of columns are present, then compares the data in each files by column wise

 

Kindly suggest me a good solution to compare two excel files which takes less amount of time.

 

Thanks in advance.

 

Thanks,

Karthik

  • baxatob's avatar
    baxatob
    9 years ago

    Hi Karthik,

     

    I think you have to use another approach for Scenario #2 , because actually these files are different.

     

    E.g. open File 1, get all values under the column "Name" into the array1. Then open File 2, get all values under the column "Name" into the array2. Then compare array1 with array2.

     

    How to work with Excel files using TestComplete methods you can find here. Also it's may be more convenient to use a native libraries for your preferred language to work with Excel directly.

    • krkarthik_info's avatar
      krkarthik_info
      Frequent Contributor
      9 years ago

      Hi baxatob,

       

      Thanks for the suggested solution.

       

      I have tried objectExcel.ExcelCompare method to compare two excel files and observed no mismatches. [In this scenario, both the excel files are having same no of columns and positioned in the same place like given below.]

      Scenario1:  [No Mismatches]

      File1
      NameAge
      ABC21
      XYS22

       

      File2
      NameAge
      ABC21
      XYS22

       

      But I have an another scenario, where columns are not positioned in the same place in both the files as given below. In this case, I got the mismatches on using objectExcel.ExcelCompare method

       

      Scenario 2: [Mismatches]

      File1
      NameAge
      ABC21
      XYS22

       

      File2
      AgeName
      21ABC
      22XYS

       

      Is there any way where we can swap the columns in File2 based on File1.So that we can use ExcelCompare method. Kindly suggest some solutions.

       

      Thanks in advance.

       

      Thanks,

      Karthik

      • baxatob's avatar
        baxatob
        Community Hero
        9 years ago

        Hi Karthik,

         

        I think you have to use another approach for Scenario #2 , because actually these files are different.

         

        E.g. open File 1, get all values under the column "Name" into the array1. Then open File 2, get all values under the column "Name" into the array2. Then compare array1 with array2.

         

        How to work with Excel files using TestComplete methods you can find here. Also it's may be more convenient to use a native libraries for your preferred language to work with Excel directly.