Excel CSV imports that mangle zeros and dates: 12 traps tested

Open a CSV in Excel and it decides what each field is. ZIP code 02134 becomes the number 2134, an 18-digit order ID loses its last three digits, and a fraction like 1/2 can turn into a date. Microsoft’s own support page says Excel removes leading zeros and rounds anything past the 15th digit. Once it has done that, formatting cannot bring the data back.

This guide shows why it happens, what each fix does, and when each one fails. We built a test CSV with 12 trap columns and ran it through LibreOffice’s importer, pandas and a short Python script that writes an Excel file with nothing guessed. We did not have Excel in our test environment, so the Excel steps come from Microsoft’s documentation and are marked as such. Sources were read on October 21, 2026.

A CSV carries no types, so every program guesses

The CSV format’s reference document, RFC 4180, says there is “no formal specification” and describes only records, commas and quotes. Nothing in the file can say “this column is text”. So the program that opens it has to guess from what each value looks like.

Excel’s guess runs at the moment data comes in. Microsoft’s automatic data conversion page lists when: opening a .csv or .txt file, typing, and pasting from outside sources. It names four conversions:

00123 becomes the number 123 (leading zeros removed).
12345678901234567890 becomes 12345678901234500000 or 1.23457E+19, because Excel keeps 15 digits of precision.
123E5 becomes 1.23E+07, read as scientific notation.
JAN1 becomes the date January 1.

The damage happens on the way in. Microsoft says a custom number format such as 00000 “will not restore leading zeros that were removed prior to formatting”. The same holds for digits past the 15th: they are gone from the cell, not hidden.

The guessing has corrupted published science

This is not a rare edge case. In 2016, Mark Ziemann, Yotam Eren and Assam El-Osta screened supplementary files from 18 genomics journals published from 2005 to 2015. They confirmed gene name errors in 987 files from 704 papers: 19.6% of the papers with Excel gene lists. Gene symbols such as SEPT2 and MARCH1 had become 2-Sep and 1-Mar, and a RIKEN identifier, 2310009E13, had become 2.31E+13.

The fix that stuck was renaming the genes. The HUGO Gene Nomenclature Committee’s 2020 guidelines in Nature Genetics report that “all symbols that auto-converted to dates in Microsoft Excel have been changed”. SEPT1 is now SEPTIN1, and MARCH1 is now MARCHF1. Your part numbers and customer IDs will not get renamed, so the fix has to happen at import.

Our test: 12 trap columns, seven ways to open them

We wrote a two-row CSV with 12 columns, each holding a value type that spreadsheets are known to change. Every value in it is made up.

zip,employee_id,order_id,gene,gene_2,promo_code,portion,part_no,accession,product_code,phone,ship_date
02134,00123,123456789012345678,SEPT2,MARCH1,JAN1,1/2,3-4,2310009E13,1E5,+46701234567,03/04/2026
07030,00456,987654321098765432,SEPT9,MARCH5,DEC25,3/4,10-12,4930430E12,2E10,+14155550100,12/05/2026

We opened it seven ways on Linux on October 21, 2026, and counted how many of the 24 data cells came back different from the file. The LibreOffice runs used version 24.2.7.2, converting to .xlsx from the command line, with each result read back with openpyxl 3.1.5. Here is what changed.

Route	Cells changed (of 24)	What went wrong
LibreOffice, default import	12	Zeros dropped, order IDs cut to 15 digits, `2310009E13` and `1E5` read as numbers, `+` dropped from phones
LibreOffice, “detect special numbers” on, English (US)	16	All of the above, plus `1/2` read as January 2 and `03/04/2026` as March 4
LibreOffice, “detect special numbers” on, English (UK)	18	All of the above, but `1/2` read as February 1, `03/04/2026` as April 3, and `3-4` as April 3
LibreOffice, every column set to Text	0	Nothing
pandas 3.0.5, `read_csv` defaults	10	Zeros dropped, `+` dropped, accession numbers and `1E5` read as floats
pandas, `dtype=str, keep_default_na=False`	0	Nothing
Our script (below)	0	Nothing, and a LibreOffice re-export matched the source byte for byte

Guess before you read on: which value was the most dangerous? Not the dropped zeros, which are visible. The fraction column was worse.

The same file, imported with the same program, gave 1/2 as January 2 under US settings and February 1 under UK settings. Both looked like valid dates, and both took the current year (2026), which was never in the file.

Two smaller findings. First, LibreOffice stored the order ID as 1.23456789012346E+017 in the saved file, so the digits after the 15th were gone from the workbook itself, not just from the display.

