Split large Excel/Csv file to multiple files on PHP or Javascript

Question

I have excel(file.xls)/csv(file.csv) file that contains/will contain hundreds of thousands of entry, even millions I guess. Is it possible to split this one to multiple file? Like file.xls to file1.xls, file2.xls, file3.xls and so on.

Are there any libraries to use? Is this possible on PHP? or how about javascript? On where I can specify how many rows to be included on each file?

Thanks

Answer 1

Quick and dirty way of splitting a CSV file into several CSV files

$inputFile = 'input.csv';
$outputFile = 'output';

$splitSize = 10000;

$in = fopen($inputFile, 'r');

$rowCount = 0;
$fileCount = 1;
while (!feof($in)) {
    if (($rowCount % $splitSize) == 0) {
        if ($rowCount > 0) {
            fclose($out);
        }
        $out = fopen($outputFile . $fileCount++ . '.csv', 'w');
    }
    $data = fgetcsv($in);
    if ($data)
        fputcsv($out, $data);
    $rowCount++;
}

fclose($out);

Answer 2

Yes it is possible to do that in PHP and with CSV files. You basically iterate over the large file and chunk each X rows, forwarding those rows to another file.

You find the information how to open the large CSV file as an iterator in this answer here:

• Answer to "how to extract data from csv file in php"

Then you need to chunk the iterator each X rows parts. That can be done as outline here:

• Answer to "Need some advice with PHP loop"

Just instead of outputting into multiple <ul>...</ul> HTML lists, you copy over into a new files. That basically works like outlined in:

• Answer to "How can I split a CSV file in PHP?"

However this time you want to use the SplFileObject::fputcsv method. Take care you use the latest stable PHP for this, otherwise you need do different, see fputcsv().

If the first line of the original file contains column-headers, you might be as well interested in the following:

• Answer to "Process CSV Into Array With Column Headings For Key"

It just shows some ways to extend / process the incomming file. You might not need the full abstraction done there, just keeping the first line around might do it already.