Upload large csv file to mysql with Laravel

Upload the file and use fgetcsv to parse it and fill your table. The PHP League has a nice package to make parsing csv a little easier: https://csv.thephpleague.com/9.0/.

fgetcsv will not give performance boost .i have already use it in a CORE PHP project ,which was too slow . LOAD DATA LOCAL INFILE is a better option on uploading data

Another option you can look into is this package: https://github.com/Maatwebsite/Laravel-Excel

You could do something like:

Excel::load('file.csv')->chunk(250, function ($data) {

    $inserts = [];

    foreach ($data as $row) {

        $record = new \App\MyModel;

        $record->fill($data->toArray());

        $inserts[] = $record;

    }

    // insert into db

});

This way, you can harness the power of Laravel's Model class and set all your mutators and whatever else. This package will also automatically use Laravel's queue system for all the chunked data.

I don't know about this being a "better" solution when it comes to performance, but just throwing this out there as another option for flexibility.

Spout (https://github.com/box/spout) is the best library to read and write spreadsheet files (CSV, XLSX and ODS), in a fast and scalable way. Its streaming mechanism makes it incredibly fast with memory fingerprint of around 3MB, irrespective of file size. It is capable of processing filesizes in GBs.

Install using composer require box/spout and then simply use

use Box\Spout\Reader\ReaderFactory;
use Box\Spout\Common\Type;

$reader = ReaderFactory::create(Type::XLSX); // for XLSX files
//$reader = ReaderFactory::create(Type::CSV); // for CSV files
//$reader = ReaderFactory::create(Type::ODS); // for ODS files

$reader->open($filePath);

foreach ($reader->getSheetIterator() as $sheet) {
    foreach ($sheet->getRowIterator() as $row) {
        // do stuff with the row
    }
}

$reader->close();

Thanks for replying mates @Talinon and @ehsanquddusi I will test both of libraries with my app and will post about performance. Very thanxx

OK, after examining every single method out there including the ones posted here. I have come with the fastest way to upload huge CSV files without having to install any additional composer items, like the EXCEL stuff. I honestly thought this through and I am able to insert 40K+ records in under 5 seconds.

Laravel 5 and up, comes with a PDO class. So using this was the fastest way due to executing more directly with MySql than using Eloquent.

Here is a code example, dress it up as your own.

        // Created a faster loading method for largte CSV files using PDO method
        // Copy the uploaded CSV file with a new name
        $csvfile = storage_path() . '/YourDirectory/NewFileName.csv';
        copy($_FILES['file']['tmp_name'], $csvfile);
        // Make sure before you start you have a temporary table with the exact field structure of your CSV file
        // Truncate the temp table
        DB::table('tmpTableStructure')->truncate();

        // This will process the PDO connection and input of the records from the CSV file
        try {
            // This holds your temp table name
            $databasetable = 'tmpTableStructure';
            // This gets the environment variable of your host connection
            $hostconnection = trim(config('app.connect_info.host')," ");
            // This creates the connection string to your database
            $databasehost = "mysql:host=".$hostconnection.":3306;dbname=YourDatabaseName";
            // This has the user name saved in your environment
            $databaseusername = config('app.connect_info.username');
            // This has the passwofd savedd in your environment
            $databasepassword = config('app.connect_info.password');
            // This is the PDO way to connect to your database
            $pdo = new PDO($databasehost,$databaseusername,$databasepassword,[PDO::MYSQL_ATTR_LOCAL_INFILE => true]);

            // This catches any connection errors
        } catch (PDOException $e) {
            error_log('database connection failed!');
            die("database connection failed: " . $e->getMessage());
        }

        // These are the variables to store strings
        // used to execute the MySql load command
        $fieldSeparator = ",";
        $fieldEscapedBy = "\";
        $fieldEnclosedBy = '"';
        $lineSeparator = "\r\n";

        // This is how you execute the load command of your CSV file
        $affectedRows = $pdo->exec(
            "LOAD DATA LOCAL INFILE "
            . $pdo->quote($csvfile)
            . " INTO TABLE `$databasetable` FIELDS TERMINATED BY "
            . $pdo->quote($fieldSeparator)
            . " ESCAPED BY "
            . $pdo->quote($fieldEscapedBy)
            . " ENCLOSED BY "
            . $pdo->quote($fieldEnclosedBy)
            . " LINES TERMINATED BY "
            . $pdo->quote($lineSeparator)
            . " IGNORE 1 LINES "
        );

Trust me there is no faster way than to use MySql Engine itself for MySql processes.