Keep Leading Zeros in Product IDs When Cleaning Supplier CSVs

What should an identifier round-trip preserve?

These are authored examples, not an export produced by a tested application. If the zeros were already lost before you received the file, return to an authoritative supplier record. Padding every value to an assumed length could create convincing but incorrect IDs.

These comparison rows are authored fixtures. Compare strings before and after import, including leading zeros and variant suffixes. If the source already lacks the zeros, ask for the authoritative identifier rather than applying a padding rule. A successful numeric sum does not prove that the identifiers survived.

Check the completed record

Protect IDs through the first round trip

1. List identifier columns and give each a text-preservation rule. Confirm whether case, hyphens, spaces, or a fixed length are meaningful.
2. Preview import parsing and prevent number/date interpretation for identifier fields where the software permits it.
3. Inspect representative IDs: a leading-zero code, a long digit string, an alphanumeric code, and a code containing punctuation.
4. Perform the cleanup on descriptive columns first. Keep an original-ID field if you need a separate normalized matching key.
5. Export to a new file and inspect it with a text-aware viewer or import preview. A spreadsheet's display formatting can hide an already altered value.
6. Compare row counts and identifiers with the source before merging the result into another system.

Decide which columns identify a record

An identifier tells you which item a row refers to; a quantity or price tells you something about that item. Write that distinction into a small field contract before importing. Product IDs, supplier codes, postal codes, and account references can look numeric while needing their original characters.

Keep the incoming file unchanged. Work on a copy and record the application/import settings used. OpenRefine documents cell-level data types and numeric parsing behavior; inspect the actual values and settings in your chosen tool rather than assuming a whole column was preserved because it looks right on screen.

A clean identifier can still be the wrong merge key. Two suppliers may both use 00127 for different products. Use a supplier-plus-product key when the source system defines identity that way, and review duplicate groups before accepting updates.

If a downstream system requires a numeric field, keep the authoritative text identifier separately and document the conversion. Get the receiving system's actual requirements before deleting information to satisfy an import error.

• Prepare a local cleanup project with inspectable imports
• Review duplicate keys before merging records
• Add identifier preservation to a reusable import check

Ask AI to flag changes rather than repair identities

Review this identifier comparison.
IDENTIFIER CONTRACT: [meaning, allowed characters, length rules]
ORIGINAL ROWS: [source ID + identifier]
WORKING ROWS: [same source ID + imported identifier]

List every changed, missing, duplicated, or unexpectedly typed identifier. Preserve the original characters in the report. Propose review questions rather than guessing missing zeros or changing IDs. Keep a normalized matching key separate from the source identifier. Return an exception table with source row, original, working value, and reason.

Keep a small regression sample beside the export

Save a few permitted sample rows that previously exposed ID damage. Run them through the next import before processing the whole file. The handoff should contain the checked output, the source copy, and the list of unresolved identities.

• Continue with supplier spreadsheet cleanup