Open CSV Files in Excel Without Losing Leading Zeros
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.
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.
Import the CSV and set identifier columns to Text
Use Power Query to preserve leading zeros before Excel converts the values to numbers.
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.
In the import preview, choose Do not detect data types if that option is available, then select Transform Data.
In Power Query Editor, check Applied Steps for a Changed Type step. If present, select the X next to that step to remove it.
Select each identifier column, open its data type selector in the column header, and choose Text.
Select Home > Close & Load, or Close & Load To if you want to choose the destination.

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. Open the CSV: In WPS Spreadsheet, select Menu > Open and choose the CSV file to bring up the text import options.
- 2. Check the delimiter: In the import dialog, select the delimiter used by the file, such as Comma, and check the data preview.
- 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. Save the workbook: Select Menu > Save As and choose an Excel workbook (.xlsx) to retain the imported text values.

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.




