Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.

wing5wong's avatar

Compare csv to DB - return added/removed/modified

I have a CSV of info which is updated daily. It has approx 2k rows

what id like to do is be able to read the CSV, and compare it to the database. Any new records should be added to the DB, and records NOT in the csv should be removed from the DB and any modifications to existing records should be updated

my ideal output is 3 arrays of each variation.

$newStudents = [];
$removedStudents = [];
$modifiedStudents = [];

as a start i have the following which gives me the added/removed students. Im not sure its possible with this approach to find the modified rows in the csv (e.g. changed preferred name, or changed course.)

$students = Student::all();

        $csvData = $this->loadCSV();

        // create new students and update modified ones
        $studentsFromCsv = [];
        foreach($csvData as $student) {
            $studentsFromCsv[] = Student::updateOrCreate([
                'student_id' => $student["ID Number"]
                ],
                [
                'first_name' => $student['First Name  (Preferred)'],
                'last_name' => $student['Surname  (Preferred)']
                ]);
        }

        // convert array to allow diff
        $studentsFromCsv = new \Illuminate\Support\Collection($studentsFromCsv);

        // students that exist in CSV but not in DB
        $studentsAddedToCSV= $studentsFromCsv->diff($students);

        // students that exist in DB but not CSV
        $studentsRemovedFromCSV = $students->diff($studentsFromCsv);

        // delete removed students
        foreach($studentsRemovedFromCSV as $r) {
            $r->delete();
        }

        return view('welcome', ['students'=>$studentsFromCsv, 'added'=>$studentsAddedToCSV, 'removed'=>$studentsRemovedFromCSV]);
}

Is there a more efficent way to solve this? I would like to be able to produce a daily report using this info

0 likes
0 replies

Please or to participate in this conversation.