Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.

Maison012's avatar

Uploading 1 million csv data extends the runtime and max upload

Hello , i am working on a project where i need to upload massive data and i mean relly big file. I Have builded a function where i can upload file to excel with field mapping, but unntil now i can upload only small excel file , i have tryed 1,000 rows and it works fine, but i need to upload million rows on that system so when i try to upload 1000,000 rows the system show me error of extending max size of uploading also runtime , i have tryed to extend php.ini max file upload to 1048M and runtime to 480 but still show same error. Does anyone have an idea how can i upload this amount of data without breaking the system?

My import function is builded in 2steps, I upload the excel file and save it on a db.table couse i need to match fields cvs header -> db.table header

public function parseImport(CsvImportRequest $request)
    {
        if ($request->has('header')) {
            $headings = (new HeadingRowImport)->toArray($request->file('csv_file'));
            $data = Excel::toArray(new ContactsImport, $request->file('csv_file'))[0];
        } else {
            $data = array_map('str_getcsv', file($request->file('csv_file')->getRealPath()));
            // $data = array_map('fgetcsv', file($request->file('csv_file')->getRealPath()));
        }

        if (count($data) > 0) {
            $csv_data = array_slice($data, 0, 2);

            $csv_data_file = CsvData::create([
                'csv_filename' => $request->file('csv_file')->getClientOriginalName(),
                'csv_header' => $request->has('header'),
                'csv_data' => json_encode($data)
            ]);
        } else {
            return redirect()->back();
        }

        $contact = new Contact;
        $table = $contact->getTable();
        $db_field = \Schema::getColumnListing($table);
        // dd($db_field);

        return view('import_fields', [
            'headings' => $headings ?? null,
            'csv_data' => $csv_data,
            'csv_data_file' => $csv_data_file,
            'db_field' => $db_field
        ]);
    }

this is my first step, Here my function breaks. I mean i cant pass first step to when i upload big data. And after this step i send data to blade file to match like this

@foreach ($csv_data[0] as $key => $value)
     <td class="px-6 py-4 whitespace-no-wrap text-sm leading-5 text-gray-900">
         <label for="csv_field_{{ $key }}">{{ $value }}</label>
          <select id="csv_field_{{ $key }}" name="fields[{{ $key }}]">
          <option value="">-- Select --</option>
                @foreach ($db_field as $field)
                        <option value="{{ $field }}"
                                 @if (old("fields.$key") == $field) selected @endif>{{ $field }}</option>
                 @endforeach
             </select>
          </td>
@endforeach

After mapping field i send the request to this function to make upload on mysql table

  public function processImport(Request $request)
    {
        // dd($request->fields);
        $data = CsvData::find($request->csv_data_file_id);
        $csv_data = json_decode($data->csv_data, true);

        foreach ($csv_data as $row) {
            $contact = new Contact();
            foreach ($request->fields as $csv_index => $db_field) {
                $csv_field = $row[$csv_index];
                $contact->$db_field = $csv_field;
            }
            $contact->save();
        }

        return redirect()->route('contacts.index')->with('success', 'Import finished.');
    }
0 likes
86 replies
psrz's avatar

If I understand correctly, the parseImport() method receives a file but you don't actually save it as such. Instead you convert that into a huge array/object, which then save as json in one CsvData record, in a longtext field, right ?

If that's the case, I don't see how it can work with a million record csv. Nevermind the overehead converting the csv file into some array/object, then converting that into json. That massive record has to be saved on the database, just later on be retrieved.

Personally, I would save the file as csv in the filesystem, with some random name to avoid be overwritten or something like that. The CsvData model will store the path to such file, instead of the actual data.

When it's time to process the file, don't load the entire thing into memory. Use a stream

    $stream = fopen($csvData->path, 'r');

    if ($stream) {
        while (! feof($stream)) {
            $line = fgets($stream);
            // parse each line and do stuff
        }

        fclose($stream);
    }

Though with a million record dataset the process time to create all those contacts might be an issue

Maybe you should explore the posibility of processing such file with a job in the background

1 like
Maison012's avatar

@psrz

If I understand correctly, the parseImport() method receives a file but you don't actually save it as such. Instead you convert that into a huge array/object, which then save as json in one CsvData record, in a longtext field, right ?

Yes, you are right.

I would save the file as csv in the filesystem

I thing this is a good method, to work with big data. But I need an example couse i does not know how to deal with this. Please can you do an example ?

Maybe you should explore the posibility of processing such file with a job in the background

Also i was thinking about using a job in background but also iam not sure how is best way to work with big data. Maybe i can do both? I mean to save on file system and not in the mysql (temp) and to use job in background?

jlrdw's avatar

If that is the case, I suggest chunking the data and storing in related tables, not one field.

Edit:

For that large amount of data you could also consider a document database.

Just suggestions.

psrz's avatar

@jlrdw

You could "chunk" the file with bash using split https://stackoverflow.com/a/2016918/11170729

 split -l 10000 filename.csv

You'll end up with a bunchs of smaller files each containing 10000 lines each.

That'll give the option of processing the data with a very manageable size, without fear of running of memory, hitting some timeout, etc

Saving the data , in several pieces, in a related table doesn't seem too practical. I mean, you would be saving that million records twice, one as chunks in csv format, then as contacts after all of that is processed

Maison012's avatar

@jlrdw @psrz

For that large amount of data you could also consider a document database.

Maybe this is a chouse but i haven't worked before with document database. Also for chunking data , i have tryed to chunk it and save on temp database where i save now, but there was a problem. I can't upload then on the contacts couse data is chunked on some arrays.

split -l 10000 filename.csv

What is the difference between spliting file with bash comand and with using array_chunk php function?

psrz's avatar

@Leon012

In my suggestion, there wouldn't be an array to chunk. You get the file from the request and you store like that on the filesystem. Then you split it using a bash command (using the Process class from Laravel)

But now you have a bunch of different suggestions.... lol

1 like
Maison012's avatar

@psrz I have tryed to do this method as you sugessted

 $data = $request->file('csv_file')->store('assets');

            $path = storage_path('app/assets/F7p6rC7sLJiwe2bgbXQTQizUAL0DobacaW1buniV.txt');
            $stream = fopen($path, 'r');

          $d =[];
            if ($stream) {
                while (! feof($stream)) {
                    $line = fgets($stream);
                    // parse each line and do stuff
                    $d[] = array($line);
                }
        
                fclose($stream);
            }
            dd($d);

and as return with dd i get this line

"id,first_name,last_name,email,phone\n"

If this is what you have sugested to me. what supposted to do in this line // parse each line and do stuff exactly?

I have push all records from steam to an array and when i do dd i get this array

