Skip to content
Advertisement

How to remove duplicate rows from a CSV file?

Is there a simple way to find and remove duplicate rows from a CSV file?

Sample test.csv file:

row1 test tyy......
row2 tesg ghh
row2 tesg ghh
row2 tesg ghh
....
row3 tesg ghh
row3 tesg ghh
...
row4 tesg ghh

Expected results:

row1 test tyy......
row2 tesg ghh
....
row3 tesg ghh
...
row4 tesg ghh

Where can I start to accomplish this with PHP?

Advertisement

Answer

A straight-to-the point method would be to read the file in line-by-line and keep track of each row you’ve previously seen. If the current row has already been seen, skip it.

Something like the following (untested) code may work:

<?php
// array to hold all "seen" lines
$lines = array();

// open the csv file
if (($handle = fopen("test.csv", "r")) !== false) {
    // read each line into an array
    while (($data = fgetcsv($handle, 8192, ",")) !== false) {
        // build a "line" from the parsed data
        $line = join(",", $data);

        // if the line has been seen, skip it
        if (isset($lines[$line])) continue;

        // save the line
        $lines[$line] = true;
    }
    fclose($handle);
}

// build the new content-data
$contents = '';
foreach ($lines as $line => $bool) $contents .= $line . "rn";

// save it to a new file
file_put_contents("test_unique.csv", $contents);
?>

This code uses fgetcsv() and uses a space comma as your column-delimiter (based on the sample-data in your question comment).

Storing every line that has been seen, as above, will assure to remove all duplicate lines in the file regardless of whether-or-not they’re directly following one another or not. If they’re always going to be back-to-back, a more simple method (and more memory conscious) would be to store only the last-seen line and then compare against the current one.

UPDATE (duplicate lines via the SKU-column, not full-line)
Based on sample data provided in a comment, the “duplicate lines” aren’t actually equal (though they are similar, they differ by a good number of columns). The similarity between them can be linked to a single column, the sku.

The following is an expanded version of the above code. This block will parse the first line (column-list) of the CSV file to determine which column contains the sku code. From there, it will keep a unique list of SKU codes seen and if the current line has a “new” code, it will write that line to the new “unique” file using fputcsv():

<?php
// array to hold all unique lines
$lines = array();

// array to hold all unique SKU codes
$skus = array();

// index of the `sku` column
$skuIndex = -1;

// open the "save-file"
if (($saveHandle = fopen("test_unique.csv", "w")) !== false) {
    // open the csv file
    if (($readHandle = fopen("test.csv", "r")) !== false) {
        // read each line into an array
        while (($data = fgetcsv($readHandle, 8192, ",")) !== false) {
            if ($skuIndex == -1) {
                // we need to determine what column the "sku" is; this will identify
                // the "unique" rows
                foreach ($data as $index => $column) {
                    if ($column == 'sku') {
                        $skuIndex = $index;
                        break;
                    }
                }
                if ($skuIndex == -1) {
                    echo "Couldn't determine the SKU-column.";
                    die();
                }
                // write this line to the file
                fputcsv($saveHandle, $data);
            }

            // if the sku has been seen, skip it
            if (isset($skus[$data[$skuIndex]])) continue;
            $skus[$data[$skuIndex]] = true;

            // write this line to the file
            fputcsv($saveHandle, $data);
        }
        fclose($readHandle);
    }
    fclose($saveHandle);
}
?>

Overall, this method is far-more memory friendly as it doesn’t need to save a copy of every line in memory (only the SKU codes).

User contributions licensed under: CC BY-SA
10 People found this is helpful
Advertisement