Remove duplicates form CSV

[wiggst3r]

Hi

 

I have a CSV file over 500,000 rows.

 

What's the best way to remove any duplicate rows?

 

Thanks

[gevans]

What ever you do is going to be slow with 500,000+ lines, but if you get the CSV file line by line into an array and use array_unique() it will filter all duplicate rows

[wiggst3r]

Isn't that going to be quite memory hungry?

Putting such a huge amouunt of rows into an array?

[Maq]

Found this litte script online.  You may want to use some kind of recursive method to take chunks of the CSV if it's running slow.

 

  $filename = "file.csv";
$file = fopen($filename, "r");
$read = fread($file, filesize($filename));

$split = array_unique(explode("\n", $read));

fclose($file);

$filename2 = "other.csv";

$file2 = fopen($filename2, "a");

foreach($split as $key=>$value) {
    if($value != "") {
        fwrite($file2, $value . "\n");
    }
}

fclose($file2);

echo "Update done successfully.";
?> 

[gevans]

I was actually curious about this, just been running arrays of up to 200,000 lines locally. It struggles a bit, but in a live environment you should have more memory dedicated to the process.

[wiggst3r]

Well what I'm ideally looking to do is remove all duplicate email addresses and the whole row in which the email address occurs within a CSV with 4 fields. The file is approx 50mb and the CSV has 900,000 rows.

 

[gevans]

And you want to use php to do this?

[wiggst3r]

What else could I use?

I can't open it in openoffice/excel because they are too many rows

[gevans]

This might do the trick for you CLICKY

[wiggst3r]

Thanks.

 

I'll take a look at home as I'm using a Mac at the moment

[Mark Baker]

In a similar situation, I'm inserting the data into a temporary database table with a unique constraint on the e-mail address column.

You could do something similar, and then rewrite database table data back to the CSV.

[printf]

if you use PHP, use a reader / writer with a temp directory to store your file hash references, that way you use very little memory and your compare is based on an IO handle which is cached so the lookup is faster than storing arrays and doing string or array based comparisons in your loop. It will speedup the process and use the least amount of memory! I'll give you example if you want one...

 

 

[gevans]

I don't know if wiggst3r wants an example but I would

[printf]

My point was "using the least amount memory", so using line by line reading and writing will use the least amount of memory. Then if you need to compare one or more "FIELDS" in each row you don't need to store those comparisons in a array because you assign a file name that hash comparison.

 

example... (to make a test CSV file)

 

<?php

$total = 0;

$lines = 750000;

$file = './in.csv';

$array = array ( 7, 9, 0, 6, 4, 44, 77, 22 );

$io = fopen ( $file, 'wb' );

while ( ++$total <= $lines )
{
shuffle ( $array );

fputs ( $io, implode ( "', '", $array ) . "\r\n" );
}

fclose ( $io );

?>

 

Then to eliminate the duplicates that exist in "FIELDS" => 0, 2 & 4...

 

// note, the $temp folder must exist before running the script

 

<?php

$s = microtime ( true );

// file to read

$in = './in.csv';

// file to write

$out = './out.csv';

// temp directory...

$temp = './files';

// csv values split by...

$split = "', '";

// colums to remove duplicates from (if they all match other rows)

$columns = array ( 0, 2, 4 );

$fi = fopen ( $in, 'rb' );

$fo = fopen ( $out, 'wb' );

while ( ! feof ( $fi ) )
{
$l = fgets ( $fi, 4096 );

$a = explode ( $split, $l );

$h = '';

foreach ( $columns AS $e )
{
	$h .= $a[$e];
}

$h = '_' . crc32 ( $h );

if ( ! file_exists ( $temp . '/' . $h ) )
{
	file_put_contents ( $temp . '/' . $h, '' );

	fwrite ( $fo, $l );
}
}

fclose ( $fi );

fclose ( $fo );

if ( $fd = opendir ( $temp ) )
{
while ( false !== ( $file = readdir ( $fd ) ) )
{
	if ( $file != '.' && $file != '..' )
	{
		unlink ( $temp . '/' . $file );
	}
}

closedir ( $fd );
}

echo microtime ( true ) - $s;

?>