Convert bulk records of excel to csv using PHPExcel

Question

I am working on conversion of excel to csv. And i have currently facing one issue that conversion of excel file with 15k records and it is taking 45 to 50 seconds on average to count the rows and columns of the file.

And if i working with 50k records then system crashes. Here i am not doing any csv conversion work and yes here I am doing only rows and columns count. Is there any way out or any alternative that will solve the issue? Your suggestions are highly appreciated.

require_once('../PHPExcel/Classes/PHPExcel.php');

$start = time();

$infile = 'IMPORT_DATA_15000.xlsx';
$outfile = 'csv/15000.csv';

ini_set('memory_limit', -1);

//Usage:
convertExcel2CSV($infile);

echo 'Time: ' . (time() - $start);
die;

function convertExcel2CSV($infile)
{
    //  Read your Excel workbook
    try
    {
        $inputFileType = PHPExcel_IOFactory::identify($infile);
        $objReader = PHPExcel_IOFactory::createReader($inputFileType);
        $objPHPExcel = $objReader->load($infile);
    } catch (Exception $e)
    {
        die('Error loading file "' . pathinfo($infile, PATHINFO_BASENAME) . '": ' . $e->getMessage());
    }

    //  Get worksheet dimensions
    $sheet = $objPHPExcel->getSheet(0);
    $highestRow = $sheet->getHighestRow();
    $highestColumn = $sheet->getHighestColumn();

    echo $highestColumn;
    echo "====";
    echo $highestRow;
}

OUTPUT:

AMK
====
15000

What do you mean with system crashes? Is there any specific error messages that you get? — codedge, May 05 '16 at 08:15
No i can't get any message as file has lots of records so system goes down and do not complete my process. — Khushang Bhavnagarwala., May 05 '16 at 08:16
Like codedge said, what do you mean by the 'system goes down'? Does it simply stop before producing output? Did you check your logs? Could it be running out of memory? — Julie Pelletier, May 05 '16 at 08:18
System goes down means when i start convert excel file to csv then it will go out of memory for 15k records. What happened if i convert 100k records.? — Khushang Bhavnagarwala., May 05 '16 at 08:23
http://stackoverflow.com/questions/25056030/phpexcel-how-can-i-read-the-excel-sheet-row-by-row — Ricardo Seromenho, May 05 '16 at 08:23
Loading the entire file into memory with (believe it or not) use some memory, and files with larger volumes of data will typically require more memory. There are many documented techniques to reduce the memory usage of PHPExcel, including loading only the data and not the styles, loading parts of the file rather than the entire file, reducing the memory footprint of cell data by caching that data in compacted form, or in a database or redis or similar — Mark Baker, May 05 '16 at 08:26

Mark Baker · Answer 1 · 2016-05-05T08:27:01.543

0

If all you need is to know how many rows and columns there are in each worksheet, then why don't you simply use

$inputFileType = PHPExcel_IOFactory::identify($infile);
$objReader = PHPExcel_IOFactory::createReader($inputFileType);
$worksheetData = $objReader->listWorksheetInfo($inputFileName);

var_dump($worksheetData);

It's amazing how much you can learn simpy by reading the documentation

There are also methods that allow you to load the file a worksheet, or a rage of cells at a time, and other wonderful techniques described therein.... and knowing those worksheet details that listWorksheetInfo() provides detail that will allow you to work with the files in smaller chunks, or to reduce memory usage through cell caching, or read only the data rather than all the cell styles as well

edited May 05 '16 at 08:27

answered May 05 '16 at 08:21

Mark Baker

209,507
32
346
385

This solution solves my counting problem but how to convert the 15k data into 1k chunk? I have follows this link. http://stackoverflow.com/questions/25056030/phpexcel-how-can-i-read-the-excel-sheet-row-by-row – Khushang Bhavnagarwala. May 05 '16 at 08:46
And what happened when you created a read filter to read the data in chunks? – Mark Baker May 05 '16 at 08:48
Did you try any of the other methods I've mentioned, like setting the reader to load only cell data rather than data and styles, or to use cell caching? – Mark Baker May 05 '16 at 08:49

Convert bulk records of excel to csv using PHPExcel

1 Answers1