Bluk Upload of the data

Hello I am using the Bulk upload of the data in my laravel

public function store(Request $request) { $request->validate([ 'document' => 'required', 'cat_id' => 'required|numeric', 'status' => 'required|in:active,inactive',

    ]);

    // Get the uploaded file
    $file = $request->file('document');

    if ($request->cat_id == 1) {
        // Import data using FastExcel
        $data = (new FastExcel)->import($file);

        // Additional processing, if needed
        // For example, you can loop through the imported data and store it in the database
        foreach ($data as $row) {
            $report = SbiMainUpsReport::create($row);

            // Attach selected user IDs to the report
            // $report->selectedUserIds = $request->user_id;
            $report->save();
        }
    }

    if($request->cat_id == 2) {
        $data = (new FastExcel)->import($file);

        foreach($data as $row) {
            $report = StatusReport::create($row);

            $report->save();
        }
    }

    if($request->cat_id == 3) {
        $data = (new FastExcel)->import($file);

        foreach($data as $row) {
            $report = PspbEventReport::create($row);

            $report->save();
        }
    }

    if($request->cat_id == 4) {
        $data = (new FastExcel)->import($file);

        foreach($data as $row) {
            $report = PspbircEventReport::create($row);

            $report->save();
        }
    }

    // Store other form data (category, status, user_id) in your database tables as needed

   return back()->with('success','Data inserted successfully');
}

this is my controller loogic to upload the bulk data through .xlsx file

use Rap2hpoutre\FastExcel\FastExcel;

this is the package i am using to upload the data in bulk through excel

but i am not getting that what is the issue when i am uplaoding the 80,000 data it taking 2 min how can i optimize the code so that it get uploaded within 10 sec and my server is also very fast so there is no issue of the serve.

please help and Thank you in advance for helping

Robstar

2 years ago

Level 50

For things like this I'd always push this to a queue. You could also experiment using Laravel's batch feature.

Looking at the package you're using there does appear to be methods that use PHP generators which will help things.

Semi related, the method you've posted is screaming out for a factory to clean up the code. Would also recommend you always use strict comparison:

// if ($request->cat_id == 1) {
if (1 === $request->integer('cat_id') {

EDIT: the $report->save() call is not needed.

EDIT 2: the package you're using appears to have a neater syntax:

(new FastExcel())->import('file.xlsx', function ($line) {
    return User::create([
        'name' => $line['Name'],
        'email' => $line['Email']
    ]);
});

wilz

2 years ago

Level 1

@robstar Does this will descrease the uploading timing as i have already tries the batch method but i don't get any proper result

Robstar

2 years ago

Level 50

@wilz Your problem relates to understanding the request.

At the moment you're attempting to process a very large Excel file and then insert 80,000 records into a database.

That is possibly the slowest method because you['re attempting to do everything in a single request.

When you call import() (https://github.com/rap2hpoutre/fast-excel?tab=readme-ov-file#import) you're returning a huge collection object containing 80,000 entries. I can see the package you're using uses generators behind the scenes, which is good. However, even so, you still end up with a huge PHP object.

You could try chunking data into batches, to avoid running a single query for every row, that may help speed things up, see https://laravel.com/docs/11.x/collections#method-chunk. That's a very easy refactor.

Another alternative is to avoid creating the huge collection of rows from your Excel file. Instead, read the file line by line, using a generator and immediately insert the record (or build up a batch of records to insert and then continue reading the file).

However, having users wait for a more than few seconds for a single request isn't good. What you';re attempting to achieve should pretty always be ran behind the scenes on ma queue or scheduled job.

Snapey

2 years ago

Level 122

lines like this,

$report = SbiMainUpsReport::create($row);

are they simple eloquent models or is more going on behind the scenes?

Please or to participate in this conversation.