Second, LibreOffice left SEPT2, MARCH1 and JAN1 alone in every setting we tried, while Microsoft’s page says Excel turns JAN1 into a date. Results from one program do not predict another. LibreOffice’s import documentation confirms the locale effect: with “Detect special numbers” on, “the selected language influences how such special numbers are detected”.

Fix 1: set every column to Text before the data comes in

The fix that worked in every route we tried is the same idea in each program: tell the importer the type of each column before it reads a single value. Text is the safe default, because text can always become a number later, and a number cannot become the original text again.

Excel, Power Query (from Microsoft’s documentation; we did not run Excel). Microsoft’s steps are:

Choose Data, then From Text/CSV, and pick the file.
In the preview, open the data type detection list. Microsoft’s Power Query documentation says it can be based on the first 200 rows or the entire data set, or turned off so every column defaults to Text. Turn it off.
Choose Transform Data. Select the columns that are real numbers or dates and change their type on Home, Transform, Data Type. For text columns Microsoft’s path is the same menu with Text, then Replace Current in the Change Column Type dialog.
Choose Close & Load.

Two settings on the same preview screen matter for this too. File Origin sets the character set, and the documentation says UTF-8 is only recognized automatically when the file starts with a UTF-8 byte-order mark. Delimiter offers comma, semicolon, tab and others, for files that do not use commas.

The trade-off: a query is a saved recipe, so refreshing it next month applies the same types again. That is good for a monthly export with a fixed layout and annoying for a one-off file. And if a new column appears later, check its type by hand; the recipe was built for the old layout.

Excel, the old wizard (from Microsoft’s documentation, untested). Microsoft’s import page says that opening a CSV directly uses “the current default data format settings”. Renaming the file from .csv to .txt before opening it starts the Import Text Wizard instead.

The wizard’s page also gives a menu route. Turn on File, Options, Data, Show legacy data import wizards, From Text (Legacy), then choose Data, Get Data, Legacy Wizards, From Text (Legacy). In step 3, select each column in the preview and set Column data format to Text. It is quicker than Power Query for a single file and saves nothing for next time.

LibreOffice and pandas take the same Text setting

LibreOffice Calc. In the Text Import dialog, select the columns in the preview and set Column type to Text. On the command line, the CSV filter options take column/format pairs in the fifth token, where format code 2 means Text. This is the string we ran for our 12 columns:

soffice --headless --convert-to xlsx \
  --infilter="CSV:44,34,76,1,1/2/2/2/3/2/4/2/5/2/6/2/7/2/8/2/9/2/10/2/11/2/12/2,1033,false,false,,,,,,,false" \
  traps.csv

The first four tokens are comma (44), double quote (34), UTF-8 (76) and start at line 1. The token after the pairs, 1033, is the language: English (US).

The empty tokens that follow leave formula evaluation off, and the final false turns off scientific-notation detection. Our locale runs left the column types at Standard and set token 8, “Detect special numbers”, to true. Then switching 1033 to 2057 (English, UK) moved our dates from the second table row to the third.

pandas. pd.read_csv("file.csv", dtype=str, keep_default_na=False) read all 24 cells exactly in our test. keep_default_na=False matters as well: by default pandas reads “N/A”, “NULL” and other common markers as missing. In a separate check, dtype=str alone turned the country code NA (Namibia) and the text null into empty values; with keep_default_na=False both survived.

Fix 2: switch off Excel’s four conversions (Excel 365 and 2024 only)

Microsoft added settings for each of the four conversions. The page lists Excel for Microsoft 365 and Excel 2024, on Windows and Mac:

Windows: File, Options, the Data tab, Automatic Data Conversion.
Mac: Excel, Preferences, the Edit tab, Automatic Data Conversion.

Under it are four options. Clear the ones you do not want:

“Remove leading zeros and convert to number”
“Keep the first 15 digits of long numbers and display in scientific notation if required”
“Convert digits surrounding the letter “E” into a number in scientific notation”
“Convert “date-like” combinations of continuous letters and numbers into a date”

Know the limits before you rely on it. The date option covers continuous letters and numbers like JAN1. Microsoft notes that JAN 1 or JAN-1 may still be read as dates, and the page does not say whether values like 1/2 or 3-4 are covered. We could not test it without Excel, so assume they are not.

The settings live in each person’s copy of Excel, so a colleague who opens the same file sees the old behavior. Microsoft also says the options do not apply to Power Query, which has its own types. Older versions, such as Excel 2021 or 2019, are not on the page’s list.

Our view: switch them off on your own machine, and still use Fix 1 for any file that matters. A setting that differs per person is not a process.

Fix 3: for one value at a time, type it as text

When you type into a cell rather than import, Microsoft’s page on stopping numbers changing to dates gives four ways:

