Import a CSV into your CRM without losing zeros, accents or dates

Map fields, test a synthetic batch and reconcile saved values to catch silent changes before the real import.

Two people looking at a laptop on an office desk.

Article content

To import a CSV into your CRM without losing leading zeros, accents or dates, keep an untouched source file, define each column’s type and destination, and test a small synthetic batch. Then compare the values actually saved in the CRM. An “import completed” message is only one checkpoint; the resulting records need to match the intended data.

Consider a distributor moving customer accounts into a CRM. This method checks file integrity and the difference between creating and updating records. It is not a complete migration strategy, and it does not assume that your current software has a fault.

Signs that deserve a closer look

A customer code changes from 00123 to 123. The final digits of a long reference change. François appears with unexpected characters. A date is accepted but its day and month are reversed. Or a second attempt creates another copy of the same account.

Before choosing a fix, compare the original CSV as text, the file actually submitted and the records retained in the CRM. Read the import report too. This helps locate the change without automatically blaming Excel, the CRM or the original export.

Prepare access and the batch rules

You need a read-only original, a working copy, the CRM field definitions and appropriate import and export permissions. Arrange an authorised test environment or a test process agreed with the data owner. A small production batch still changes production.

Start with entirely fictional records. Check what an import can trigger, including task assignments, synchronisations and emails. Tests must not contact customers. For a real update, establish how to recover and restore the previous values. An export alone does not prove that every relationship and field can be restored.

File comparison does not require buying a connector. Import permissions, limits, custom fields and test environments may depend on the product and subscription. Check what your organisation already has. Allow time for reconciliation as well as uploading.

1. Define what each column means

Write a short mapping with the source column, destination field, type and acceptance rule. An illustrative example might use:

  • customer_id: a five-character source code stored as text; the expected value is 00123.
  • external_reference: a 17-digit reference, also stored as text; it is not used for arithmetic.
  • name: text preserving accents, such as François Noël.
  • start_date: a calendar date with an agreed meaning; 2026-05-04 means May 4, 2026.
  • optional_code: an optional field with a defined rule for empty values.

The five-character rule belongs to this example, not to every identifier. A string of digits can be a code rather than a number. For updates, also distinguish the source code from the destination record identifier. In HubSpot, for example, a Record ID identifies a HubSpot record; do not map an arbitrary customer number to it. Its precedence over other identifiers is explained in HubSpot’s import guide.

2. Read the file without losing the original values

Inspect the working copy in a tool that can read text and select an encoding. Confirm the delimiter, headers and a record containing accented characters. A comma can separate columns and also appear inside a quoted field. Count records with a CSV reader: a note with a line break may span several physical lines.

In Excel versions that provide the command, use Data → From Text/CSV and inspect the types before loading. Set codes and references to Text before any numeric conversion. If an automatic step has already converted them, return to the source with the correct types. Microsoft’s guidance on leading zeros and large numbers explains the 15-digit limit for numeric precision. Changing the display format after a loss cannot recover the original values.

Check encoding separately. Microsoft documents opening UTF-8 CSV files with a byte order mark, or importing through Power Query or the Text Import Wizard. Available commands vary by version and platform. A file that looks correct in one tool must still meet the destination importer’s requirements. Do not add a BOM without checking compatibility.

3. Resolve dates, blanks and duplicate keys

If the source contains 04/05/2026, do not guess whether it means April 5 or May 4. Confirm the format with the source system or its owner, then produce the value in a format accepted by the CRM. A calendar date does not need an invented time for this exercise. For a field that represents a timestamp, see our guide to imported dates and time zones.

For HubSpot, the import file documentation specifies UTF-8 for non-English characters and accepted date formats. This is a documented product example, not a rule for every CRM.

Decide whether a blank means missing, leave unchanged or clear the existing field. Do not substitute the word NULL without an explicit contract; it may become a literal value. Find repeated keys before submitting. Two records with the same name are not necessarily duplicates, while two rows using the same identifier need a decision before an update.

4. Test the intended import mode

Select create, update or a combination deliberately, according to the tool. Check every field mapping and the matching key. HubSpot’s single-object import workflow distinguishes these operations and requires a unique identifier to update records. Also confirm the date format selected in the import screen; it must match the file. Having a source ID column does not prove the CRM will use it for matching.

Your test batch should cover a leading zero, a long reference, an accent, a note containing a comma and quotes, a blank and a date with a known meaning. Include an existing record if updates are part of the planned operation. Record the expected numbers of creations, updates and rejections, rather than relying only on the total row count.

If the CRM reports errors, retain its report and connect each error to the affected row or key. Do not rerun the entire file until you know what was already created or changed.

5. Reconcile the result before continuing

Read the saved CRM records or export the tested fields. Compare them with your reference using a stable key, not just row position: the order may change. Inspect exact values, links to the intended account and deliberately empty fields. Separate expected transformations from unexplained differences.

A useful result sheet records the filename, settings, mappings, batch identifier, created/updated/rejected counts, field-level differences and decision. Continue only when differences are explained and the agreed recovery remains possible. Our user acceptance testing grid can help the team write these criteria.

What we tested locally—and what we did not

On October 26, 2026, we ran an isolated test with Python 3.9.6 and its standard csv module, using UTF-8, a comma delimiter and fields read as text. The Python documentation describes this behaviour and opening files with newline=''.

Four fictional records with seven fields were written and read back. All 28 values were identical, including 00123, a 17-digit reference, accents, a note with a comma, quotes and a line break, and empty fields.

A deliberately damaged second file contained seven differences. The comparison detected all seven. Three also violated the selected schema rules: code length, date format and identifier uniqueness. A wrong delimiter was rejected by the header check, while strict ASCII decoding rejected the accented UTF-8 text. All four scenarios produced the expected results.

This tested file reading, explicit rules and comparison. We did not run Excel or import the data into HubSpot or another CRM. The losses were deliberately injected, not observed in those products. The demonstration does not validate your CRM’s automations, blank-field behaviour or rollback.

A short checklist for the real batch

  • The original is preserved and the working copy is identified.
  • Encoding, delimiter and headers are confirmed.
  • Codes remain text; dates and blanks have explicit rules.
  • The create/update mode and matching key are confirmed.
  • Saved test records have been compared by key, with every difference explained.
  • Authorisation, automatic effects and recovery are agreed for the real batch.

For recurring transfers, our ERP and CRM integration guide covers synchronisation beyond the file. Hamdi Services also supports custom applications and integrations in Quebec and across Canada when these checks need to become part of daily operations.

Technical sources rechecked on November 7, 2026.

Explore more articles

Let’s discuss your project.

Tell us what you would like to improve. We’ll help you decide on the next steps.

Start the conversation