Import big csv in Laravel

Question

I'm using following package to import large CSV file to MySQL database:

https://github.com/Maatwebsite/Laravel-Excel

Here is my controller code:

Excel::filter('chunk')->load($file)->chunk(250, function($results) use ($count)
    {
        ++$count;

        echo "<br/> Count = " . $count;

        foreach($results as $row)
        {
            // do stuff
        }

Here is line from composer.json

"maatwebsite/excel": "~2.1.0"

Here is my config/app.php file:

'providers' => [
 ....
 ....
 Maatwebsite\Excel\ExcelServiceProvider::class,
 ],

 'aliases' => [
 ....
 ....
 'Excel' => Maatwebsite\Excel\Facades\Excel::class,
 ]

I am getting this error and I cannot find any solution:

InvalidArgumentException in Dispatcher.php line 333:
No handler registered for command [Maatwebsite\Excel\Readers\ChunkedReadJob]

I tried following link for solution but no luck:

https://github.com/Maatwebsite/Laravel-Excel/issues/957

https://github.com/Maatwebsite/Laravel-Excel/issues/952

Answer 1

Are you doing some manipulations for each row of csv before inserting into the database or is it like you need to import the data directly to the database?

Just a quick tip, its your csv is in the sequence how your database table columns are ordered, you can use ubuntu terminal to import larger files :

mysql -uroot -proot --local_infile=1 3parsfdb -e "LOAD DATA LOCAL INFILE '/logfiles/Bat_res.csv' INTO TABLE Bat_res FIELDS TERMINATED BY ','"

If this is something which you want to do programatically or in cron, then you need this package. Can you try clearing laravel cache and also try

composer dump-autoload

One more thing is to ensure there are no special characters in the csv which can not get imported.

Answer 2

Instead of that package, try the LOAD DATA INFILE MySQL procedure, using Laravel's DB object. Here is an example. I used this for importing big sized csv files (300M-400M) into a mysql db and worked for me.

Answer 3

LOAD DATA LOCAL INFILE! I really missed MySQL, I used this to ingest a huge CVS file, about 6gb in our server, this took about 20 mins.

You can use something like this:

private function importData(int $ignoreLines, string $fileName) : void
{
    //$this->setLOG("Importing data, please wait", "i");
    $table = 'npdata_csvfile_temp';

    $importDB  = "LOAD DATA LOCAL INFILE '$fileName' ";
    $importDB .= "INTO TABLE $table ";
    $importDB .= "COLUMNS TERMINATED BY ',' ";
    $importDB .= 'OPTIONALLY ENCLOSED BY "\""';
    $importDB .= "LINES TERMINATED BY '\\n' ";
    $importDB .= "IGNORE $ignoreLines LINES ";

    DB::connection()->getpdo()->exec($importDB);
    //$this->setLOG("Done with importing data", "i");
}