array:1002 [▼
  0 => array:1 [▶
    0 => "id,first_name,last_name,email,phone"
  ]
  1 => array:1 [▶
    0 => "1,Yoshi,Scurrey,[email protected],619-366-9950"
  ]
  2 => array:1 [▶
    0 => "2,Cindie,Heddan,[email protected],293-157-3839"
  ]

Now i need some help how should i proced ?

Maison012's avatar

@psrz I am actually trying to open stream with a file with 1000 records. But when i try with a file 1000,000 records then i see the page is not working

Tray2's avatar

How I would do it

  1. Upload the file.
  2. Store the file name in a csvs table.
  3. Dispatch a job that imports the file into your database. Into a temporary table https://dev.mysql.com/doc/refman/8.0/en/create-temporary-table.html https://stackoverflow.com/questions/6605765/importing-a-csv-into-mysql-via-command-line
  4. Dispatch the next job that validates the records (chunked).
  5. If all is valid insert it into your regular tables (chunked).
  6. Drop the temporary table
  7. Mark the the file name as imported or failed in the csvs table.
3 likes
Maison012's avatar

@Tray2 I am facing the problem where i upload the CSV file couse it is too large. THis is the first problem

rodrigo.pedra's avatar

@Leon012

From the code you shared, your problem doesn't seem to be related to the size of the upload, but on trying to process it on the same request.

  • Having Laravel Excel process one million records takes time.
  • Or calling array_map + str_getcsv on one million records, takes time.
  • Converting one million records to JSON takes time.
  • Storing o huge JSON string in a database takes time.

It is unpredictable how much time you'll need to increase your server timeout, in case you really want your user to stare their screen waiting for several minutes.

It depends on server load, database load, and you will be consuming a web worker during the whole period.

If your server is configured to have 4 FPM workers, and you have 4 users waiting for huge files to be processed, no one else will be able to access your app.

This is one of the reasons web requests are meant to be quick, so one can serve more requests with fewer resources. Long time processing should be done asynchronously.

Read @tray2 again answers carefully.

In summary

  • On the web request, just store the file on your server somewhere and just save a reference to where it is stored.
  • Before sending the user a response, dispatch a job with the reference to where you stored your job, that will be processed later by the queue.
  • Tell the user their file is being processed, and they will be notified later when processing is done.
  • On the queued job you then read your huge CSV file and do whatever you need, like importing into a database.
  • When processing is finished, you then notify the user.

Also, I wouldn't encode one million CSV rows as JSON and store on a single column for a single record. Even if I didn't need to do anything with it later. These days you can add indices to JSON fields, but I guess they are meant to index JSON documents across several records, and not a huge amount of JSON documents stuffed on a single database record.

If you don't want to import the CSV line by line into the database, both MySQL and Postgres can handle CSV files as tables.

@tray2 already posted enough material to have you informed on how to do most of it.

1 like
Maison012's avatar

@psrz So, even if i use stream to open the file i save on filesystem, again have same problem. The system will go down couse it is too much to handle. How do you think can handle this much data?

psrz's avatar

@Leon012

How much data actually is ? In megabytes I mean.

I'm asking because I downloaded one of the files from this site https://stats.govt.nz/large-datasets/csv-files-for-download/ and one of those, with 129 mb of size has almost 6 millions lines. Obviously, it all depends how many columns and how long each field is

Anyways, go step by step. First set up your controller and web server so you can store a big file.

    $file = $request->file('file');

    $file_name = uniqid('contacts_') . '.csv';

    $result = $file->storeAs('tmp/', $file_name, ['disk' => 'local']);

Check your php.ini


post_max_size = 500M
upload_max_filesize = 500M
max_execution_time = 60

Something like that should be plenty I think.

Make your controller as small as posible. The three lines I posted above are enough. Try the endpoint and check if the file is where it's supposed to be. Only then start thinking about processing the records.

1 like
Tray2's avatar

@Leon012 Not sure what the heck you have in your csv, but I just exported about 1.3 million records from a table with nine fields, It took aqbout 4 seconds, the file was around 300Kb, and took 14 seconds to import.

Do you have images inside your database as a blob or clob?

2 likes
Maison012's avatar

@Tray2 No i does not have images on my database, i have only text. Also is a basic database structure already, contains only basic laravel migration and contact table and csv_data table.

It took aqbout 4 seconds, the file was around 300Kb

Sound good, my actual file is about 120MB but i cant import , when i try to import i see some errors our of memory or a awww snap crash on chrome page. I can import 1000,000 rows with function i have provided above on filesystem but i cant display the data on 1st step to continue to make the matching field for 2step

Maison012's avatar

@psrz Okayy then, Yes this works. It take a bit to insert about 1,9 million rows with about 90MB Size. But still i have some problems where i try to read this data. I also have tryed to use stream to upen this file and get the data as i provided above

$d =[];
            if ($stream) {
                while (! feof($stream)) {
                    $line = fgets($stream);
                    // parse each line and do stuff
                    $d[] = array($line);
                }
        
                fclose($stream);
            }
            dd($d);

I cant send this data on my blade view where i have a table where i display 2 or 3 rows record just for field matching (this is my 1step) after i match i go to second step where need to add on database where i thing should use job batching? But i still am a bit stuck here on 1step

Maison012's avatar

@psrz I am trying to open stream with this function

$file = $request->file('csv_file');
            
            $file_name = uniqid('contacts_') . '.csv';

            $result = $file->storeAs('tmp/', $file_name, ['disk' => 'local']);

            $stream = fopen(storage_path('app/tmp/'.$file_name.''), 'r');
            $d =[];
            if ($stream) {
                while (! feof($stream)) {
                    $line = fgets($stream);
                    // parse each line and do stuff
                    // dd($line); // If i uncoment this line i get a response witch is `"time_ref,"account","code","country_code","product_type",value,"status""` this is the first row of csv
                    $d[] = $line;
                }
        
                fclose($stream);
            }
            dd($d);

But if i try to populate $d array i get this page is not working, https://prnt.sc/IT0X6xAKylA4 so i am not sure how can i pass csv data to my blade table

psrz's avatar

@Leon012

Okay, then you have to parse each line, it's only string at this point.

   if ($stream) {
        // get the first line for  the headers
        $headers = explode(',', trim(fgets($stream)));

        while (! feof($stream)) {
            $values = explode(',', trim(fgets($stream)));
            $row = array_combine($headers, $values);
            // this is 1 record
            dd($row);
        }

        fclose($stream);
    }

And that's about it. With $row you can what you need. I saw above you were using $contact = new Contact(); .... $contact->save(); to insert each record. That is going to be really slow. A million round trips to the database

My suggestion is build an array of $row, maybe 5000 to start, and then use the DB::insert() method that @tray2 linked above for a "bulk" insert

1 like
Tray2's avatar

@Leon012 You really shouldn't do that in the request.

These are the commands that I used.

The export

 select * from books into outfile '/users/tray2/code/books.csv' fields terminated by ',' enclosed by '"' lines terminated by 'n';
Query OK, 1311006 rows affected, 1 warning (4.426 sec)

The import

load data infile "/users/tray2/code/books.csv" into table books columns terminated by ','  enclosed by '"' lines terminated by 'n' ignore 1 lines;
Query OK, 1311006 rows affected (13.812 sec)
Records: 1311006  Deleted: 0  Skipped: 0  Warnings: 0
2 likes
Maison012's avatar

@psrz I already fixed the first step, I believe in my problem. That's what I think at least. But you can give me an opinion if this function can be improved, and many thanks for the help.

this is parseImport function

$file = $request->file('csv_file');
        $file_name = uniqid('contacts_') . '.csv';
        $result = $file->storeAs('tmp/', $file_name, ['disk' => 'local']);

        $stream = fopen(storage_path('app/tmp/'.$file_name.''), 'r');
        if ($stream) {
            // get the first line for  the headers
            $headings = explode(',', trim(fgets($stream)));
            // dd($headings);
            while (! feof($stream)) {
                $values = explode(',', trim(fgets($stream)));
                $row = array_combine($headings, $values);
                
                // this is 1 record
                // dd($row);

                $contact = new Contact;
                $table = $contact->getTable();
                $db_field = \Schema::getColumnListing($table);
                // dd($db_field);

                return view('import_fields', [
                    'headings' => $headings ?? null,
                    'csv_data' => $row,
                    'db_field' => $db_field
                ]);
            }
    
            fclose($stream);
        }

Now can you give some help with processImport 2st part please? This function does not work anymore, but how can i do this?

 $data = CsvData::find($request->csv_data_file_id);
        $csv_data = json_decode($data->csv_data, true);
        foreach ($csv_data as $row) {
            $contact = new Contact();
            foreach ($request->fields as $csv_index => $db_field) {
                $csv_field = $row[$csv_index];
                $contact->$db_field = $csv_field;
            }
            $contact->save();
        }

        return redirect()->route('contacts.index')->with('success', 'Import finished.');

Also this is all my form, when i do matching field and after i click submit function pass to processImport function

<form action="{{ route('import_process') }}" method="POST">
                        @csrf


                        <table class="min-w-full divide-y divide-gray-200 border">
                            @if (isset($headings))
                                <thead>
                                <tr>
                                    @foreach ($headings as $csv_header_field)
                                        {{--                                            @dd($headings)--}}
                                        <th class="px-6 py-3 bg-gray-50">
                                            <span class="text-left text-xs leading-4 font-medium text-gray-500 uppercase tracking-wider">{{ $csv_header_field }}</span>
                                        </th>
                                    @endforeach
                                </tr>
                                </thead>
                            @endif

                            <tbody class="bg-white divide-y divide-gray-200 divide-solid">
                                <tr class="bg-white">
                                    @foreach($csv_data as $key => $row)
                                        <td class="px-6 py-4 whitespace-no-wrap text-sm leading-5 text-gray-900">
                                            {{ $row }}
                                        </td>
                                    @endforeach
                                </tr>

                            <tr>
                                @foreach ($csv_data as $key => $value)
                                    <td class="px-6 py-4 whitespace-no-wrap text-sm leading-5 text-gray-900">
                                        <!-- <label for="csv_field_{{ $key }}">{{ $value }}</label> -->
                                        <select id="csv_field_{{ $key }}" name="fields[{{ $key }}]">
                                            <option value="">-- Select --</option>
                                            @foreach ($db_field as $field)
                                                <option value="{{ $field }}"
                                                        @if (old("fields.$key") == $field) selected @endif>{{ $field }}</option>
                                            @endforeach
                                        </select>
                                    </td>
                                @endforeach
                            </tr>
                            </tbody>
                        </table>

                        <x-button class="mt-4">
                            {{ __('Submit') }}
                        </x-button>
                    </form>
Maison012's avatar

@Tray2

The importload data infile "/users/tray2/code/books.csv" into table books columns terminated by ',' enclosed by '"' lines terminated by 'n' ignore 1 lines; Query OK, 1311006 rows affected (13.812 sec) Records: 1311006 Deleted: 0 Skipped: 0 Warnings: 0

I understand the logic on this code but, how exactly can i do this on my second step in laravel?

Maison012's avatar

@Tray2 I am using laravel 8 and does not found documentation for Process facade https://laravel.com/docs/8.x/processes

I think https://laravel.com/docs/8.x/queues queue is a good idea to import large files but also i am considering https://laravel.com/docs/9.x/collections#lazy-collections (suggest from @hondnl )

Also i am trying to do this function with a queue job i am trying also to do the best way. So i am trying to add the matched data on mysql.table with this function

$file_name = '_contacts.csv';
        $filePath = storage_path('app/tmp/'.$file_name.'');
        
        $stream = fopen($filePath, 'r');
        if ($stream) {
            // get the first line for the headers
            $headings = explode(',', trim(fgets($stream)));

            while(! feof($stream)) {
                // get the values from file
                $values = explode(',', trim(fgets($stream)));

                foreach ($values as $row) {
                    // dd($values);
                    $contact = new Contact();
                    foreach ($request->fields as $csv_index => $db_field) {
                        // dd($csv_index);
                        $csv_field = $row[$csv_index];
                        $contact->$db_field = $csv_field;
                    }
                    $contact->save();
                }

            }

            fclose($stream);
        }

But i am getting an error on

Illegal string offset 'id'

//On this line    
$csv_field = $row[$csv_index];

Can you help me with that error? Any idea?

Tray2's avatar

@Leon012 Let me put it like this.

Don't do anything with php that you can let the database do for you.

Maison012's avatar

@Tray2 I does not understand this

Don't do anything with php that you can let the database do for you.

What you mean?

I am trying to add on database field matched. I think the database cant do this couse i need to match field manually. With matched i mean (for example phone on csv will added on phone_number on database)

Tray2's avatar

@Leon012 Exactly what I wrote. If you can do it in the database do it there.

Ponder this query

SELECT *
FROM contacts
WHERE phone_number = '555-555-555';

Or this one

INSERT INTO table2 (column1, column2, column3, ...)
SELECT column1, column2, column3, ...
FROM table1
WHERE condition;

Any matching can and should be done in the database, your csv should have a predifined order on the columns, the first row should contain column names. That way you don't need to match anything manually.

Also since you really should store it in a temporary table, you can then mix and match to your hearts content in side the database.

One CSV could then be inserted into multiple tables.

1 like
Maison012's avatar

@Tray2

That way you don't need to match anything manually.

I do, couse sometime i need to match manually. For example: i have on csv this header first_name, last_name, phone and on my db first_name, last_name, phone, phone_number so i need phone from csv to be added on phone_number on db so i should select it manually.

Also since you really should store it in a temporary table

Now i save it on filesystem not on temp table on db.

Any matching can and should be done in the database

I am trying to add on my db like this , i think there is no insert query or am i worng?

while(! feof($stream)) {
                // get the values from file
                $values = explode(',', trim(fgets($stream)));

                foreach ($values as $row) {
                    // dd($row);
                    $contact = new Contact();
                    foreach ($request->fields as $csv_index => $db_field) {
                        $csv_field = $row[$csv_index];
                        dd($csv_field);
                        $contact->$db_field = $csv_field;
                    }
                    $contact->save();
                }

            }

this is function inside a stream. but as i told you i get Illegal string offset 'id' error. So where do you think is this problem? and how can i fix this?

Maison012's avatar

@Tray2 i saw the documentation, and understand the logic you are telling me. But can you do an example with my code i cant do it with laravel. at the moment I'm stuck on it, I don't know how I can make this possible in my case and with the code I've shown you here

Tray2's avatar

@Leon012 I don't work for free. Try yourself and we will help you if you run into trouble

Maison012's avatar

@Tray2 I am not asking you to work for free, i am asking you to help me to slove this problem. I have done all my function thanks of the sugesstions of others on this forum. But now i am facing another problem and i am not fixing it by myself , iam stuck here on a piece of my function.

while(! feof($stream)) {
                // get the values from file
                $values = explode(',', trim(fgets($stream)));

                foreach ($values as $row) {
                    // dd($row);
                    $contact = new Contact();
                    foreach ($request->fields as $csv_index => $db_field) {
                        $csv_field = $row[$csv_index]; // This is the line i think is the problem. Can you help me to fix this problem here?
                        dd($csv_field);
                        $contact->$db_field = $csv_field;
                    }
                    $contact->save();
                }

            }

also this is error i get

Illegal string offset 'id'
Maison012's avatar

@Tray2 i have tryed with array_key_exists but it shows same error. This is how i have tryed

while(! feof($stream)) {
    // get the values from file
    $values = explode(',', trim(fgets($stream)));

    foreach ($values as $row) {
        // dd($row);
        $contact = new Contact();
        foreach ($request->fields as $csv_index => $db_field) {
            if (array_key_exists($csv_index, $request->fields)) {
                $csv_field = $row[$csv_index];
                dd($csv_field);
                $contact->$db_field = $csv_field;
            }
        }
        $contact->save();
    }

}

But i think id exists here couse when i do dd($values) i get

array:5 [▼
  0 => "1"
  1 => "Yoshi"
  2 => "Scurrey"
  3 => "[email protected]"
  4 => "619-366-9950"
]
Tray2's avatar

@Leon012 Your array doesn't seem to be an assosiative array (key => value), but rather a regular array.

The index for those a numeric so you would need to do something like

$model->id = $row[0];
$model->first_name =  $row[1];
Maison012's avatar

@Tray2 I see but i am not finding any way to pass index on the $row . Before the $csv_index return me same value as now. But i am not understanding the difference between my old function and new function couse my old function work like this, should i convert to json or something like this?

rodrigo.pedra's avatar

@Leon012

I will assume the CSV's first line is header line, e.g. contains the column names that matches your database column names.

// assuming your are ingesting directly from an uploaded file, 
// on the same request, which you should consider avoiding 
// for huge files, as already explained before
$path = $request->file('file')->store('tmp');

$file = new \SplFileObject(storage_path('app/' . $path));

// this tells \SplFileObject to read all lines as CSV records
$file->setFlags(SplFileObject::READ_CSV);

// use this if your CSV has different separators, enclosure, or escape characters
// $file->setCsvControl(...);

foreach ($file as $index => $record) {
    if ($index === 0) {
        $header = $record;
    } else {
        Contact::forceCreate(array_combine($header, $record));
    }
}

If your CSV's first line doesn't match your database column names, you can manually set the $header values to your database column names in the same order you have their corresponding data on the CSV you are importing.

And if your CSV doesn't have a header line, you can just remove the if clause inside the for, keeping the code inside the else branch.

Depending on where, and how the CSV is generated there can be some issues, like BOM sequences, different encodings, etc.

I often reach to the league/csv package for ingesting/exporting delimited data, due to these limitations. But I am in Brazil, where often CSV exported from Excel is exported in UTF-16LE due to Portuguese language diacritics and line breaks within cells, besides using semicolon as separator by default (comma is a decimal separator locally).

If your CSV is well-behaved, and your MySQL server allows remote data ingestion, take @tray2 advice and go with LOAD DATA INFILE.

References:

rodrigo.pedra's avatar

On my local machine, with MySQL running locally, my code snipped above timed out after 30 seconds (the default), and ingested around 100K records as Eloquent models.

For reference, the file I tested has about 1.2 million records.

Using raw PDO it ingested around 400K on the same 30 seconds

$path = $request->file('file')->store('tmp');

$file = new \SplFileObject(\storage_path('app/' . $path));
$file->setFlags(\SplFileObject::READ_CSV);

$statement = DB::getPdo()->prepare('INSERT INTO `data` (colA, colB, colC) VALUES (?,?,?)');

foreach ($file as $index => $record) {
    if ($index > 0) {
        $statement->execute($record);
    }
}

One downside is remembering to set your timestamps later.

I am working on an ETL library that prepares batch inserts using PDO, it ingested all 1.2 million in 29 seconds (should also timeout on a real server under real world condition). This library is unreleased, and I don't know when I will be able to release it. There are other PHP ETL libraries out there, search packagist for ETL.

LOAD DATA INFILE ingested all the 1.2 million records in around 11 seconds (on my machine).

Even in the LOAD DATA INFILE case I would take an async approach as I described earlier. Loading this amount of data in one go, is really prone to failure. And I guess you haven't considered what to do with import failures yet.

If it was a small dataset, it doesn't really matter, all solutions presented so far should work well for datasets as large as 5k records.

Hope this helps.

rodrigo.pedra's avatar

As a remark SplFileObject is actually an iterator to read your file, as such it will only hold one line at memory at once.

On a PHP script, importing your one million records using the PDO approach should not consume more than 2 MB of memory.

If you want to go with the LazyCollection approach for the cool effect of using it, you can simply create it using SplFileObject iterator:

$file = new \SplFileObject(\storage_path('app/' . $path));
$file->setFlags(\SplFileObject::READ_CSV);

$lazy = LazyCollection::make($file);

Contrary to first sight, it will lazily read your file, line by line, as SplFileObject is already a PHP iterator.

Maison012's avatar

@rodrigo.pedra Thank you for replaying and for helping me. So you mean to remove my stream function and to use what you have sugessted to me here

I will assume the CSV's first line is header line, e.g. contains the column names that matches your database column names.

Couse this is what i have tryed to do and it does not work for me. It says header and record array should be same column

rodrigo.pedra's avatar

@Leon012

The array_combine() call is mangling the first line as the keys, and the current record as the values, before passing them to Eloquent.

If your eloquent columns are named differently, just hardcode the headers array in the same order to match your CSV data.

Something like this:

// hardcode the field names as they are called in your DB
// in the same order they appear on CSV
$header = ['column_a', 'column_b', 'column_c'];

foreach ($file as $index => $record) {
    if ($index === 0) {
        // ignore first CSV line with different column names
    } else {
        // array_combine(), will turn 
        // [1, 2, 3] into ['column_a' => 1, 'column_b' => 2, 'column_c' => 3]
        // so Eloquent can handle it
        Contact::forceCreate(array_combine($header, $record));
    }
}
rodrigo.pedra's avatar

Of course, you can get rid of the else branch by changing your if condition to:

if ($index > 0) {
    Contact...
}

I left as it was to highlight the first line was being skipped.

Maison012's avatar

@rodrigo.pedra

If your eloquent columns are named differently, just hardcode the headers array in the same order to match your CSV data.

But i does not want to hardcode my columns. I want to save ti as i match manually on the 1st step.

rodrigo.pedra's avatar

@Leon012

Well, you need to map from one field set to another somehow.

If CSV columns are named as the first row, you can grab just the first row, by using the foreach as is and breaking after the first iteration.

The database columns you already know how to grab from your first post.

I guess in your UI you will somehow map one to another, maybe using a list of select fields.

Then when processing the file you just map one record shape to another, something like this:

// Your UI sends a mapping between DB columns and CSV columns
// on the request
$columnsMap = [
    // ...
];

foreach ($file as $index => $record) {
    if ($index === 0) {
        // let's keep the CSV header in its current order 
        $header = $record;
        
        continue;
    }

    // let's key the current record by its CSV column names
    // we'll use the first record as it is ordered the same
    $record = array_combine($header, $record);

    $attributes = [];
    
    // map CSV values into DB columns
    foreach ($columnsMap as $dbName => $csvName) {
        // null coalesce operator avoids erroring if the key does not exist
        $attributes[$dbName] = $record[$csvName] ?? null;
    }

    Contact::forceCreate($attributes);
}
Maison012's avatar

@rodrigo.pedra I have done something similar using stream i think. You can see here

$file_name = '_contacts.csv';
        $filePath = storage_path('app/tmp/'.$file_name.'');

        $stream = fopen($filePath, 'r');
        if ($stream) {
            // get the first line for the headers
			// get header from csv file  
            $headings = explode(',', trim(fgets($stream)));

            while(! feof($stream)) {
                // get the values from file
                $values = explode(',', trim(fgets($stream)));

                foreach ($values as $index => $row) {
                    // dd($values);
                    // $contact = new Contact();
                    $datas = [];
                    foreach ($request->fields as $csv_index => $db_field) {
                        if (array_key_exists($csv_index, $request->fields)) {
                            // $csv_field = $row[$csv_index] ?? null;
                            $datas[$db_field] = $row[$csv_index] ?? null;
                        }
                    }
                    // dd($datas);
                    Contact::forceCreate($datas);
                }

            }

            fclose($stream);
        }

but the problem is Illegal string offset 'id' on this line

 $datas[$db_field] = $row[$csv_index] ?? null;

Couse the csv index is not founded on $row i think. I also have dd the $request->fields and this is the result,

array:5 [▼
  "id" => "id"
  "first_name" => "first_name"
  "last_name" => "last_name"
  "email" => "email"
  "phone" => "phone_number"
]

I am not understanding why iam getting this error

rodrigo.pedra's avatar

@Leon012

In your code, $row is not an array, it is a string. It won't have any character indexed as id. As your code is now, I would call it $cell and not $row, to better describe what data it is holding.

$values is the array from the line you just read. But as you are creating it with explode(), it will only have integer indices.

You can use array_combine() to use the $headings as $values keys, much like I did in my code snippets.

But event though I also don't understand where the error you are getting come from, as the null coalesce operator was to prevent that.

$ php -a
Interactive shell

php > $array = [];
php > echo $array['id'] ?? 'not found';
not found
php > $string = 'foo';
php > echo $string['id'] ?? 'not found';
not found
php > 

Note, this is the built-in PHP interactive shell. In both cases, the error you say you are getting does not happen, as it is not expected to happen when using the null coalesce operator with the offset operator on either an array or a string.

One additional thing: your if condition is not necessary, in the sense you are already iterating over the keys and values from $request->fields, so you already know $csv_index exists as a key.

I guess you want to check over the current CSV record, which is held by your variable called $values, but that is currently indexed by integers at the moment, so array_key_exists() with a string field would always fail.

Take a look at the code snippets people had already contributed. From your description, it seems you have enough material to solve your issue.

Also as an advice, when asking people from help with code, read their answers carefully, and try out all the answer's code, even if you prefer to stick to your own code style.

Trying to cherry-pick a piece here and there when you don't even really understand your own solution is not the best way to value the help others spent time on putting together.

And also, if you are not fully understanding your own code (from your own words when describing you can't tell why that error is happening), and seems to not being testing any answer fully, how do you know you cherry-picked the right part that would help your problem?

Well, where I come from we usually say if advices were any good, no one would give them, but they would sell them. So take mine with a grain of salt.

Good luck =)

rodrigo.pedra's avatar

Testing the previous sent code.

<?php

// note last line has a comma inside last_name,
// which would fail with explode
$input = <<<'CSV'
id,first_name,last_name,email,phone_number
1,John,Doe,[email protected],"555-0000"
2,Mary,Jane,[email protected],"555-0001"
3,Ronald,"McDonalds, PhD",[email protected],"555-0003"
CSV;

$columnsMap = [
    'id' => 'id',
    'first_name' => 'first_name',
    'last_name' => 'last_name',
    'email' => 'email',
    'phone' => 'phone_number',
];

// using https://www.php.net/wrappers.data
$file = new \SplFileObject('data://text/plain,' . urlencode($input));
$file->setFlags(\SplFileObject::READ_CSV);

foreach ($file as $index => $record) {
    if ($index === 0) {
        $header = $record;

        continue;
    }

    $record = array_combine($header, $record);

    $attributes = [];

    foreach ($columnsMap as $dbName => $csvName) {
        $attributes[$dbName] = $record[$csvName] ?? null;
    }

    // emulate attributes passed to Contact::forceCreate()
    var_dump($attributes);
}
$ php test.php 
array(5) {
  ["id"]=>
  string(1) "1"
  ["first_name"]=>
  string(4) "John"
  ["last_name"]=>
  string(3) "Doe"
  ["email"]=>
  string(19) "[email protected]"
  ["phone"]=>
  string(15) "555-0000"
}
array(5) {
  ["id"]=>
  string(1) "2"
  ["first_name"]=>
  string(4) "Mary"
  ["last_name"]=>
  string(4) "Jane"
  ["email"]=>
  string(20) "[email protected]"
  ["phone"]=>
  string(15) "555-0001"
}
array(5) {
  ["id"]=>
  string(1) "3"
  ["first_name"]=>
  string(6) "Ronald"
  ["last_name"]=>
  string(14) "McDonalds, PhD"
  ["email"]=>
  string(21) "[email protected]"
  ["phone"]=>
  string(15) "555-0003"
}

Also, if you prefer to use the file functions family (fopen, fgets, ...), look into fgetcsv instead of using explode() to parse CSV data. If using explode() what a good alternative to parse CSV data, they wouldn't provide so many CSV helper functions since PHP 4.

https://www.php.net/manual/en/function.fgetcsv

hondnl's avatar

Take a look at Lazy Collections , https://laravel.com/docs/9.x/collections#lazy-collections

So you only process 1 line at the time , reducing the amount of memory needed.

  1. save the uploaded data to a file.
  2. make a new lazy Collection and ask to stream each line from that file.
  3. do something with it ( save each line to a database row for example)
1 like
Maison012's avatar

@hondnl I am trying to do this function for importing data with LazyCollection

public function processImport(Request $request)
    {
LazyCollection::make(function () {
            $filePath = storage_path('app/tmp/contacts_63f342dde053c.csv');
            $handle = fopen($filePath, 'r');

            while ($line = fgetcsv($handle)) {
                yield $line;
            }
        })->chunk(10000) //split in chunk to reduce the number of queries
            ->each(function ($lines, $request) {
                foreach ($lines as $line) {
                    $contact = new Contact();
                    foreach ($request->fields as $csv_index => $db_field) {
                        $csv_field = $line[$csv_index];
                        $contact->$db_field = $csv_field;
                    }
                    $contact->save();
                }
            });
  return redirect()->route('contacts.index')->with('success', 'Import finished.');
    }

But i am getting this error

Trying to get property 'fields' of non-object

Also this is my actuall form for matching fields

@foreach ($csv_data as $key => $value)
                                    <td class="px-6 py-4 whitespace-no-wrap text-sm leading-5 text-gray-900">
                                        <!-- <label for="csv_field_{{ $key }}">{{ $value }}</label> -->
                                        <select id="csv_field_{{ $key }}" name="fields[{{ $key }}]">
                                            <option value="">-- Select --</option>
                                            @foreach ($db_field as $field)
                                                <option value="{{ $field }}"
                                                        @if (old("fields.$key") == $field) selected @endif>{{ $field }}</option>
                                            @endforeach
                                        </select>
                                    </td>
                                @endforeach
Maison012's avatar

@hondnl I just tryed this function as you suggest

LazyCollection::make(function () {
    $file_name = '_contacts.csv';
    $filePath = storage_path('app/tmp/'.$file_name.'');
    $handle = fopen($filePath, 'r');

    while ($line = fgetcsv($handle)) {
        yield $line;
    }
})->chunk(10000) 
    ->each(function ($lines) use($request) {
        foreach ($lines as $line) {
            // dd($line);
            $contact = new Contact();
            foreach ($request->fields as $csv_index => $db_field) {
                $csv_field = $line[$csv_index]; //this is the line where i get error
                $contact->$db_field = $csv_field;
            }
            $contact->save();
        }
    });

error i get

Undefined index: id
hondnl's avatar

@Leon012 Well it means $csv_index = 'id' and he tries to find that in the array $line, but it does not exists. dump( $requests->all()) at the top and dd ($line) in the loop for me , You will see where it goes wrong.

1 like
Maison012's avatar

@hondnl dump( $requests->all()) prints this

array:2 [▼
  "_token" => "bJ343cyGxyRNPfObWv3XeWxodkeDwzpNuuS47SCA"
  "fields" => array:5 [▶
    "id" => "id"
    "first_name" => "first_name"
    "last_name" => "last_name"
    "email" => "email"
    "phone" => "phone_number"
  ]
]

dd($line) print this array

foreach ($lines as $line) {
                    dd($line); // You mean dd here?

array:5 [▼
  0 => "id"
  1 => "first_name"
  2 => "last_name"
  3 => "email"
  4 => "phone"
]

with the method i have posted on first query the return was same as now. But now it does not work. Maybne is the problem from my form? or should i do any convertions here on backend?

psrz's avatar

@leon012

Four day weekend.... Lots of things going on here....

Does the production server (or would be) has the mysql client installed ? If the server has both services, web and database, then yes. But if the mysql server is another machine, can you install the mysql client on the web server ?

I ask this because you might want to explore @tray2's suggestion, importing the csv file using mysql command line. Not only that would be the fastest but also it's entirely different from which you've been attempting and not working

2 likes
rodrigo.pedra's avatar

@psrz

I totally agree. Using @tray2 suggestion is much more efficient, as many have already said.

But unfortunately, @leon012 doesn't say why they are so focused on insisting in a PHP solution. If they can't change their environment, that would be a valid reason.

Or if they want to learn how to do it in PHP, I can't tell why they barely try out any advice.

1 like
Maison012's avatar

@psrz @rodrigo.pedra Yes my production server has mysql client. I also tried it the way of @tray2 but I couldn't understand it 100%. It is worth emphasizing that I am a beginner in this work. I have also tried other solutions from everyone who contributed to this topic. But since it takes a few days and there are many ways to choose, I have tried them all. @tray2 way to upload without php, I don't know how to do it even though there are documentation links, but they are a bit complicated for me at the moment. I tried @rodrigo.pedra way and it works although the page times out after 120 seconds but it's a way that works. I'm just trying to do the best I can

Or if they want to learn how to do it in PHP, I can't tell why they barely try out any advice.

This is a good reason why i want to do in php. But also i can try and another way.

jlrdw's avatar

@Leon012 have you read about importing in the MySQL documentation, it covers all of that.

Do some trial and error on a small test CSV file. These are my suggestions.

rodrigo.pedra's avatar

@Leon012

@tray2 solution is not about doing fully without PHP.

When you are calling Contact::forceCreate(...), it isn't PHP that is serializing the data, and saving into a database file.

It defers to MySQL for this task. Laravel provides a convenient way to abstract writing your own SQL statements, but at the end of the day, PHP is just executing a SQL string.

@tray2 solution wasn't about you having a shell script somewhere in your server, or granting your app's user access to your MySQL server, so they could execute that query themselves.

It was about you using that MySQL feature to perform that operation from PHP, within your Laravel application.

If you don't know how to execute raw SQL statements from PHP, or from Laravel, you could have asked on how you would execute the query @tray2 had already provided.

I put together a small script you can use within a controller's method.

$path = $request->file('file')->store('tmp');
$path = \storage_path('app/' . $path);

$file = new \SplFileObject($path);
$header = $file->fgetcsv();

$columnsMap = $request->input('fields', []);

// invert to have it keyed by CSV columns (CSV => DB)
$columnsMap = array_flip($columnsMap);

$columns = collect($header)
    ->map(fn ($column) => $columnsMap[$column] ?? null)
    ->map(fn ($column) => $column ? '`' . addcslashes($column, '`') . '`' : '@skip')
    ->implode(',');

$path = DB::getPdo()->quote($path);

$sql = <<<SQL
LOAD DATA LOCAL INFILE {$path}
INTO TABLE `data`
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
ESCAPED BY '\\\\'
LINES TERMINATED BY '\\n'
IGNORE 1 ROWS
({$columns})
SQL;

DB::statement($sql);

Note that you might have to check on backslashes -- they might need to be doubled -- as Laracasts markdown renderer uses it for escaping, PHP uses it for escaping, and MySQL uses it for escaping. So often some are missing on code provided on the forums.

Read the code carefully. Add a dd() to debug it after each part you don't understand. Reading code is a great way to improve our own coding skills.

But as I already said, if advices were any good, no one would give them away for free 😉

1 like
Maison012's avatar

@rodrigo.pedra Now i see, And I definitely appreciate your advice and help and thank you very much. Since you say this is the best way to do this job, I think I will use this. Thank you for clarifying this for me. There is one more thing that I cannot understand. In the way that I have created a job queue and I can manage to insert millions of data there, it doesn't take much time actually, but at the end of the data key, after they are inserted, I get such an error

local.ERROR: array_combine(): Both parameters should have an equal number of elements {"exception":"[object] (ErrorException(code: 0): array_combine(): Both parameters should have an equal number of elements

And the function is like that

$file = new SplFileObject(storage_path('app/tmp/' . $file_name));
        $file->setFlags(SplFileObject::READ_CSV);

        foreach($file as $index => $record) {
            if ($index === 0) {
                $header = $record;

                continue;
            }

            $record = array_combine($header, $record);

            $datas = [];
            foreach($this->columnsMapp as $dbName => $csvName) {
                $datas[$dbName] = $record[$csvName] ?? null;
            }

            // Contact::forceCreate($datas);
            Contact::insert($datas);

            unlink($record);
        }

I have used Contact::Insert couse i think is mor faster than forceCreate. But can you tell me please why iam getting this error , i think the array combine have equal numbers of element

array:5 [▼
  "id" => "1"
  "first_name" => "Yoshi"
  "last_name" => "Scurrey"
  "email" => "[email protected]"
  "phone" => "619-366-9950"
]
rodrigo.pedra's avatar

@Leon012

On top of my head, I'd guess your CSV has an extra blank line at the end.

Add this if-clause to skip blank rows:

$file = new SplFileObject(storage_path('app/tmp/' . $file_name));
        $file->setFlags(SplFileObject::READ_CSV);

        foreach($file as $index => $record) {
            if (blank($record)) {
                continue; // skip empty rows
            }

            if ($index === 0) {
                $header = $record;

                continue;
            }

            $record = array_combine($header, $record);

            $datas = [];
            foreach($this->columnsMapp as $dbName => $csvName) {
                $datas[$dbName] = $record[$csvName] ?? null;
            }

            // Contact::forceCreate($datas);
            Contact::insert($datas);

            unlink($record);
        }

blank() is a Laravel helper. As $record is an array, empty($record) should also work.

Maison012's avatar

@rodrigo.pedra Should it be this function that I made really slow since on my computer (I haven't tested it on the server yet). It takes almost 1 hour to upload about 40,000 records from a file that is 120MB (with almost 5 million records with 4 fields)?

Note I have inserted this function on a job queue

Maison012's avatar

@rodrigo.pedra I think i need to change a bit configuration on my mysql client ?

PDOStatement::execute(): LOAD DATA LOCAL INFILE forbidden (SQL: LOAD DATA LOCAL INFILE ...
Maison012's avatar

@rodrigo.pedra also this function is not working

if (blank($record) {
                continue; // skip empty rows
            }

show me same error

rodrigo.pedra's avatar

@Leon012

Should it be this function that I made really slow since on my computer (I haven't tested it on the server yet). It takes almost 1 hour to upload about 40,000 records from a file that is 120MB (with almost 5 million records with 4 fields)?

It shouldn't. Might be some local configuration. Or if you have a low thread count, your CPU might be struggling to keep a lot of things happening simultaneously.

rodrigo.pedra's avatar

@Leon012

I think i need to change a bit configuration on my mysql client ?

Actually on the server. Search for local-infile config option on MySQL server configuration.

rodrigo.pedra's avatar

@Leon012

also this function is not working ... show me same error

You could have a malformed CSV where commas within fields are not quoted, o that have different field count per line.

You could try this instead:

if (count($record) !== count($header)) {
    logger()->error('field mismatch', $record);

    continue; // do not try to insert a mismatched record
}

And then check your logs for any errors.

Maison012's avatar

@rodrigo.pedra

Actually on the server. Search for local-infile config option on MySQL server configuration.

If you mean this

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| local_infile  | ON    |
+---------------+-------+

I have already set it on

Maison012's avatar

@rodrigo.pedra

Might be some local configuration

What type of config. You mean php.ini configurations?

your CPU might be struggling to keep a lot of things happening simultaneously.

No, my cpu is on 40%

What you think i can change on my local configuration? This is taking too long

psrz's avatar

@Leon012

you're missing the enclosing parentheses

if (blank($record)) {
     continue; // skip empty rows
 }

psrz's avatar

@Leon012

The issue I see is this Contact::insert($datas); That inserts one record. So if you have a couple millions lines in the csv that means a couple of millions round trips to the database.

You need less trips to the database so you must insert by "bulk". It's quite simple, accumulate a bunch of records on one single array


$file = new SplFileObject(storage_path('app/tmp/' . $file_name));
        $file->setFlags(SplFileObject::READ_CSV);

       $batchSize = 1000;
       $batch = [];

        foreach($file as $index => $record) {
            if (blank($record)) {
                continue; // skip empty rows
            }

            if ($index === 0) {
                $header = $record;

                continue;
            }

            $record = array_combine($header, $record);

            $datas = [];
            foreach($this->columnsMapp as $dbName => $csvName) {
                $datas[$dbName] = $record[$csvName] ?? null;
            }

		    // I assume the contacts table has these fields. Since you won't be using Eloquent, you have to set them manually
            $datas['created_at'] = $datas['updated_at'] = now();
            $batch[] = $datas;

   	    // once you've accumulate "enough", you insert them all in one trip and then start to accumulate again
            if (count($batch) === $batchSize) {
                \DB::table('contacts')->insert($batch);
                $batch = [];
            }

            unlink($record);
        }

        // A final trip with whatever you have left
        if (count($batch) > 0) {
           \DB::table('contacts')->insert($batch);
       }

IIRC, PDO can map up to 65k values so you can ballpark how big can $batchSize be by considering how many columns and how many records you want to insert at one time. Try first with a few thousands, then increase that a bit and check how it goes. In any case, I wouldn't go to the max possible since that probably take up too much memory

I'm also assuming that you don't need the Eloquent events when inserting contacts, because by using DB::table('contacts')->insert() none of that is happening.

1 like
Maison012's avatar

@psrz

you're missing the enclosing parentheses

Yes, i know there is missing an eclosing parentheses. But on my code i have fixed this. I des not added this problem couse i thought it is not nessesary couse any editor will mark it as error if any bracket is missing. Anyway. it is not working and show me the same error.

Also i have tryed this

if (count($record) !== count($header)) {
    logger()->error('field mismatch', $record);

    continue; // do not try to insert a mismatched record
}

and this prints me field mismatch on laravel.log file

Maison012's avatar

@psrz Thank you soo much for this . I just tryed this method and it work fine i think, also i have mondifyed php.ini memory_limit = 128M to 500M and now just uploaded 5985000 this amount of record with 5 field for about 3 minutes. It is much faster than before.

I thought I was doing the same thing, I mean I thought I was sending an array to the database and not each record one by one. that was why i was using Contact::insert($datas);

Still i get a error on my laravel.log for array_combine after data is uploaded , Also another error for unlink($record); it except path and not array value

Maison012's avatar

@jlrdw Yes, i am on it. Just letting know where are some small problems here

jlrdw's avatar

@Leon012 I still suggest follow @tray2 guidelines. I have imported large amounts of data following the same techniques. Anytime you can, let the database do the work. In my case I used a stored procedure. It's been a while however.

Maison012's avatar

@jlrdw i have tryed i think and i am getting this error PDOStatement::execute(): LOAD DATA LOCAL INFILE forbidden (SQL: LOAD DATA LOCAL INFILE ...

I have posted above a replay. So i does not know exactly how to continue

psrz's avatar

@Leon012

forbidden .... that's your clue.

Looks like your mysql server cannot read that file due to file permissions. If it's located on the storage folder inside your laravel application, sounds about right.

Copy/Move the file to some other folder where the mysql process can read files from

rodrigo.pedra's avatar

@Leon012

Searching for your error message:

https://duckduckgo.com/?t=ffab&q=PDOStatement%3A%3Aexecute%28%29%3A+LOAD+DATA+LOCAL+INFILE+forbidden+%28SQL%3A+LOAD+DATA+LOCAL+INFILE+...

The first result I got is:

https://stackoverflow.com/a/17430025

Which suggests adding PDO::MYSQL_ATTR_LOCAL_INFILE => true to your PDO configuration.

Adapting to a new Laravel installation, add this to your ./config/database.php

'mysql' => [
    'driver' => 'mysql',
    'url' => env('DATABASE_URL'),
    'host' => env('DB_HOST', '127.0.0.1'),
    'port' => env('DB_PORT', '3306'),
    'database' => env('DB_DATABASE', 'forge'),
    'username' => env('DB_USERNAME', 'forge'),
    'password' => env('DB_PASSWORD', ''),
    'unix_socket' => env('DB_SOCKET', ''),
    'charset' => 'utf8mb4',
    'collation' => 'utf8mb4_unicode_ci',
    'prefix' => '',
    'prefix_indexes' => true,
    'strict' => true,
    'engine' => null,
    'options' => extension_loaded('pdo_mysql') ? array_filter([
        PDO::MYSQL_ATTR_SSL_CA => env('MYSQL_ATTR_SSL_CA'),
        PDO::MYSQL_ATTR_LOCAL_INFILE => true, // <<< ADDED THIS LINE
    ]) : [],
],

Note that if you made any other changes to the database configuration, you should preserve them. The snippet above is based on the default configuration available at:

https://github.com/laravel/laravel/blob/9507bf2b2ad9656eabbb7176b554791c5c402026/config/database.php#L46-L64

1 like
Maison012's avatar

@rodrigo.pedra I have searched also and found this solution but there is one problem. It basically works and import about 6 million record in less than 2 seconds, but this is my database table now.

*************************** 1. row ***************************
           id: 1
 	first_name:
 	middle_name: NULL
  	last_name:
  	email:
    phone: NULL
	phone_number: NULL
    lead_id: NULL
    status: NULL
	assigned_user: NULL
    city: NULL
    zip_code: NULL
    source: NULL
    birthday: NULL
    notes: NULL
   	created_at: NULL
   	updated_at: NULL

You can see some fields are blank, there should be inserted with records. but are blank

So like this, we cant match column manually by select option

rodrigo.pedra's avatar

@Leon012

Well you can.

You just have to specify the column listing on the LOAD INFILE statement.

On MySQL docs, there is the syntax description. But you basically list all the columns at the end, and if your CSV has additional columns you don't want to map, you can assign them to a user variable that you won't use, like @skip.

From memory, I thought my sample code covered that.

  • Did you check (with dd() or whatever) the generated SQL statement to see if it was generated as you would expect? Maybe you need to invert the CSV and DB columns in the map array, you will only be able to tell if you check
  • Did you manually run the generated SQL statement to check for any errors?
  • Does this happen to a few lines, or to all lines? Did you check those lines in the CSV file to see if they have missing data?
  • Are using the separator, enclosing and escape sequence your CSV is encoded with?

Please or to participate in this conversation.