How do i split a 6 gb CSV file into chunks using php

Question

I'm a beginner level developer learning php.The task that i need to do is upload a 6gb CSV file which contains data, into the data base.I need to access the data i.e reading the file through controller.php file and then splitting that huge CSV file into 10,000 row output CSV files and writing data into those output CSV files. I have been through this task a week already and dint figure it out yet.Would you guys please help me in solving this issue.

<?php

namespace App\Http\Controllers;
use Illuminate\Queue\SerializesModels;

use App\User;
use DateTime;
use Illuminate\Http\Request;
use Storage;
use Validator;
use GuzzleHttp\Client;
use GuzzleHttp\RequestOptions;
use Queue;
use App\model;


class Name extends Controller
{


     public function Post(Request $request)
     {

         if($request->hasfile('upload')){
            ini_set('auto_detect_line_endings', TRUE);
                $main_input = $request->file('upload');
                $main_output = 'output';
                $filesize = 10000;
                $input = fopen($main_input,'r');
                $rowcount = 0;
                $filecount = 1;
                $output = '';

                // echo "here1";
                while(!feof($input)){
                    if(($rowcount % $filesize) == 0){
                        if($rowcount>0) { 
                            fclose($output);
                        }
                    $output = fopen(storage_path(). "/tmp/".$main_output.$filecount++ . '.csv','w');
                    }
                    $data = fgetcsv($input);
                    print_r($data);

                    if($data) {

                        fputcsv($output, $data);
                    }

                    $rowcount++;
                }
                fclose($output);
        }
     }
}  

Answer 1

Maybe it's because you are creating a new $output file handler for each iteration.

I've made some adjustments, so that we only create a file when the rowCount = 0 and close it when the fileSize is reached. Also the rowCount has to be reset to 0 each time we close the file.

public function Post(Request $request)
     {

         if($request->hasfile('upload')){
            ini_set('auto_detect_line_endings', TRUE);
                $main_input = $request->file('upload');
                $main_output = 'output';
                $filesize = 10000;
                $input = fopen($main_input,'r');
                $rowcount = 0;
                $filecount = 1;
                $output = '';

                // echo "here1";
                while(!feof($input)){
                    if ($rowCount == 0) {
                        $output = fopen('php://output', storage_path(). "/tmp/".$main_output.$filecount++ . '.csv','w');
                    }
                    if(($rowcount % $filesize) == 0){
                        if($rowcount>0) { 
                            fclose($output);
                            $rowCount = 0;
                            continue;
                        }

                    }
                    $data = fgetcsv($input);
                    print_r($data);

                    if($data) {

                        fputcsv($output, $data);
                    }

                    $rowcount++;
                }
                fclose($output);
        }
     }

Answer 2

Here is working example of splitting CSV file by the amount of lines (defined by$numberOfLines). Just set your path in $filePath and run the script in shell for example:

php -f convert.php

script code: convert.php

<?php

$filePath = 'data.csv';
$numberOfLines = 10000;

$file = new SplFileObject($filePath);

//get header of the csv
$header = $file->fgets();

$outputBuffer = '';
$outputFileNamePrefix = 'datasplit-';

$readLinesCount = 1;
$readlLinesTotalCount = 1;
$suffix=0;

$outputBuffer .= $header;

while ($currentLine = $file->fgets()) {
    $outputBuffer .= $currentLine;
    $readLinesCount++;
    $readlLinesTotalCount++;

    if ($readLinesCount >= $numberOfLines) {
        $outputFilename = $outputFileNamePrefix . $suffix . '.csv';
        file_put_contents($outputFilename, $outputBuffer);
        echo 'Wrote '  . $readLinesCount . ' lines to: ' . $outputFilename . PHP_EOL;    

        $outputBuffer = $header;
        $readLinesCount = 0;
        $suffix++;
    }
}

//write remainings of output buffer if it is not empty
if ($outputBuffer !== $header) {
    $outputFilename = $outputFileNamePrefix . $suffix . '.csv';
    file_put_contents($outputFilename, $outputBuffer);
    echo 'Wrote (last time)'  . $readLinesCount . ' lines to: ' . $outputFilename . PHP_EOL;

    $outputBuffer = '';
    $readLinesCount = 0;

}

you will not be able to convert such amount of data in one php execution if it is run form web because of the maximum execution time of php scripts that is usually between 30-60sec and there is a reason for that - don't event try to extend it to some huge number. If you want your script to run even for hours you need to call it from command line, but you also can call it similar way from another script (for example the controller you have) You do that this way:

exec('php -f convert.php');

and that's it.

The controller you have will not be able to tell if the whole data was converted because before that happens it will be terminated. What you can do is to write your own code in convert.php that updates some field in database and other controller in your application can read that and print to the user the progress of the runnig convert.php.

The other approach is to crate job/jobs that you can put in the queue and can be run by job manager process with workers that can take care for the conversion but I think that would be an overkill for your need.

Keep in mind that if you split something and on different location join you may have problem of getting something wrong in that process the method that would assure you that you split, transferred, joined your data successfully is to calculate HASH ie SHA-1 of the whole 6GB file before split, send that HASH to destination where all small parts of data needs to be combined, combine them into one 6GB file, calculate HASH of that file and compare with the one that was send. Keep in mind that each of small parts of your data after splitting has their own header to be CSV file easy to interpret (import), where in the original file you have only one header row.