vesunar's avatar

Importing from CSV and checking for updates

Hey, so I just got my csv import working and works fine so far except if i modify a phone number. This is a phone book kinda project. So I'd like to check if the name already exists but the number is different to update only the number. This is what I've got so far

public function csvImportStore(Request $request)
    {
        //Get the file
        $upload = $request->file('import-file');
        $filePath = $upload->getRealPath();

        //Read the file
        $file=fopen($filePath, 'r');

        $header = fgetcsv($file);
                
        //Loop through the columns
        while ($columns = fgetcsv($file)) 
        {
            $data = array_combine($header, $columns);

            //Table Update
            $name = $data['Name'];
            $number = $data['Number'];

            $contact = Contacts::firstorNew(['Name'=>$name, 'Number'=>$number]);

            if ($contact->name == $name && $contact->number != $number) 
            {
                Contacts::where($contact->name, '=', $name)
                        ->update([$contact->number => $number]);
            }

            else
            {
                $contact->owner_id = auth()->id();
                $contact->name = $name;
                $contact->number = $number;
            }
            
            $contact->save();
         }

         return redirect(route('contacts.index'))->withSuccess('Imported Contacts Successfully');
    }
0 likes
3 replies
lostdreamer_nl's avatar
lostdreamer_nl
7 years ago
Best Answer
Level 53

Right now, if you are uploading a 1000 line csv, it will take 1000 queries, just to check if it needs to update anything. Anything needing updates will do more queries.

I suggest loading the list in memory before importing (unless the csv is > 10.000.000 lines)


    public function csvImportStore(Request $request)
    {
        //Get the file
        $upload = $request->file('import-file');
        $filePath = $upload->getRealPath();

        //Read the file
        $file=fopen($filePath, 'r');

        $header = fgetcsv($file);
        $contacts = Contacts::select('Name', 'Number')->get()->pluck('Number', 'Name')->toArray();

        //Loop through the columns
        while ($columns = fgetcsv($file))
        {
            $data = array_combine($header, $columns);

            //Table Update
            $name = $data['Name'];
            $number = $data['Number'];

            if(isset($contacts[$name]) && $contacts[$name] != $number) {
                // update
                Contacts::where('name', '=', $name)
                    ->update(['number' => $number]);
            }
            if(!isset($contacts[$name])) {
                // new contact
                $contact = new Contacts(['Name'=>$name, 'Number'=>$number]);
                $contact->owner_id = auth()->id();
                $contact->save();
            }
        }
        return redirect(route('contacts.index'))->withSuccess('Imported Contacts Successfully');
    }

This will do 1 query to get the list of names / phoneNumbers, and 1 query for each update / insert.

1 last note: Models should be named in singular (Contacts --> Contact) and attributes should be in lowercase, Name / Number => name / number)

In both import scripts it is not handling updating a contact name ;)

vesunar's avatar

@LOSTDREAMER_NL - Thank you for the feedback/help and sorry for the wrong naming conventions I'm learning them as I go. Thanks again.

Please or to participate in this conversation.