Diff CSV files - But ignore certain fields

[tsk1979]

I have a script which runs over a data generated, and then creates a csv file Now I need to diff multiple csv files. But the diff as a twist Lets take a look at my sample csv file

Index1,Index_ID,Final_result,Method1,TimeGiven,TimeTaken,Result,Method
+2,TimeGiven,TimeTaken,Result
set1,541,PASSED,SOLVE12,13020,13481,UNDETERMINISTIC,Solve2,329614,683,
+PASSED           
set1,542,FAIL,SOLVE12,13020,13531,UNDETERMINISTIC,Solve2,315259,84094,
+FAIL           
set1,545,UNDETERMINISTIC,SOLVE12,13638,13403,UNDETERMINISTIC,Solve2,43
+6268,437526,UNDETERMINISTIC    
set1,546,UNDETERMINISTIC,SOLVE12,13638,13420,UNDETERMINISTIC          
+                       
set1,547,UNDETERMINISTIC,SOLVE12,13638,13476,UNDETERMINISTIC          
+                       
set1,548,UNDETERMINISTIC,SOLVE12,13638,13476,UNDETERMINISTIC          
+                       
set1,549,UNDETERMINISTIC,SOLVE12,13638,13486,UNDETERMINISTIC          
+                       
set1,550,UNDETERMINISTIC,SOLVE12,13638,13530,UNDETERMINISTIC          
+                       
set1,551,UNDETERMINISTIC,SOLVE12,13638,13461,UNDETERMINISTIC          
+                       
set1,552,UNDETERMINISTIC,SOLVE12,13638,13449,UNDETERMINISTIC

The first two fields constitude the index. So if I am comparing 2 csv files, I will compare based on this index. eg set1.551
To my script I will give more than 1 generated csv files
I want to print all those lines in a new CSV where
1. Index is not present in all the csv files
2. Either Field 3, field 7, and field 12(corresponding to columns) is different
Any fast and easy way to do this.

I have tried some hash based methods, but once I go to more than 2 files, things get complicated.
Here is what I am kind of trying
1. Read all csv files, create a hash of index<nr> 2. Store all indexes in an array which are not present in all the csv files
3. Store all indexes in an array where the number of columns for that particular row are different
3. For each index, extract the required fields, and then see if they are same in all the csv files
As you can see, its getting fairly complicated and I am getting confused, any help on how to proceed.

[McA]

Hi

I give that as a starting point. Nr. 1 should be solved. the rest is an execise for tsk1979.

#!/usr/bin/perl
use warnings;
use strict;

use Text::CSV;
use Data::Dumper;

die "ERROR: You have to provide at least one csv file."
    unless(@ARGV);

my %RESULT;

foreach my $filename (@ARGV) {
    if(grep { $_ eq $filename } keys %RESULT) {
        warn "WARN: Why dou you want to compare a file '$filename' whi
+ch you have read already. Skipped.";
        next;
    }
    my $csv = Text::CSV->new(
         { binary => 1 }
    ) or die "Cannot use CSV: " . Text::CSV->error_diag ();

    open my $fh, "<", $filename or die "ERROR: Can't open '$filename':
+ $!";
    my $counter = 0;
    while (my $row = $csv->getline($fh)) {
        $counter++;
        next if($counter < 2);
        unless(@$row <= 11) {
            die "ERROR: Line '$counter' of file '$filename' has wrong 
+format. Check your data crap.";
        }
        my $index = $row->[0];
        my $index_id = $row->[1];
        $RESULT{$filename}->{$index}->{$index_id} = { 'record' => $row
+ };
    }
    $csv->eof or die "ERROR: Couldn't read csv file '$filename': " . $
+csv->error_diag();
    close $fh;
}

# Missing keys
my %FOUND_KEYS;
my $files_loaded = scalar keys %RESULT;

foreach my $filename (keys %RESULT) {
    foreach my $index (keys %{$RESULT{$filename}}) {
        foreach my $index_id (keys %{$RESULT{$filename}->{$index}}) {
            my $key = $index . '@' . $index_id;
            my $hash_ref = $FOUND_KEYS{$key} || {};
            $hash_ref->{$filename} = 1;
            $FOUND_KEYS{$key} = $hash_ref;
        }
    }
}
foreach my $key (keys %FOUND_KEYS) {
    if(scalar keys %{$FOUND_KEYS{$key}} != $files_loaded) {
        print "INFO: Key combination '$key' only found in the followin
+g files: " . join(', ', sort keys %{$FOUND_KEYS{$key}}) . "\n";
    }
}

Best regards
McA

[tmharish]

I have a script ...

I cant help with a script I cant see - please update with script?

[McA]

Hi,

IMHO the csv file format is not correct. The first line of the file lets the user assume you allways get 11 fields. Afterwards there are not enough commas to define 11 fields even if they are empty.

Best regards
McA

[stonecolddevin]

1. Use Text::CSV_XS to parse your CSV
2. Read your files into two separate arrays of arrays, populating the arrays with only the fields you need
3. Use Set::Scalar to do a difference operation to get your diff

[tsk1979]

I finally came up with a solution. you can choose which field to diff, and then print a diff report based on that field

use strict;
use warnings;
if ($#ARGV < 1) { die "usage csvout_compare.pl <csvoutout1> <csvout2> 
+<csvoutout3>.....\n"};
my $f=0;

my %allhash;
my @indexes_to_print;
my %data;
my @line;
my $index;
foreach my $files (@ARGV) {
    $f++;
    open FILE,$files or die "Cannot open $files\n";
    print "Opened $files \n";
    while (<FILE>) {
        next if (/csvout_ID/);
        chomp;
        @line = split(/,/,$_);
        $index = $line[0].".".$line[1];
        $allhash{$index} = 1;

        $data{$f}{$index} = "$_";
            }
    close FILE;
}

foreach $index (keys %allhash) {
    my @temparray;
    my $flag = 0;
    #First lets take all those indexes which are not in all files
    foreach my $dataset (keys %data) {
        if (!(exists $data{$dataset}{$index})) {
            push (@indexes_to_print,$index);
            #delete $allhash{$index};
            $flag = 1;
        }
    }
    next if ($flag eq 1);

    #Now we take those indexes where the Final result is different
    for (my $i=1;$i<=$f;$i++) {
        my @temp = split(/,/,$data{$i}{$index});
        $temparray[$i] = $temp[2];
        if ($i>1) {
            if ($temparray[$i] !~ $temparray[$i-1]) {
                push (@indexes_to_print,$index);
            }
        }
    }
}
#uniquify @indexes_to_print
#
 my %seen   = map { $_, 1 } @indexes_to_print;
 my @unique_indexes = keys %seen;

 foreach my $index (@unique_indexes) {
     print "$index";
    for (my $i=1;$i<=$f;$i++) {
        if (exists $data{$i}{$index}) {
            my @temp = split(/,/,$data{$i}{$index});
            #print ",$i,$temp[2]";
            print ",$i";
            foreach (my $j=2;$j<=10;$j++) {
                if (exists ($temp[$j])) {
                    print ",$temp[$j]";
                } else {
                    print ",";
                }
            }
        } else {
            print ",$i,NA,,,,,,,,";
        }
    }
    print "\n";
}