Import CSV into Excel Without Losing Leading Zeros

Guides & Tutorials / Excel / Data import

To import a CSV into Excel without losing leading zeros, use the text/CSV import workflow and set identifier columns to Text before they are converted to numbers. Check long IDs, date-like codes, delimiters, and character encoding in the preview. Save the working result as an Excel workbook, then verify any CSV export separately.

Double-clicking a CSV can let Excel interpret its contents automatically. That is convenient for ordinary quantities, but an identifier such as 00123 is not necessarily the number 123. The same problem affects postal codes, product codes, and long references that must survive an exact match with another system.

01 / Decide which columns are identifiers

Before importing, distinguish labels from quantities. You calculate with a quantity; you match an identifier exactly. A value containing only digits can still belong in a Text column.

These are invented examples, not real account details. Ask the exporting system’s owner for column definitions when the meaning is unclear. Do not assume every code has the same length just because the first few rows do.

Microsoft’s guidance on leading zeros and large numbers explains that Excel numbers have a maximum precision of 15 significant digits. Identifiers requiring 16 or more digits should therefore be retained as text in the worksheet. A scientific-looking display and actual loss of precision are different issues; widening a column fixes neither a missing digit nor a changed value.

02 / Import through Text/CSV

The following workflow is for desktop Excel with the Power Query Text/CSV import experience. Labels and available connectors vary by edition and platform. Start in a blank workbook rather than opening the source by double-clicking it.

1. Keep an unchanged source copy. Use a separate working filename or folder.
2. Open the Data tab. Choose From Text/CSV, or the corresponding Get Data → From File → From Text/CSV route shown in your version.
3. Select the CSV. Review the preview before loading.
4. Check the delimiter and file origin/encoding. Headers should align with the right columns, and names should display correctly.
5. Disable automatic type detection if the preview offers that option. Start with Text columns, then assign numeric or date types deliberately where needed.
6. Choose Transform Data. Inspect the query and explicitly keep identifier columns as Text before loading the result.

Microsoft’s Text/CSV connector documentation describes the preview’s delimiter, encoding, type-detection, and Transform Data options. Turning off type detection lets columns default to Text. This is useful for a first inspection because it postpones decisions about what the characters mean.

Do not leave every column as Text indefinitely if you need calculations. Once the source is preserved, convert genuine quantities, amounts, and confirmed dates using rules appropriate to that column. Keep codes and identifiers as Text.

03 / Check Power Query conversion steps

A worksheet formatting change and a query data-type change happen at different stages. If the query already changed an ID to a number, adding a later Text conversion may merely turn that changed number into text.

Microsoft documents automatic type detection for unstructured sources and the resulting Changed Type step. Inspect Applied Steps in the editor. Select the step before conversion and compare a known identifier with the source.

If an automatically added Changed Type step is responsible, edit or remove that conversion before assigning the intended types. On a new import, this allows the original text values to flow through. Be aware that removing a step in an existing, more complex query may affect later steps; review the resulting preview and errors.

Use the data-type icon on the column header or the Change Type menu to choose Text for identifiers. Verify the exact characters, then use Close & Load when the query is ready. Microsoft’s import guidance describes setting selected columns to Text and replacing the current conversion where prompted.

For repeat imports, save the query in the workbook and document the expected columns. Reusing transformations can reduce repeated setup, but a new export format still needs review. An upstream change in column names or date conventions can invalidate an old assumption.

04 / Handle dates, separators, and encoding

Confirm the date convention before conversion

Hypothetical example: 04/05/2026 can mean April 5 under month/day/year or 4 May under day/month/year. A successful conversion is not proof that the interpretation is correct. Confirm the source convention rather than relying on your location or the recipient’s country.

Power Query’s locale guidance explains using Change Type → Using Locale to interpret a column under a specified convention. Retain a raw date-text column during checking if you need a direct comparison. Verify a few day, month, and year components explicitly before trusting the whole column.

Check the delimiter and quoted fields

If the preview puts an entire row into one column, check the delimiter first. If a description containing a comma splits into extra fields, check how the file quotes those values. Do not repair the worksheet by deleting separators until you understand the source structure.

Microsoft’s Text Import Wizard documentation explains delimiters and text qualifiers. The same inspection principle applies to the modern preview: content belonging inside one field should remain together.

Check characters as well as digits

Inspect names and addresses containing accented or non-English characters. The correct column count does not prove the encoding is right. Choose the encoding used by the exporter; if you cannot identify it, request that information or an agreed export format.

Check decimal and thousands separators for numeric columns too. A label resembling 1,234 can have different interpretations depending on the source convention. Keep an ambiguous value as text until its meaning is settled.

05 / Use conversion controls or the legacy wizard

Recent Excel editions include Automatic Data Conversions controls. Microsoft lists these for Excel for Microsoft 365 and Excel 2024, including the corresponding Mac editions. They can change how Excel handles leading zeros, long numeric strings, scientific-looking codes, and some date-like text.

Review Microsoft’s conversion-options documentation for the available controls in your edition. On Windows, relevant settings are under File → Options → Data. These settings do not replace inspection of Power Query’s Applied Steps, and the documented date-conversion option does not cover every string containing spaces or punctuation.

If your edition uses the legacy Text Import Wizard, choose the correct delimiter and text qualifier, then select each identifier column and set its column data format to Text before finishing. Set date order explicitly when importing confirmed dates. Microsoft documents enabling Text (Legacy) through the Data options where that feature is available.

Do not confuse the wizard with the Power Query editor: their controls differ. Use the workflow available in your version, preserving the same rule that identifier typing happens before numeric conversion.

06 / Verify before using the data

Use the unchanged CSV as the reference. Open a copy in a plain-text editor for small spot checks, or compare against the exporting system’s records. Avoid making Excel’s automatically interpreted display your only evidence of what the source contains.

Check more than the first row. Include the longest identifier, a leading-zero value, a blank, a code containing letters, and a value near the end of the file. For a large recurring feed, make those cases part of the agreed import checks.

Count records rather than simply counting lines in a text editor: quoted fields can contain line breaks. Review query errors and unexpected blanks. If you filter or remove rows, record the reason so missing data is distinguishable from an import failure.

Keep cleanup separate from preservation. Trimming spaces, removing punctuation, or padding codes to a fixed width may be correct for a documented specification, but those transformations change values. Preserve the original field alongside the cleaned output where comparison matters.

07 / Save the workbook and check exports

Save the working file as .xlsx so you retain the workbook structure and query setup. If another system requires CSV, export a separate copy using that system’s required column order, delimiter, encoding, and date representation.

Microsoft’s text-export documentation warns that text formats remove workbook formatting and that CSV saves the active sheet. A CSV is not a way to preserve Excel’s formatting instructions for the next person who double-clicks it.

Inspect the exported text and, where possible, import a sample into the receiving system. Confirm that identifiers still match exactly. Reopening by double-clicking in Excel can apply conversions again, so use the deliberate import workflow for your verification.

Send a brief field specification with recurring exports: which columns are identifiers, which are numeric, the date convention, and any meaningful blanks. If the file contains restricted information, use appropriate recipient permissions; our secure file-sharing guide covers that delivery step.

Frequently asked questions