Laravel-excel: [QUESTION] Best way to handle large csv files?

[angelpy]

I'm trying to work with a csv file with more than 30,000 records. I read the file path from a form

$path = $request->file('import_file')->getRealPath();

and then use

$data = Excel::load($path,function($reader) {}, 'ISO-8859-1')->get();

To load the excel file. If not empty, the $data variable gets converted to an array, manipulated and, depending on the path, passed to a certain function. That is, I have several functions, one for each table (they require different operations).

The issue is that I can't handle a large file like the one with > 30,000 records.

If I dd($data), sometimes the server is not unable to handle the request.

I've tried using chunk and declaring $data a global variable

$data = array();
Excel::filter('chunk')->load('file.csv')->chunk(250, function($results)
{
      global $data;
      $data[] = $results->toArray();

});

When all the jobs are done, I would expect the $data array to be filled with the >30,000 records, but instead it is either empty or filled with the latest 250 records.

(I know that is more a general php issue rather than a laravel-excel specific one).

Perhaps another option is trying to split the csv file into different csv files, but I wonder if there's a more general solution.

Has anyone encountered this problem?

[ghost]

@angelpy It could help you: http://www.thecreativeroad.com/blog/export-large-dataset-using-laravel-excel-using-laravel-db-chunk

[mumin91]

I did not find any solution through the link provided by @jmorocho . The link is dead. The question was about importing data from excel/csv. The link is about exporting data into excel/csv. So, I will be very grateful if someone provides a valid solution. Thanks.

[SeoRoman]

I've found that files of 10,000 more using 3.1 take roughly 3mins, but 100,000 = 30mins, and 400,000 = 120mins... (FAR TOO LONG) ... I have used PHPLeague/CSV and can do the 400,000 file in 20 mins... Hoping this package can optimize and get a little bit closer...

[farhadSagar]

@SeoRoman I am struggling with League/CSV package. I am loading almost 200,000 records using League/CSV and inserting into database. But that processes is taking more then 8hours to complete. This is what I am doing:

$sftp = SSH::into('ssh_option'); // SSH into an sftp server where the csv file is

$csv_contents = $sftp->getString('remote_path_here');
$reader = Reader::createFromString($csv_contents);
$reader->setHeaderOffset(0);
return $reader->getRecords();

And then I am iterating through what I get from the above return and inserting data into the database.

Can you please let me know what can be done to optimize this processes?