how to order a big csv file with php?

Question

i'm looking for an algorithm strategy. I have a csv file with 162 columns and 55000 lines. I want to order the datas with one date (which is on column 3).

first i tried directly to put everything in an array, but memory explodes.

So i decided to : 1/ Put in an array the 3 first columns. 2/ Order this array with usort 3/ read the csv file to recover the other columns 4/ Add in a new csv file the complete line 5/ replace the line by an empty string on the readed csv file

//First read of the file
while(($data = fgetcsv($handle, 0,';')) !== false)
{
    $tabLigne[$columnNames[0]] = $data[0];
    $tabLigne[$columnNames[1]] = $data[1];
    $tabLigne[$columnNames[2]] = $data[2];

    $dateCreation = DateTime::createFromFormat('d/m/Y', $tabLigne['Date de Création']);



    if($dateCreation !== false)
    {
        $tableauDossiers[$row] = $tabLigne;
    }
    $row++; 

    unset($data);
    unset($tabLigne);
}

//Order the array by date

usort(
    $tableauDossiers,
    function($x, $y) {
        $date1 = DateTime::createFromFormat('d/m/Y', $x['Date de Création']);
        $date2 = DateTime::createFromFormat('d/m/Y', $y['Date de Création']);

        return $date1->format('U')> $date2->format('U');

    }
);


fclose($handle);
copy(PATH_CSV.'original_file.csv', PATH_CSV.'copy_of_file.csv');



for ($row = 3; $row <= count($tableauDossiers); $row++)

{
    $handle = fopen(PATH_CSV.'copy_of_file.csv', 'c+');
    $tabHandle = file(PATH_CSV.'copy_of_file.csv');
    fgetcsv($handle);
    fgetcsv($handle);
    $rowHandle = 2;
    while(($data = fgetcsv($handle, 0,';')) !== false)
    {

        if($tableauDossiers[$row]['Caisse Locale Déléguée'] == $data[0]
                && $tableauDossiers[$row]['Date de Création'] == $data[1]
                && $tableauDossiers[$row]['Numéro RCT'] == $data[2])
        {
            fputcsv($fichierSortieDossier, $data,';');
            $tabHandle[$rowHandle]=str_replace("\n",'', $tabHandle[$rowHandle]);
            file_put_contents(PATH_CSV.'copy_of_file.csv', $tabHandle);
            unset($tabHandle);

            break;
        }
        $rowHandle++;
        unset($data);
        unset($tabLigne);
    }

    fclose($handle);
    unset($handle);
}

This algo is really too long to execute, but works

Any idea how to improve it ?

Thanks

Answer 1

Assuming you are limited to using PHP and can not use a database to implement it as suggested in the comments, the next best option is to use an external sorting algorithm.

1. Split the file into small files. The files should be small enough to sort them in memory.
2. Sort all these files individually in memory.
3. Merge the sorted files to one big file by comparing the first lines of each file.

The merging of the sorted files can be done very memory efficient: You only need to have the first line of each file in memory at any given time. The first line with the minimal timestamp should go to the resulting file.

For really big files you can cascade the merging ie: if you have 10,000 files you can merge groups of 100 files first then merge the resulting 100 files.

---

Example

I use a comma to separate values instead of line-breaks for readability.

The unsorted file (imagine it to be be too big to fit into memory):

1, 6, 2, 4, 5, 3

Split the files in parts that are small enough to fit into memory:

1, 6, 2

4, 5, 3

Sort them individually:

1, 2, 6

3, 4, 5

Now merge:

1. Compare 1 & 3 → take 1
2. Compare 2 & 3 → take 2
3. Compare 6 & 3 → take 3
4. Compare 6 & 4 → take 4
5. Compare 6 & 5 → take 5
6. Take 6.

Answer 2

You have a fairly large set of data to process, so you need to do something to optimize it.

You could increase your memory, but that will only postpone the error, when there is a bigger file, it'll crash then (or get waaaayy too slow).

The first option is try to minimize the amount of data. Remove all non-relevant columns from the file. Whichever solution you apply, a smaller dataset is always faster.

I suggest you put it into a database and apply your requirements to it, using that result to create a new file. A database is made to manage large data sets, so it'll take a whole lot less time.

Taking that much data and write that to a file from PHP will still be slow, but could be manageble. Another tactic might be using the commandline, using a .sh file. If you have do basic terminal/ssh skills, you have basic .sh writing capabilities. In that file, you can use mysqldump to export as csv like this. Mysqldump will be significantly faster, but it's a bit trickier to get going when you're used to PHP.

---

To improve your current code:
- The unset at the end of the first will don't do anything userful. They barely store data and get reset anyways when the next itteration of the while starts.
- Instead of DateTime() for everything, which is easier to work with, but slower, use epoch values. I dont know in what format it comes now, but if you use epoch seconds (like the result of time()), you have two numbers. Your usort() will improve drastically, as it no longer has to use the heavy DateTime class, but just a simple number comparison.

---

This all asumes that you need to do it muliple times. If not, just open it in Excel or Numbers, use that sort and save as copy.

Answer 3

I would load the data in a database and let that worry about the underlying algorithm.

If this is a one-time issue, i would suggest not to automate it and use a spreadsheet instead.

Answer 4

I've only tried this on a small file, but the principle is very similar to your idea of reading the file, stores the dates and then sorts it. Then reading the original file and writing out the sorted data.

In this version, the load just reads the dates in and creates an array which holds the date and the position in the file of the start of the line (using ftell() after each read to get the file pointer).

It then sorts this array (as date is first just uses normal sort).

Then it goes through the sorted array and for each entry it uses fseek() to locate the record in the file and reads the line (using fgets()) and writes this line to the output file...

$file = "a.csv";
$out = "sorted.csv";

$handle = fopen($file, "r");
$tabligne = [];
$start = 0;
while ( $data = fgetcsv($handle) )    {
    $tabligne[] = ['date' => DateTime::createFromFormat('d/m/Y', $data[2]),
        'start' => $start ];
    $start = ftell($handle);
}

sort($tabligne);

$outHandle = fopen( $out, "w" );
foreach ( $tabligne as $entry ) {
    fseek($handle, $entry['start']);
    $copy = fgets($handle);
    fwrite($outHandle, $copy);
}

fclose($outHandle);
fclose($handle);