PHP: How to optimise looping through a CSV and writing a line to a file dependent on value in column

Question

I have the following code which takes a $filename and loops through it. If the 9th column is in an array of values (not shown here), I ignore it.

Otherwise I write the row to a file which name is based on the 3rd column.

if (($handle = fopen($filename, "r")) !== FALSE) {
    fgetcsv($handle);
    while (($line = fgetcsv($handle, 2000, ";")) !== FALSE) {
        if (!in_array($line[8], $exclude)) {
            $d = str_replace('/','',$line[2]);
            $f = fopen($base.$d.'.csv', "a");
            fputcsv($f, $line);
            fclose($f);
            unset($line);
        }
    }
    fclose($handle);
}

This works fine. However it's very slow. I have a 200Mb CSV it's looping through.

My question is whether it can be optimised and/or whether I am doing anything tragically wrong?

Thanks

You can move the opening and closing of the file where you write data to outside the loop. — ascsoftw, Oct 05 '19 at 13:12
The name of the file depends on stuff within the loop though — pee2pee, Oct 05 '19 at 13:14
I see now. didn't noticed earlier. Lets see if others have any input. — ascsoftw, Oct 05 '19 at 13:15
I saw it in a post on SO to reduce overhead - with or without it, it doesn't make an observable difference — pee2pee, Oct 05 '19 at 13:24
How many files are generated by this (approximately) 10 - 100 - 1000? — Nigel Ren, Oct 05 '19 at 13:42
101 files are generated - there are 15 or so columns and about 1.5m lines — pee2pee, Oct 05 '19 at 14:31

score 1 · Accepted Answer · answered Oct 05 '19 at 14:02

Opening and closing files is always an expensive operation, so reducing this would help as you open and close a file for every row in the input file.

This code keeps an array of the files opened and each time checks if it is already open, if so just use the stored handle, if not open and store the new handle. Then at the end of the code it loops through all of the open files and closes them all...

if (($handle = fopen($filename, "r")) !== FALSE) {
    $outHandles = [];
    fgetcsv($handle);
    while (($line = fgetcsv($handle, 2000, ";")) !== FALSE) {
        if (!in_array($line[8], $exclude)) {
            $d = str_replace('/','',$line[2]);
            if ( isset($outHandles[$d]) )   {
                $f = $outHandles[$d];
            }
            else    {
                $f = fopen($base.$d.'.csv', "a");
                $outHandles[$d] = $f;
            }
            fputcsv($f, $line);
            unset($line);
        }
    }
    fclose($handle);
    foreach ( $outHandles as $file )    {
        fclose($file);
    }
}

I'll try this next - just timing current setup. I had a feeling about the open/close but was thinking to have 100 or so files open wouldn't be the best idea... — pee2pee, Oct 05 '19 at 14:37
So sorry - yes, it was quicker - scared me that all files were 0kb but then I realised they had not been written to yet :-) — pee2pee, Oct 08 '19 at 19:48

PHP: How to optimise looping through a CSV and writing a line to a file dependent on value in column

1 Answers1