Format the cells as Text before you type.
Type an apostrophe first, as in '1/47. The apostrophe is not shown.
Type a space first. The space stays in the cell.
Type 0 1/2 for the fraction one half.

These fit a handful of entries, not a 5,000-row export. The leading space in particular is a trap of its own: the cell now holds “ 1/2”, which will not match “1/2” in a lookup.

Fix 4: write the Excel file yourself, with types you chose

If a CSV arrives every week from the same system, the most reliable route is to skip the import step entirely. A script reads the file as plain text and writes an .xlsx in which every column is text unless you named it as a number or a date. The receiver opens a workbook, and no program has anything left to guess.

The script below does that with Python and openpyxl. It also formats text columns as Text (@), so values someone types into them later stay text too. Then it reads the saved file back and stops if any text cell differs from its CSV field. It writes to a temporary file first, so a failed run leaves no half-finished workbook.

One trap we found while building it: openpyxl stores any string that starts with = as a formula. In our edge-case file, a field =1+2 saved by a plain append came back from LibreOffice as 3. The script forces those cells back to text, which also stops a CSV field from running as a formula in the receiver’s spreadsheet.

"""Turn a CSV into an .xlsx where nothing is guessed.

Every column is stored as text unless you name it:
  --number amount,qty        stored as numbers (a value that is not a plain number stops the run)
  --date ship_date=%m/%d/%Y  stored as real dates, read with the format you give

Usage: python csv_to_xlsx.py input.csv output.xlsx [--number ...] [--date ...]
"""
import argparse, csv, os, sys, tempfile
from datetime import datetime
from openpyxl import Workbook, load_workbook

p = argparse.ArgumentParser()
p.add_argument("src")
p.add_argument("dst")
p.add_argument("--number", default="", help="comma-separated column names")
p.add_argument("--date", action="append", default=[], help="column=strptime format")
p.add_argument("--delimiter", default=",")
a = p.parse_args()

# utf-8-sig drops a byte-order mark at the start of the file, if there is one
with open(a.src, newline="", encoding="utf-8-sig") as f:
    rows = list(csv.reader(f, delimiter=a.delimiter))
if not rows:
    sys.exit(f"{a.src} is empty, nothing written")

header = rows[0]
numbers = {c for c in a.number.split(",") if c}
dates = dict(d.split("=", 1) for d in a.date)
missing = (numbers | dates.keys()) - set(header)
if missing:
    sys.exit(f"no such column(s): {', '.join(sorted(missing))}")

def convert(col, value, line):
    if value == "":
        return None                      # an empty cell stays empty
    try:
        if col in numbers:
            if "_" in value:             # Python reads 1_000 as 1000; a CSV should not
                raise ValueError
            return float(value) if any(ch in value for ch in ".eE") else int(value)
        if col in dates:
            return datetime.strptime(value, dates[col]).date()
    except ValueError:
        sys.exit(f"line {line}, column {col}: {value!r} is not a {'number' if col in numbers else dates[col] + ' date'}")
    return value                         # everything else: text, exactly as in the file

wb = Workbook()
ws = wb.active
ws.append(header)
for line, row in enumerate(rows[1:], start=2):
    if len(row) != len(header):
        sys.exit(f"line {line} has {len(row)} fields, the header has {len(header)}")
    ws.append([convert(c, v, line) for c, v in zip(header, row)])

# Format text columns as Text (@) so values typed into them later stay text too.
# openpyxl stores any string that starts with "=" as a formula; force those back to text.
for i, col in enumerate(header, start=1):
    fmt = "yyyy-mm-dd" if col in dates else ("General" if col in numbers else "@")
    for (cell,) in ws.iter_rows(min_col=i, max_col=i, min_row=2):
        cell.number_format = fmt
        if fmt == "@" and isinstance(cell.value, str):
            cell.data_type = "s"

# Write to a temporary file and rename, so a failed run never leaves half a workbook
fd, tmp = tempfile.mkstemp(suffix=".xlsx", dir=os.path.dirname(os.path.abspath(a.dst)))
os.close(fd)
try:
    wb.save(tmp)
    # Read it back: every text cell must equal its CSV field exactly
    back = list(load_workbook(tmp).active.iter_rows(values_only=True))
    for line, (want, got) in enumerate(zip(rows[1:], back[1:]), start=2):
        for col, w, g in zip(header, want, got):
            if col not in numbers and col not in dates and (g or "") != w:
                sys.exit(f"read-back mismatch, line {line}, column {col}: {w!r} became {g!r}")
    os.replace(tmp, a.dst)
finally:
    if os.path.exists(tmp):
        os.remove(tmp)
