Protect Leading Zeros in CRM Imports
Test identifier text values through spreadsheet and CRM round trips, catch lost zeros and rounding, and quarantine values that cannot be reconstructed.
10/23/20263 min read
A business identifier can look numeric without being a number. The fictional branch code 00384 names a particular record; it does not describe a quantity of 384. If a spreadsheet converts it before an import, the CRM may receive a different key, create an unwanted record, or fail to associate the original branch. Formatting the cell to display five digits afterward can conceal rather than repair that loss.
Protect the value at every conversion boundary: source export, spreadsheet loading, spreadsheet saving, destination import, and destination export. A round-trip test checks all of them. It is more useful than inspecting whether the first spreadsheet looks tidy.
Make the identifier contract explicit
For each identifier, record the issuing system, entity type, permitted characters, treatment of spaces, and whether length is fixed. Say explicitly that the value is text and must not be calculated. Do not assume all identifiers from the same country have identical lengths. A branch code, postal code, tax identifier, and internal account key can follow different rules.
Use a separate human-readable display column if staff want padded presentation. Keep the actual joining key unchanged. A rule that adds zeros until every value reaches eight characters is safe only when the issuing specification establishes that length. Otherwise it can manufacture a plausible but false identifier.
HubSpot's import documentation describes supported unique identifiers and property formats. Confirm that the destination field and selected import route preserve your identifier; a spreadsheet's text setting cannot override a numeric destination property.
Build a small challenge fixture
This fictional fixture uses internal codes, not real registry numbers. Add a stable test-row reference that is unrelated to the identifier being tested.
Z01: source value 00384; expected destination value 00384; expected length five.
Z02: source value 00000; expected destination value 00000; validity in production remains a separate question.
Z03: source value 120045678901234567; expected exact eighteen-character value, with no rounding or scientific notation.
Z04: source value AB007; expected letters and zeros unchanged.
Z05: source value blank; expected quarantine because this fictional workflow requires an identifier.
Z06: source value 384; expected 384, not an automatic conversion to 00384.
The last pair matters. If 00384 and 384 are both legitimate source values, stripping or adding zeros collapses their distinction. A test consisting only of padded codes would miss that collision.
Walk the fixture through the real route
Load the fixture through an import process that allows the identifier column to be interpreted as text before conversion. Merely changing the column type after automatic interpretation may be too late. Inspect the underlying value in a safe text view as well as the displayed value, then save a controlled export.
Use a CSV parser to compare exact strings, keyed by Z01 through Z06. Quotation marks in a CSV delimit fields; they do not provide a universal instruction to every spreadsheet to treat the contents as text. Check the export rather than relying on how the program displayed its cells.
Import the five valid rows into an isolated test area with unrelated workflows controlled by an authorized administrator. Export those records again using the intended downstream route. Compare the original, intermediate, and final values character for character. Record the tool, version or configuration, date, and the first boundary where any change occurred.
A worked decision, including a failure
Suppose the illustrative test preserves Z01 in the CRM but returns 384 in the final spreadsheet export. The CRM import is not the failed boundary; the downstream opening process is. Do not change the production identifier or mark the entire pipeline accepted. Adjust that export-consumption step and rerun the fixture.
If Z03 already differs in the initial spreadsheet, return to the authorized original. Digits lost through rounding cannot be reconstructed reliably from the rounded value. Ask the source owner for a fresh export rather than inventing the missing suffix.
The acceptance record should name every tested boundary, list exact comparisons, and retain unresolved rows outside production. Release only the route that passed. A different spreadsheet application, destination property type, or export setting creates a new test obligation, even when the file extension remains unchanged.
If you are also choosing research access, ask about Leads Automation’s independent Sales Navigator offer at US$15/month on WhatsApp. Confirm the available plan, eligibility and terms before ordering; this is not LinkedIn’s official price or an assurance that any particular integration is included.