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
Please or to participate in this conversation.