Excel and CSV for mail personalisation: preserving leading zeros and identifiers

The source file contains recipient ID 001234. After someone opens the list in a spreadsheet, it becomes 1234. The printed document still looks professional, but the reference no longer matches the client’s case record. A long reference number or a code resembling a date can change too. The problem arises before printing, where data crosses between systems.

Outsourced correspondence needs agreement on how each field is read, as well as on the letter layout. The model below concerns preserving values during import and export. It complements the rules for naming print files: a correct filename does not protect the contents against conversion.

1. Identify text fields, even when they contain digits

Case references, customer identifiers and postal codes identify records; they are not quantities to add up. Specify them as text. Keep dates, amounts, country and address in separate columns. Agree one date notation, such as YYYY-MM-DD, and define an empty value. Do not apply one postal-code pattern to every country.

For each column, record its name, type, permitted length, example and validation rule. The example 001234 is a value, not an instruction to pad every number with zeros. Also agree whether an identifier must be unique or may appear on several documents for the same case.

2. Stop conversion before values are lost

Microsoft states that Excel’s numeric precision is limited to 15 significant digits. Store longer identifiers as text. Applying text formatting after digits or leading zeros have been lost does not recover the original value; return to the untouched export.

Automatic Data Conversions options are available in Microsoft 365 and Excel 2024, including the corresponding Mac editions. They can restrict selected automatic conversions. Check the installed version and settings; an option does not replace the column specification or a test of the file handed to the provider.

3. Import CSV deliberately instead of trusting the preview

In Excel, use From Text/CSV and continue to data transformation. Power Query’s Text/CSV connector can disable type detection and leave columns as text. Set the delimiter in the preview and check that the number and arrangement of columns match the specification.

Assign field types explicitly in Power Query. Locale matters when converting dates and numbers. Inspect the query steps: an earlier numeric conversion may already have damaged an identifier. Remove or correct that step near the source rather than converting its result to text afterwards.

Example field specification to agree

4. Check encoding and the actual exported file

Agree UTF-8 with the recipient of the data. Microsoft describes opening UTF-8 CSV normally when it has a BOM and using Power Query import otherwise. Character encoding and column type are separate settings: correct display of “Łódź” does not prove that leading zeros survived.

A text export does not carry the entire workbook; Microsoft notes that it saves the current worksheet. After export, inspect the result as text and import it using the agreed specification. A value containing a delimiter, quotation mark or line break needs correct handling by both exporter and importer. Viewing the worksheet before saving is insufficient.

5. Prepare a small set of difficult cases

Build a synthetic sample: 001234, an identifier longer than 15 digits, a code containing E, an ambiguous date and a name containing ą, Ł, ñ and ü. Add a field with a delimiter and a quotation mark. Do not use real recipient details for a general demonstration.

Run the sample through the real path: client export, provider import, intermediate save and document personalisation. Compare expected values character by character. Include the first and last record and a missing-field case. This is a proposed process test, not a claim that every system handles data identically.

6. Refer uncertain records for clarification

Flag a record if a digit is missing or a code has the wrong length. Do not automatically add zeros without an unambiguous rule and a confirmed source. Two different identifiers can look identical after conversion; counting rows alone will not reveal this.

Reconcile records received, rejected and released for processing, and provide errors with the row number and field name. Compare identifiers with the source and check duplicates under the rules of that process. If the file was damaged, request another export; changing its visual format does not repair the data.

7. Approve the rules alongside a personalised sample

The client confirms field meanings and source-data accuracy. Within the agreed scope, the provider checks import and the mapping of values into the document. Record the specification version, sample outcome and release approval. Repeat the test when an export or application changes, even if the letter template remains the same.

BackOffice Outsourcing offers data preparation and personalisation within serial and bulk correspondence and document printing and envelope inserting. Before commissioning a mailing, agree the file format and handling of discrepancies. Describe your data flow to the team to define checks appropriate to the engagement.

Sources and scope

• Microsoft Support — Keeping leading zeros and large numbers
• Microsoft Support — Data import and analysis options in Excel
• Microsoft Learn — Text/CSV connector
• Microsoft Learn — Data types in Power Query
• Microsoft Support — Opening CSV UTF-8 files correctly in Excel
• Microsoft Support — Import or export text files

Microsoft documentation checked on 16 October 2026. Command availability depends on the application version. The field specification, examples and handover test are editorial proposals for agreement between the parties, not a mandatory procedure or a guarantee of a particular system.