How can two CSV files with the same data but different column orders be validated in Java?

Question

I am trying to compare two CSV files that have the same data but columns in different orders. When the column orders match, the following code works: How can I tweak my following code to make it work when column orders don't match between the CSV files?

        Set<String> source = new HashSet<>(org.apache.commons.io.FileUtils.readLines(new File(sourceFile)));
        Set<String> target = new HashSet<>(org.apache.commons.io.FileUtils.readLines(new File(targetFile)));
        return source.containsAll(target) && target.containsAll(source)

For example, the above test pass when the source file and target file are in this way:

source file:

a,b,c
1,2,3
4,5,6

target file:

a,b,c
1,2,3
4,5,6

However, the source file is same, but if the target file is in the following way, it doesn't work.

target file:

a,c,b
1,3,2
4,6,5

Answer 1

A Set relies on properly functioning .equalsmethod for comparison, whether detecting duplicates, or comparing it's elements to those in another Collection. When I saw this question, my first thought was to create a new class for Objects to put into your Set Objects, replacing the String Objects. But, at the time, it was easier and faster to produce the code in my previous answer.

Here is another solution, which is closer to my first thought. To start, I created a Pair class, which overrides .hashCode () and .equals (Object other).

package comparecsv1;

import java.util.Objects;

public class Pair <T, U> {
        
        private final T t;
        private final U u;
    
        Pair (T aT, U aU) {
            this.t = aT;
            this.u = aU;
        }

        @Override
        public int hashCode() {
            int hash = 3;
            hash = 59 * hash + Objects.hashCode(this.t);
            hash = 59 * hash + Objects.hashCode(this.u);
            return hash;
        }

        @Override
        public boolean equals(Object obj) {
            if (this == obj) {  return true;   }
            if (obj == null) {  return false;  }
            if (getClass() != obj.getClass()) {  return false;   }
            final Pair<?, ?> other = (Pair<?, ?>) obj;
            if (!Objects.equals(this.t, other.t)) {
                return false;
            }
            return Objects.equals(this.u, other.u);
        } // end equals
    } // end class pair

The .equals (Object obj) and the .hashCode () methods were auto-generated by the IDE. As you know, .hashCode() should always be overridden when .equals is overridden. Also, some Collection Objects, such as HashMap and HashSet rely on proper .hashCode() methods.

After creating class Pair<T,U>, I created class CompareCSV1. The idea here is to use a Set<Set<Pair<String, String>>> where you have Set<String> in your code.

• A Pair<String, String> pairs a value from a column with the header for the column in which it appears.

• A Set<Pair<String, String>> represents one row.

• A Set<Set<Pair<String, String>>> represents all the rows.

  package comparecsv1;
  
  import java.util.Arrays;
  import java.util.HashSet;
  import java.util.List;
  import java.util.Set;
  
  
  public final class CompareCSV1 {
  
     private final Set<Set<Pair<String, String>>> theSet;
     private final String [] columnHeader;
  
     private CompareCSV1 (String columnHeadings, String headerSplitRegex) {
         columnHeader = columnHeadings.split (headerSplitRegex);
         theSet = new HashSet<> ();
     }
  
     private  Set<Pair<String, String>> createLine 
                  (String columnSource, String columnSplitRegex) {
         String [] column = columnSource.split (columnSplitRegex);
         Set<Pair<String, String>> lineSet = new HashSet<> ();
         int i = 0;     
         for (String columnValue: column) {
            lineSet.add (new Pair (columnValue, columnHeader [i++]));            
         }
         return lineSet;
     }
  
     public Set<Set<Pair<String, String>>> getSet () { return theSet; }
     public String [] getColumnHeaders ()  {
         return Arrays.copyOf (columnHeader, columnHeader.length);
     }
  
     public static CompareCSV1 createFromData (List<String> theData
             , String headerSplitRegex, String columnSplitRegex) {
         CompareCSV1 result = 
            new CompareCSV1 (theData.get(0), headerSplitRegex);
         for (int i = 1; i < theData.size(); ++i) {
             result.theSet.add(result.createLine(theData.get(i), columnSplitRegex));
         }
         return result;
     }
  
  
     public static void main(String[] args) {
         String [] sourceData = {"a,b,c,d,e", "6,7,8,9,10", "1,2,3,4,5"
             ,"11,12,13,14,15", "16,17,18,19,20"};
         String [] targetData = {"c,b,e,d,a", "3,2,5,4,1", "8,7,10,9,6"                
             ,"13,12,15,14,11", "18,17,20,19,16"};    
         List<String> source = Arrays.asList(sourceData);
         List<String> target = Arrays.asList (targetData);
  
         CompareCSV1 sourceCSV = createFromData (source, ",", ",");
         CompareCSV1 targetCSV = createFromData (target, ",", ",");
  
         System.out.println ("Source contains target? "
            + sourceCSV.getSet().containsAll (targetCSV.getSet())
            + ".  Target contains source? "
            + targetCSV.getSet().containsAll (sourceCSV.getSet())
            + ".  Are equal? " + targetCSV.getSet().equals (sourceCSV.getSet()));        
  
     } // end main     
   } //  end class CompareCSV1
  

