Search code examples
phpcsvmatchunique

Compare 2 csv files and highlight row on match

I have following working code to compare 2 csv files - base.csv file to compare to master.csv file using function row_compare. For now I am echoing the master.csv file followed by echoing the match items. I need help to echo only the master.csv file in a table format and highlight the rows that match the base.csv files items.

function row_compare($a, $b)
{
    if ($a === $b) {
        return 0;
    }

    return (implode("",$a) < implode("",$b) ) ? -1 : 1;
}

$file1 = new SplFileObject("master.csv");
$file1->setFlags(SplFileObject::READ_CSV);

$file2 = new SplFileObject("../../base.csv");
$file2->setFlags(SplFileObject::READ_CSV);

foreach ($file1 as $row) {
    $csv_1[] = $row;
}

foreach ($file2 as $row) {
    $csv_2[] = $row;
}

$unique_to_csv1 = array_udiff($csv_1, $csv_2, 'row_compare');
$unique_to_csv2 = array_udiff($csv_2, $csv_1, 'row_compare');

$all_unique_rows = array_merge($unique_to_csv1,$unique_to_csv2);

foreach($all_unique_rows as $unique_row) {
    foreach($unique_row as $element) {
        echo $element . "   ";
    }
    echo '<br />';
}

master.csv

02Jan2020,Marilyn Salary,-31570.29,162252.14,,
02Jan2020,Nancy Skosana,-30000,132252.14,,
02Jan2020,Johan Consulting Fees,-30000,102252.14,,
02Jan2020,Carol Consulting Fees,-30000,72252.14,,
02Jan2020,Juanic Salary,-24239.22,48012.92,,
02Jan2020,Shirley Salary,-14382.67,33630.25,,
06Jan2020,Carol Consulting Fees,-10000,72252.14,,
06Jan2020,Juanic Salary,-4239.22,48012.92,,
06Jan2020,Shirley Salary,-4382.67,33630.25,,

Base.csv

ADE Labeling
Johan Consulting Fees
Best Board
Nancy Skosana
Cabstrut
Juanic Salary
Cybertrek
Drizit
Shirley Salary

Output Format

enter image description here

So all rows containing Johan Consulting Fees, Shirley Salary, Juanic Salary and Nancy Skosan should change to yellow

Solution 
  • <?php
$file1 = new SplFileObject("master.csv");
$file1->setFlags(SplFileObject::READ_CSV);

$file2 = new SplFileObject("base.csv");
$file2->setFlags(SplFileObject::READ_CSV);

foreach ($file1 as $row) {
    $csv_1[] = $row;
}

//If there are just one column, just make a single level array
foreach ($file2 as $row) {
    $csv_2[] = $row[0];
}

//Create html-table with values from master csv file (master csv array $csv_1)
//and if name is the same in other array , make <tr class="yellow"> which
//would highlight the row
$html = '<table>';
foreach($csv_1 as $inner_arr) {
    $name = $inner_arr[1];    
    $arr_in = in_array($name, $csv_2); 

    $html .= '<tr';
    //Name exists in the other array, hightlight this row
    if ($arr_in !== false) {
        $html .= ' class="yellow"';
    }
    $html .= '>';

    foreach($inner_arr as $inner_value) {   
        $html .= '<td>';       
        $html .= $inner_value;    
        $html .= '</td>';
    }
    $html .= '</tr>';

}
$html .= '</table>';
?>
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Table</title>
    <style type="text/css">
    .yellow {background:yellow;}
    </style>
</head>
<body>
  <?php echo $html;?>  
</body>
</html>

    UPDATE (on request from OP): If you have several "base"-files you could do like this: REPLACE first part of code with this:

    <?php
$file1 = new SplFileObject("master.csv");
$file1->setFlags(SplFileObject::READ_CSV);

$basefiles = array('base.csv', 'base2.csv', 'base3.csv');
$file2 = array();
foreach($basefiles as $key=>$bf) {
    $file2[$key] = new SplFileObject( $bf );
    $file2[$key]->setFlags(SplFileObject::READ_CSV);
}

foreach ($file1 as $row) {
    $csv_1[] = $row;
}

$csv_2 = array();
//Go through all fileobjects and create arr ($csv_2) based on those files
foreach ($file2 as $file_object) {        
    //If there are just one column
    foreach($file_object as $row) {
        $csv_2[] = $row[0];
    }
}

//And then continue with creation with html-string as above
//(where line comment starts with:
//Create html-table with values from master csv file)

    UPDATE2: A better approach would be (from @Nigels recommendation) to skip the creation of file objects into a separate array and create the file array in the first loop where the file objects are created for each file.

    <?php
$file1 = new SplFileObject("master.csv");
$file1->setFlags(SplFileObject::READ_CSV);

foreach ($file1 as $row) {
    $csv_1[] = $row;
}

$basefiles = array('base.csv', 'base2.csv', 'base3.csv');
$csv_2 = array();
foreach($basefiles as $key=>$bf) {
    $file2 = new SplFileObject( $bf );
    $file2->setFlags(SplFileObject::READ_CSV);
    foreach($file2 as $row) {
        $csv_2[] = $row[0];
    }
}