Data Import & Export

Open CSV Files in Excel Without Losing Leading Zeros

Chanuka GeekiyanageChanuka Geekiyanage Nov 14, 2026 869 views

Question details

Import a CSV file into Excel while preserving leading zeros in identifier columns.

Product
Microsoft Excel
Device & OS
not provided
Scenario
Opening a CSV containing numeric-looking identifiers such as 001234.
Observed behavior
Excel interprets the identifiers as numbers and removes their leading zeros.
Before you start

Check that the original CSV still contains the leading zeros by opening it in a text editor. Identify the columns that contain identifiers rather than values used for calculations.

Solution 1Recommended

Import the CSV and set identifier columns to Text

Use Power Query to preserve leading zeros before Excel converts the values to numbers.

1
Start the import

In a blank Excel workbook, select Data > Get Data > From File > From Text/CSV, then choose the CSV file. In some versions, select Data > From Text/CSV.

2
Open Power Query

In the import preview, choose Do not detect data types if that option is available, then select Transform Data.

3
Remove automatic type conversion

In Power Query Editor, check Applied Steps for a Changed Type step. If present, select the X next to that step to remove it.

4
Set identifier columns to Text

Select each identifier column, open its data type selector in the column header, and choose Text.

5
Load the data

Select Home > Close & Load, or Close & Load To if you want to choose the destination.

Import the CSV and set identifier columns to Text
CSV files do not store column types: Save the imported workbook as an Excel workbook (.xlsx) to retain the text column types for future use.
Preserve CSV identifiers

Import CSV identifiers as text in WPS Spreadsheet

Use the CSV import options in WPS Spreadsheet to set identifier columns to Text before loading the data, then save the result as a workbook.

  1. 1. Open the CSV: In WPS Spreadsheet, select Menu > Open and choose the CSV file to bring up the text import options.
  2. 2. Check the delimiter: In the import dialog, select the delimiter used by the file, such as Comma, and check the data preview.
  3. 3. Set columns to Text: Select each identifier column in the preview and choose Text as its column data format, then finish the import.
  4. 4. Save the workbook: Select Menu > Save As and choose an Excel workbook (.xlsx) to retain the imported text values.
Preserves leading zeros in identifier columns imported as textCompatible with Microsoft Excel XLSX filesProvides familiar spreadsheet tools for reviewing imported data
microsoft office alternative - wps office

Frequently Asked Questions

Why does Excel remove zeros when I open a CSV?

Excel may detect numeric-looking fields as numbers when you open a CSV directly. Use Data > From Text/CSV and set identifier columns to Text before loading them.

Can I recover leading zeros after Excel removes them?

If the original CSV still contains the zeros, import it again and set the affected columns to Text. If you know the required fixed width, a custom number format can display zeros, but it does not change the underlying value to text.

Will saving as CSV preserve the text column format?

No. CSV stores plain values, not spreadsheet column types. Save as .xlsx to retain the text format, or set the columns to Text again when importing the CSV.

How can a database export preserve leading zeros?

Store identifiers in a text type such as VARCHAR and export the text values to CSV. When importing the CSV into Excel, still set the identifier columns to Text.