This code has some things in common with the code in my first answer:

• Except for the column header lines, which must be first in the "source" and "Target" data, matching lines in one file can be in a different order in the other file.
• I used String [] Objects, with calls to Arrays.asList method as substitutes for your data sources.
• It does not contain code to guard against errors, such as lines in the file having different numbers of columns from other lines, or no header line.

I hard coded "," as the String split expression in main. But, the new methods allow the String split expression to be passed. It allows a separate String split expressions for the column header line and the data lines.

Answer 2

Here is some code that could work. It relies on the first line of each file containing column headers.

It's a bit more than a tweak, though. It's an "old dog" approach.

The original code in the question has these lines:

Set<String> source = new HashSet<>(org.apache.commons.io.FileUtils.readLines(new File(sourceFile)));
Set<String> target = new HashSet<>(org.apache.commons.io.FileUtils.readLines(new File(targetFile)));

With this solution, the data coming in needs more processing before it will be ready to be put into a Set. Those two lines get changed as follows:

List<String> source = (org.apache.commons.io.FileUtils.readLines(new File(sourceFile)));
List<String> target = (org.apache.commons.io.FileUtils.readLines(new File(targetFile)));

This approach will compare column headers in the target file and the source file. It will use that to build an int [] that indicates the difference in column order.

After the order difference array is filled, the data in the file will be put into a pair of Set<List<String>>. Each List<String> will represent one line from the source and target data files. Each String in the List will be data from one column.

In the following code, main is the test driver. Only for my testing purposes, the data files have been replaced by a pair of String [] and reading the file with org.apache.commons.io.FileUtils.readLines has been replaced with Arrays.asList.

package comparecsv;

import java.util.Arrays;
import java.util.HashSet;
import java.util.List;
import java.util.Set;

public class CompareCSV {

    private static int [] columnReorder;

    private static void headersOrder
                              (String sourceHeader, String targetHeader) {
            String [] columnHeader = sourceHeader.split (",");
            List<String> sourceColumn = Arrays.asList (columnHeader);
            columnReorder = new int [columnHeader.length];
            String [] targetColumn = targetHeader.split (",");
            for (int i = 0; i < targetColumn.length; ++i) {
                int j = sourceColumn.indexOf(targetColumn[i]);
                columnReorder [i] = j;
            }
    }

    private static Set<List<String>> toSet
                               (List<String> data, boolean reorder) {
        Set<List<String>> dataSet = new HashSet<> ();
        for (String s: data)  {
            String [] byColumn = s.split (",");
            if (reorder) {
                String [] reordered = new String [byColumn.length];
                for (int i = 0; i < byColumn.length; ++i) {
                   reordered[columnReorder[i]] = byColumn [i];
                }
                dataSet.add (Arrays.asList (reordered));
            } else {
                dataSet.add (Arrays.asList(byColumn));
            }
        }
        return dataSet;
    }

    public static void main(String[] args) {
        String [] sourceData = {"a,b,c,d,e", "1,2,3,4,5", "6,7,8,9,10"
            ,"11,12,13,14,15", "16,17,18,19,20"};
        String [] targetData = {"c,b,e,d,a", "3,2,5,4,1", "8,7,10,9,6"
            ,"13,12,15,14,11", "18,17,20,19,16"};
        List<String> source = Arrays.asList(sourceData);
        List<String> target = Arrays.asList (targetData);

        headersOrder (source.get(0), target.get(0));
        Set<List<String>> sourceSet = toSet (source, false);
        Set<List<String>> targetSet = toSet (target, true);
        System.out.println ( sourceSet.containsAll (targetSet)
                + "  " + targetSet.containsAll (sourceSet) + "  " +
                   (    sourceSet.containsAll (targetSet)
                     &&  targetSet.containsAll (sourceSet)));
    }
}

MethodheadersOrder compares the headers, column by column, and populates the columnReorder array. Method toSet creates the Set<List<String>>, either reordering the columns or not, according to the value of the boolean argument.

For the sake of simplification, this assumes lines are easily split using comma. Data such as dog, "Reginald, III", 3 will cause failure.

In testing this, I found lines in the file can be matched with their counterpart in the other file, regardless of order of the lines. Here is an example:

Source:
a,b,c
1,2,3
4,5,6
7,8,9

Target:
a,b,c
4,5,6
7,8,9
1,2,3 

The result would be the contents match.

I believe this would match a result from the O/P question code. However, for this solution to work, the first line in each file must contain column headers.