How do I keep leading zeros in a CSV file?

Question

Sheet Reserve · Accepted Answer

The zeros are usually still in the CSV; Excel removes them when it converts the column to numbers on opening. Import the file with Data > From Text/CSV and set the column to Text, or format the column as Text before pasting. Opening the file by double-click and saving it again makes the loss permanent.

A CSV file is plain text, so a value such as 02134 is stored exactly as typed. The zero disappears when a spreadsheet program reads the file and decides the column is numeric. Excel converts 02134 to the number 2134. ZIP codes, product codes, phone numbers, account numbers and anything with a leading zero are affected.
Open the file in a text editor first. If the zeros are visible there, nothing is lost yet and you only need to import the file correctly.
How do I keep leading zeros in a CSV file in Excel for Microsoft 365?

Open a blank workbook and choose Data > From Text/CSV.
Select the file. In the preview, click Transform Data.
In Power Query, select the affected column, then choose Transform > Data Type > Text. If Power Query offers to replace the existing step, accept.
Click Close & Load.

You can also turn off automatic detection: in the preview window, set Data Type Detection to Do not detect data types, and every column loads as text.
The older wizard is still available. Choose File > Options > Data and enable From Text (Legacy) under Show legacy data import wizards. In step 3 of the wizard, click the column in the preview and choose Text as the column data format.
How do I keep leading zeros in a CSV file in Google Sheets?
Use File > Import > Upload and clear the checkbox Convert text to numbers, dates, and formulas. All values then arrive as text. For data you paste yourself, first select the target cells and choose Format > Number > Plain text.
How do I keep leading zeros in a CSV file in LibreOffice Calc?
File > Open shows the Text Import dialog. Click the column header in the preview at the bottom, set Column type to Text, and click OK. Repeat for every column that needs it.
How do I keep leading zeros in a CSV file in Numbers?
Numbers may convert digits to numbers on import. If zeros are lost, select the column and set Format > Cell > Data Format to Text, then restore the values with the TEXT approach below.
How do I restore leading zeros in a CSV file that were already lost?
Restoring them depends on the value having a fixed length. For five-digit ZIP codes:

Display fix: select the cells and choose Home > Number > More Number Formats > Custom, then enter 00000. The cell still holds the number 2134 and only looks like 02134.
Text fix: in a helper column, use =TEXT(A2,"00000"). The result is text 02134 and can be copied and pasted as values.

For example, if A2 holds 2134, =TEXT(A2,"00000") returns 02134. If A2 holds 12345, it returns 12345 unchanged.
What goes wrong when you keep leading zeros in a CSV?

Quotes do not help. A field written as "02134" is still converted by Excel.
The formula trick ="02134" keeps the zero in Excel, but writes formula syntax into the file, which breaks other programs. Avoid it for shared data.
Excel keeps 15 significant digits. A 16-digit card or account number loses precision once it is a number, so it must be imported as text.
Saving over the original CSV after Excel stripped the zeros overwrites the correct data. Use Save As and keep the original.
Scientific notation (1.23E+10) is the same problem and has the same fix.

If you are producing the CSV, there is no standard way to mark a column as text. The reliable approach is to deliver XLSX with the column formatted as Text, or to document the column types for the recipient. The guide to opening CSV in Excel without breaking data covers more cases.