Bulk CSV imports with validation and duplicate detection for a 7M-record catalogue

Almost every business application ends up with a CSV import. Product catalogues, price lists, customer lists, parts databases: someone has the data in a spreadsheet and needs it in the system. The first version of an import is usually a loop that reads a row and inserts it. It works fine until someone uploads a file with 200,000 rows, a stray header and a few thousand duplicates.

On FilterCrossHub, a cross-reference search platform for heavy-machinery parts with more than 7 million parts records, I built bulk CSV import tools with data validation and duplicate detection. They let administrators upload large datasets efficiently and safely. This guide covers the principles behind an import like that, with examples in Laravel.

The goal: an import can never damage the catalogue

Before writing any code, agree on what "safe" means. For a catalogue that people rely on, I use four rules:

1. No half-imports. A file either goes in completely, or it doesn't change anything.
2. No silent duplicates. A row that already exists is recognised as a duplicate, even if it's formatted slightly differently.
3. No silent failures. Every row that's rejected is reported with a reason.
4. No blocking. A large import doesn't slow the site down for the people searching it.

Validate the whole file before writing anything

The simplest way to avoid half-imports is to split the work into two passes. The first pass only reads and checks: are the expected columns present, are required values filled in, are numbers actually numbers? The second pass writes, and only runs if the file is acceptable.

$errors = [];

foreach ($reader->rows() as $line => $row) {
    $validator = Validator::make($row, [
        'part_number'  => ['required', 'string', 'max:64'],
        'manufacturer' => ['required', 'string', 'max:120'],
        'category'     => ['required', Rule::in($categories)],
    ]);

    if ($validator->fails()) {
        $errors[] = ['line' => $line, 'messages' => $validator->errors()->all()];
    }
}

if ($errors) {
    return ImportResult::rejected($errors); // nothing has been written
}

Laravel's validator is ideal here because the rules read like a specification, and the same rules can be shared with the manual "add a part" form in the admin panel, so both paths enforce the same standard.

Normalise before you compare

Duplicates are rarely exact. In parts data the same number can appear as LF-9000, LF 9000 and lf9000. If you compare raw strings, all three become separate records, and your catalogue slowly fills with near-duplicates that confuse search results.

The fix is to build a normalised key for comparison: uppercase, with spaces and punctuation stripped. Store it alongside the original value, which you keep for display, and index it.

function normalisePartNumber(string $value): string
{
    return strtoupper(preg_replace('/[^A-Za-z0-9]/', '', $value));
}

// "LF-9000", "LF 9000" and "lf9000" all become "LF9000"

Detect duplicates in two places

Duplicates come from two directions, and you need to catch both:

• Inside the file. The same row repeated in the upload. Keep a set of the keys you've seen while reading; if a key appears twice, flag the second one.
• Against the database. Rows that already exist in the catalogue. Checking each row with its own query is far too slow for large files, so collect the keys in chunks and look them up together.

foreach (array_chunk($keys, 1000) as $chunk) {
    $existing = Part::whereIn('normalised_number', $chunk)
        ->pluck('normalised_number')
        ->all();

    $duplicates = array_merge($duplicates, $existing);
}

A unique index on the normalised key in MySQL is the safety net underneath all of this. Even if application code misses a duplicate, the database refuses it.

Write in batches, inside transactions

Inserting a large file one row at a time means one database round trip per row. Inserting in batches of a few hundred or a few thousand rows is dramatically faster. Wrapping the write pass in a transaction gives you the all-or-nothing behaviour: if anything fails halfway, the transaction rolls back and the catalogue is untouched.

DB::transaction(function () use ($rows) {
    foreach (array_chunk($rows, 1000) as $batch) {
        Part::insert($batch);
    }
});

For very large files, one giant transaction can hold locks for too long. Then the better pattern is to import into a staging table first, validate there, and move the clean rows into the live table in one step at the end.

Run big imports in the background

An HTTP request that takes several minutes will time out, and it ties up a web worker the whole time. Large imports belong in a queued job. The upload request stores the file and dispatches a job, and the admin sees progress in the panel.

ImportPartsCsv::dispatch($upload->path, auth()->id());

return back()->with('status', 'Import started. You can leave this page.');

This also protects the public site. Searches keep running at full speed while the import works through the file.

Tell the admin exactly what happened

The import is only as good as its report. At the end, show the administrator how many rows were added, how many were skipped as duplicates, and how many were rejected, with the line number and reason for each rejection. Offer the rejected rows as a downloadable file so they can be fixed and re-uploaded on their own.

It sounds like a small thing, but it's what makes the difference between an import people trust and one they avoid.

Let missed searches show you what's missing

Imports aren't the only way a catalogue grows. On FilterCrossHub, when a user searches for a part and nothing is found, the unresolved search is forwarded to the administration panel for manual review, approval and catalogue expansion. Real demand shows the gaps, and the data improves with every miss.

Keep search fast as the catalogue grows

All of this sits next to a search that has to stay instant. On FilterCrossHub, I designed the database architecture and optimised the search workflow so cross-reference results return in milliseconds across more than 7 million parts records. An import designed along these lines supports that kind of speed: normalised, indexed keys serve both duplicate detection and fast lookups, and background jobs keep heavy writes away from the search path.

Checklist

• Validate the whole file before writing anything
• Normalise values before comparing them
• Detect duplicates inside the file and against the database
• Back it up with a unique index
• Insert in batches, inside a transaction or through a staging table
• Run large imports as background jobs
• Report every added, skipped and rejected row

Need a data import or a large searchable catalogue built properly? Tell me about it.