cib88's avatar
Level 2

File upload timing out - can I add chunk to updateOrCreate?

I'm trying to upload a user's CSV file and pass the data into a database but because of the size of the CSV / Rows, it keeps timing out.

The data must be checked to see if it's already in the database and update or create if not so I'm using updateOrCreate.

I have applied a chunk to the CSV for reading the data but didn't know if it's possible to add a chunk to the upload to database section?

Here is my function


    if($request->file('imported-file')) {

        $path = $request->file('imported-file')->getRealPath();

        $data = Excel::filter('chunk')->load($path)->chunk(200, function($results) {

            foreach($results as $row) {

                if(!empty($row['postcode'])) {

                    $url = "https://maps.googleapis.com/maps/api/geocode/xml?address=".urlencode($row['postcode'])."&region=uk&key=";
                    $tmp = file_get_contents($url);
                    $xml = simplexml_load_string($tmp);

                    if((string)$xml->status == 'OK' && isset($xml->result[0])) {

                        $lat = 0;
                        $lng = 0;

                        if(isset($xml->result[0]->geometry->location->lat)) {
                            $lat = (string)$xml->result[0]->geometry->location->lat;
                        }
                        if(isset($xml->result[0]->geometry->location->lng)) {
                            $lng = (string)$xml->result[0]->geometry->location->lng;
                        }

                    }

                    Import::updateOrCreate(
                        [
                            'sitecode' => $row['sitecode']
                        ],
                        [
                            'sitecode' => $row['sitecode'],
                            'sitename' => $row['sitename'],
                            'address_1' => $row['address_1'],
                            'address_2' => $row['address_2'],
                            'address_town' => $row['address_town'],
                            'address_postcode' => $row['postcode'],
                            'charity' => $row['charity'],
                            'latitude' => $lat,
                            'longitude' => $lng,
                            'approved' => 1
                        ]
                    );

                } else {

                    // Postcode not valid!!!

                }


            } // endforeach

            Session::flash('sucess', 'Import was sucessful.');
            return redirect()->route('locations');

        });

    } else {

        Session::flash('error', 'Please select a file to upload!');
        return back();

    }

}
0 likes
3 replies
martinbean's avatar

@craigb88 Try to be a bit more patient than a couple of hours before bumping topics. Especially when the code’s hardly legible due to formatting.

Processing a file can be a long task, so it’s best to upload the file and then dispatched a queued job to process the file. You can create a row in your database and show your user a “Processing” message, then mark the row as processed once the job’s finishing processing that file.

If it’s the actual file upload that’s timing out, then you might need to look at something like Amazon S3 for storing files and uploading it via a multi-part upload.

Snapey's avatar

Chunking just gets around any memory issues. It won't speed processing.

Make sure the sitecode column is indexed as this will make a big difference.

You don't say how many rows you are trying to import? If it is truly massive then your only option may be to break the data into jobs and queue them so that you can get back to the user and then process in the background.

Please or to participate in this conversation.