Leading zeros lost when merging CSV files: how to keep IDs intact

What actually happens to the IDs

A CSV is plain text. 00742 sits in the file as five characters. When Excel opens the file by double-click, it guesses a type for each value, decides 00742 is a number and stores 742. Long numeric strings get a second treatment: Excel stores numbers to 15 significant digits, so a 16-digit card or order reference loses its last digit for good, and anything longer than 11 digits is shown in scientific notation in the General format.

The last two rows are the dangerous ones. If you save the merged file as CSV after this, the corrupted values are written back as text and the originals are lost. Keep the source files until the merge is checked.

Merge without opening the files

Concatenating files at the command line copies bytes, not values, so nothing is converted. On Windows the copy command or PowerShell does it; on a Mac or Linux, cat and tail do. Our guides to merging CSV files on Windows and on a Mac cover the header handling. The IDs only get damaged if you then double-click the result, so open the merged file with the import steps below.

Import with the ID columns as text

1. In a blank workbook choose Data > From Text/CSV and pick the file (or Data > Get Data > From File > From Folder for many files at once).
2. Click Transform Data rather than Load, so the Power Query editor opens.
3. Power Query adds a Changed Type step that turns IDs into whole numbers. Delete that step, or select the ID columns and set Data Type > Text, choosing Replace current when asked.
4. Close & Load. The IDs land as text with their zeros and full length.

To stop the guessing for every new query, set File > Options > Data (or Query Options > Data Load) so types are never detected for unstructured sources. Recent Microsoft 365 builds also have an Automatic Data Conversion section under File > Options > Data, where you can switch off removing leading zeros and converting long numbers to scientific notation when opening CSVs; availability depends on your version, so check before relying on it.

Changing the cell format to Text after the file is open does not bring zeros back. The conversion already happened; the format only changes how the damaged value is displayed.

Repairing IDs that are already damaged

If every ID has a fixed length, you can pad the zeros back with =TEXT(A2,"00000") or =RIGHT("00000"&A2,5). Numbers shown in scientific notation but shorter than 16 digits can be shown in full with a custom format of 0. IDs of 16 or more digits cannot be repaired from the sheet: go back to the source CSV.

=TEXT(A2, "00000")          742 -> "00742"
=RIGHT("00000000" & A2, 8)  742 -> "00000742"

Keeping IDs as text in code

pandas and DuckDB also infer types, so tell them not to. In pandas, read the ID column as a string; in DuckDB, read everything as VARCHAR and cast only the columns you need.

import glob
import pandas as pd

frames = [pd.read_csv(f, dtype={"customer_id": str}) for f in sorted(glob.glob("exports/*.csv"))]
pd.concat(frames, ignore_index=True).to_csv("merged.csv", index=False)

SELECT * FROM read_csv('exports/*.csv', all_varchar = true);

Analistable loads CSVs into DuckDB in your browser rather than opening them in Excel, so you can query across files without a save step that rewrites them; if a column of codes is detected as numeric, say so in your question and it will be cast to text. For very large inputs see merging large CSV files, and for the Excel-only route see combining CSV files in Excel. The merge CSV files guide compares every method.

A quick check after any merge

• Count characters: =MIN(LEN(A2:A5000)) and =MAX(LEN(A2:A5000)) should match the expected ID length.
• Count rows per source file before and after, so a merge that silently dropped rows is caught.
• Search for E+ in the ID column; any hit means a value was converted.
• Spot-check three IDs from the end of each source file against the merged output.

Which columns are at risk

Not every number-like column needs protecting, and treating them all as text makes sums and sorting harder later. The columns that break are the ones that look numeric but are really labels: anything you would never add up. Go through the header row of one source file and mark each column before you build the merge.

A useful rule: if a value can start with 0, is longer than 11 digits, or contains a letter E between digits, import it as text. Keep the list with the merge so the next person (or next month's you) applies the same choices. When the files come from different systems, check each one: a column that is always six digits in one export can be stored without padding in another, and the two will not match until both are padded to the same length.

The same discipline applies when you later join the merged file to another table. A lookup of text 000418 against number 418 fails, so make the key the same type on both sides before you look anything up.

Frequently asked questions

Why does Excel remove leading zeros from my CSV?

Because it opens CSVs by guessing types, and 00742 looks like a number. Import with Data > From Text/CSV and set the column to Text, or use the Automatic Data Conversion options in recent Microsoft 365 builds.

Can I get back the last digits of a long ID shown as 1.2E+15?

No. Excel keeps 15 significant digits, so anything beyond that was replaced with zeros. Re-import the original CSV with the column as Text.

Does merging with copy or cat damage IDs?

No. They join text files byte for byte. The damage happens only when the merged file is opened in Excel and saved again.

Will Google Sheets do the same thing?

Google Sheets also converts numeric-looking text on import unless you untick the option to convert text to numbers, dates and formulas, so check the import dialog.

Related guides

• How to combine CSV files in Excel
• How to merge large CSV files
• How to join a big CSV to a small lookup table
• How to join two CSV files by a common column
• How to merge CSV files on Windows