Testing Spreadsheet Asset IDs Before a Software Import
Test asset identifiers for leading-zero loss, duplicate keys and altered formats before migration, then prove that imported records preserve their links.
How can a team prevent spreadsheet asset IDs from changing during migration?
Treat asset identifiers as text, profile their actual formats, and test the entire export-to-import path using representative edge cases. Compare identifiers exactly before and after import, including leading zeros and punctuation. Keep a mapping for approved corrections and reject accidental changes rather than allowing the destination system to generate silent substitutes.
A tag reading 000184 can become 184 when a spreadsheet interprets it as a number. Long numeric identifiers can also be altered during conversion. If the change reaches a new asset system, photographs, maintenance records and printed labels may no longer connect. A focused identifier test catches this problem before a full migration makes it expensive.
Profile the identifier column without normalising it
Preserve a copy of the source file and list identifier lengths, prefixes, blanks and repeated values. Look for scientific notation, unexpected decimals and inconsistent spaces. Keep the raw value alongside any proposed cleaned value. What appears to be harmless formatting may distinguish two legitimate identifiers, especially where several legacy registers have been combined.
Choose test records that represent the difficult cases: leading zeros, long strings, letters, hyphens and identifiers that differ by one character. Include a blank and a confirmed duplicate to test rejection behaviour. The purpose is not to make the sample look clean; it is to discover exactly how the import handles records it cannot safely accept.
Microsoft's guidance explains why Excel can remove leading zeros and lose precision in long numeric values. Formatting an already damaged value as text cannot restore missing digits. Recover the identifier from an intact original export, the physical label or other reliable evidence, then import that recovered value as text.
Test every conversion boundary
Run the sample through the same export format, transfer process and import configuration intended for the full migration. Opening a file in another spreadsheet application can change interpretation even if the first export was correct. Inspect the resulting raw identifier values, not only their visual appearance in a formatted cell.
Where a legacy identifier genuinely needs correction, create an explicit old-to-new mapping with a reason and reviewer. Test whether dependent attachments and transaction references follow that mapping. Do not use automatic trimming, punctuation removal or case conversion across the entire population until the team has established that those transformations cannot create collisions.
Prove identity after import
Export the imported sample and compare each identifier character for character with the approved input. Check that rejected rows appear in a usable error report and were not silently skipped. Open linked photographs and movement histories for several edge cases. A matching record count alone will not reveal identifiers that changed while the number of rows stayed constant.
Define a release condition for the full load: no unexplained identifier changes, no unresolved key collisions and a reconciled rejection list. Keep the mapping and comparison results with the migration evidence. If a late source update introduces a new identifier format, rerun the relevant test before accepting the final file.
Practical Example
Illustrative example: a municipality tests identifiers 000184 and 184, which belong to different items. The first import treats both as numeric and rejects one as a duplicate. The team changes the identifier mapping to text, reloads the sample and confirms that both printed tags resolve to the correct records and their existing photographs.
Action Checklist
- 1.Preserve raw identifiers and identify blanks, duplicate values and unusual formats.
- 2.Include leading-zero and long-string records in a representative migration trial.
- 3.Compare approved input with a fresh export from the destination system.
- 4.Test attachment and movement links for identifiers that required an approved mapping.
- 5.Block the full load while any unexplained identity changes or key collisions remain.
Establish a migration baseline that preserves asset identities through Fixed Asset Register Reconciliation.