Mass update DB based on an array from an CSV file

I've been struggeling with a problem and i hope someone will be able to help me.

I'm working on a project in Laravel 5.8. I have CSV export fles from GSC and aHrefs with pages, links, domain raitings etc. form my tests I'm using a file with 80k entries, I will have bigger files 200-500k or even more.

First of all I convert the CSV file in to an array.

function csv_to_array($filename = '', $delimiter = ',')
{

    ini_set('auto_detect_line_endings', TRUE);
    if (!file_exists($filename) || !is_readable($filename))
        return FALSE;

    $header = NULL;
    $data = array();
    if (($handle = fopen($filename, 'r')) !== FALSE) {
        while (($row = fgetcsv($handle, 1000, $delimiter)) !== FALSE) {
            if (!$header) {
                $header = $row;
            } else {
                if (count($header) > count($row)) {
                    $difference = count($header) - count($row);
                    for ($i = 1; $i <= $difference; $i++) {
                        $row[count($row) + 1] = $delimiter;
                    }
                }
                $data[] = array_combine($header, $row);
            }
        }
        fclose($handle);
    }
    return $data;
}

Next I use a for loop to get only the fields I want to store in the DB and then I insert them like this

DB::beginTransaction();

    \DB::disableQueryLog();

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

    for ($i=0; $i <= count($file)-1 ; $i++) { 

        $ar1 =[
            'url' => $file[$i]["Linking page"],
            'last_crawl'  => (array_key_exists('Last crawled', $file[$i])) ? ($file[$i]['Last crawled'] === "N/A" ?  '' : date('Y-m-d', strtotime($file[$i]['Last crawled'])) . " 00:00:00") : "",
            'added' => Carbon::now()
         ];

        Link::create($ar1);

} DB::commit();

And this works fine even with the 80k file.

Butt the problem comes when i have to update the DB. I get the max execution time. First I tried to do it like the insertion in a loop which i know is a bad idea.

  for ($i=0; $i <= count($file)-1 ; $i++) { 

        Link::updateOrCreate(
            ['url' => $file[$i]["Linking page"]],
            [
                'url' => $file[$i]["Linking page"],
                'last_crawl'  => (array_key_exists('Last crawled',$file[$i])) ? ($file[$i]['Last crawled'] === "N/A" ?  '' : date('Y-m-d', strtotime($file[$i]['Last crawled'])) . " 00:00:00" ) : "",
                'added' => Carbon::now()
            ]);
    }

I think I've red all the related topick entries i could find. I was recommended to use this package https://github.com/mavinoo/laravelBatch

But It works with files arround 18k entries and with bigger it also dies :/

And now I just don't have any more ideas on how to do this propperly. I would appreciate on some kind of a hint or maby a code snippet on how to do this the right way, because as I wrote I will have much bigger files than 80k.

I hope I described the problem properly. Thanks for the replies.

from Newest questions tagged laravel-5 - Stack Overflow https://ift.tt/2ZantFv
via IFTTT