print(f"{a.dst}: {len(rows) - 1} rows, {len(header)} columns, "
      f"{len(header) - len(numbers) - len(dates)} as text; read-back check passed")

Run it on your file, and watch it refuse bad input

To use it:

Install the library once: pip install openpyxl.
Save the script as csv_to_xlsx.py.
Run it with the columns you know are numbers or dates. For our test file, with the ship date as a real US-style date:

$ python csv_to_xlsx.py traps.csv typed.xlsx --date ship_date=%m/%d/%Y
typed.xlsx: 2 rows, 12 columns, 11 as text; read-back check passed

With no options, all 12 columns stayed text, all 24 cells matched the file, and LibreOffice’s own CSV export of the workbook was identical to the source. With --date, the two ship dates became the real dates March 4 and December 5, 2026, because we said which format the file uses instead of letting a locale decide.

We also ran it on the failures you will meet. These were an empty file, a short row, a column name that does not exist, 12 USD in a number column and a date in the wrong format. Each stopped the run with the line and column named, and left no output file behind. A file with a byte-order mark, a quoted field with a comma in it and an empty amount came through correctly.

When not to use it: the script reads the whole file into memory. We ran it only on small files, so try it on a copy before a large export. It does not handle thousands separators (1,250.00) in number columns; it stops on them, so clean those first or leave the column as text. And a worksheet holds at most 1,048,576 rows, per Microsoft’s limits page, so a larger CSV needs a different plan.

Fix 5: when you produce the CSV, make the receiver’s job easier

If your own system or script writes the CSV, two habits reduce the damage on the other side. Write dates as 2026-03-04 (year, month, day). In our LibreOffice runs it read as March 4 under both US and UK settings. And when the receiver opens files in Excel, send an .xlsx with typed columns instead, made with the script above.

Do your CSVs come out of email or a PDF in the first place? Our guides to getting email data into Excel and bank statement PDFs to Excel cover the step before this one. More on the automation topic page.

Which fix to use

A one-off file you open yourself: Fix 1 in your spreadsheet (Power Query with detection off, the wizard, or LibreOffice’s Text column type). Nothing to maintain.
The same export every week or month, opened in Excel: a saved Power Query with types set once, or the script in Fix 4 if the file passes through a script anyway. Both repeat the same types every time.
Data a script reads: dtype=str, keep_default_na=False in pandas, or Python’s csv module, where “no automatic data type conversion is performed” unless you ask for it.
Your own Excel 365 or 2024 for everyday typing and pasting: Fix 2 as well, knowing it covers four named conversions and may not cover slash or dash dates.

Whichever route you pick, test it once on a real export before you trust it. Find a row with a leading zero, a long ID and a date where the day is 12 or lower, and compare them with the CSV opened in a text editor. If 02134 is still 02134, the import did its job.

How we made this

On October 21, 2026, we wrote a two-row CSV with 12 trap columns of made-up values. We opened it on Linux with LibreOffice 24.2.7.2 under four import settings, pandas 3.0.5 in two ways and the script in this guide (Python 3.13, openpyxl 3.1.5), and read every result back cell by cell; the script also ran on small edge-case files. We read Microsoft’s support and Power Query pages, LibreOffice’s CSV filter documentation, the pandas reference, RFC 4180 and the two gene-name papers that day. We did not have Excel, so every Excel step here comes from Microsoft’s documentation and is untested, and two rows of test data cannot show every value a real export holds.

Sources

Microsoft Support, Keeping leading zeros and large numbers, read October 21, 2026
Microsoft Support, Set automatic data conversions, read October 21, 2026
Microsoft Support, Stop automatically changing numbers to dates, read October 21, 2026
Microsoft Support, Excel specifications and limits, read October 21, 2026
Microsoft Support, Import or export text (.txt or .csv) files, read October 21, 2026
Microsoft Support, Text Import Wizard, read October 21, 2026
Microsoft Learn, Power Query Text/CSV connector, read October 21, 2026
LibreOffice Help, Text Import, read October 21, 2026
LibreOffice Help, CSV Filter Options, read October 21, 2026
Python, csv module documentation, read October 21, 2026
pandas, read_csv reference, read October 21, 2026
openpyxl, documentation, version 3.1.5
IETF, RFC 4180: Common Format and MIME Type for Comma-Separated Values (CSV) Files
Ziemann, Eren and El-Osta, Gene name errors are widespread in the scientific literature, Genome Biology, 2016
Bruford et al., Guidelines for human gene nomenclature, Nature Genetics, 2020
28creation test, October 21, 2026: one generated CSV, 12 columns, 24 cells, scored by our own script