How to Import CSV to 'Update' existing record in DB using Laravel

Question

I have new challenge in Laravel. My client want a situation whereby, they can do mass update by uploading CSV to DB Laravel will cross-check if "incidence ID" already exist, it will ONLY update the columns.

i.e I have a table that have like 20 columns. one of the columns is named "Incidence ID". If the CSV is been imported to DB and some of the CSV has a incidence ID that is the same with the one already in the DB, the script should just UPDATE the record in other 19 columns. I been searching for over two day but can find any solution yet.

This is my CONTROLLER Script that can ONLY Insert CSV to DB

 public function uploadFile(Request $request){
    if ($request->input('submit') != null ){
        $file = $request->file('file');
        // File Details
        $filename = $file->getClientOriginalName();
        $extension = $file->getClientOriginalExtension();
        $tempPath = $file->getRealPath();
        $fileSize = $file->getSize();
        $mimeType = $file->getMimeType();
        // Valid File Extensions
        $valid_extension = array("csv");
        // 2MB in Bytes
        $maxFileSize = 2097152;
        // Check file extension
        if(in_array(strtolower($extension),$valid_extension)){
            // Check file size
            if($fileSize <= $maxFileSize){
                // File upload location
              ////  $location = 'uploads';
                // File upload location
                $location = '../uploads';

                // Upload file
                $file->move($location,$filename);

                // Import CSV to Database
                $filepath = public_path($filename);

                // Reading file
                $file = fopen($filepath,"r");

                $importData_arr = array();
                $i = 0;

                while (($filedata = fgetcsv($file, 1000, ",")) !== FALSE) {
                    $num = count($filedata );

                    // Skip first row (Remove below comment if you want to skip the first row)
                    /*if($i == 0){
                       $i++;
                       continue;
                    }*/
                    for ($c=0; $c < $num; $c++) {
                        $importData_arr[$i][] = $filedata [$c];
                    }
                    $i++;
                }
                fclose($file);

                // Insert to MySQL database
                foreach($importData_arr as $importData){

                    $insertData = array(
                        "incidence_id"=>$importData[1],
                        "serial_no"=>$importData[2],
                        "name"=>$importData[3],
                        "activation_date"=>$importData[4],
                        "sol_id"=>$importData[5],
                        "address"=>$importData[6],
                        "brand"=>$importData[7],
                        "model"=>$importData[8],
                        "state"=>$importData[9],
                        "region"=>$importData[10],
                        "vendor_name"=>$importData[11],
                        "vendor_id"=>$importData[12],
                        "custodian_email"=>$importData[13],
                        "custodian_phone"=>$importData[14],
                        "asset_tag_no"=>$importData[15],
                        "atm_code"=>$importData[16],
                        "sla_level"=>$importData[17],
                        "sla_hour"=>$importData[18],
                        "timers"=>$importData[19],
                        "status"=>$importData[20],
                        "warranty"=>$importData[21]);
                    //   "user_id"=>$importData[11]);
                    BanData::insertData($insertData);
                }
                Session::flash('message','Import Successful.');
            }else{
                Session::flash('message','File too large. File must be less than 2MB.');
            }
        }else{
            Session::flash('message','Invalid File Extension.');
        }
    }

How can I make it to function like 'updating record' instead of "inserting" to DB?

Answer 1

You can use laravel's updateOrCreate function - https://laravel.com/docs/6.x/eloquent#other-creation-methods

$item = BanData::updateOrCreate(
    ['incidence_id' => $importData[1]],
    [
        "serial_no" => $importData[2],
        "name" => $importData[3],
        "activation_date" => $importData[4],
        "sol_id" => $importData[5],
        "address" => $importData[6],
        "brand" => $importData[7],
        "model" => $importData[8],
        "state" => $importData[9],
        "region" => $importData[10],
        "vendor_name" => $importData[11],
        "vendor_id" => $importData[12],
        "custodian_email" => $importData[13],
        "custodian_phone" => $importData[14],
        "asset_tag_no" => $importData[15],
        "atm_code" => $importData[16],
        "sla_level" => $importData[17],
        "sla_hour" => $importData[18],
        "timers" => $importData[19],
        "status" => $importData[20],
        "warranty" => $importData[21]
    ]
);