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

> A CSV has no column types, so spreadsheets guess. Our test file lost half its cells to guessing; typed text columns kept all 24.

- Published: 2026-10-25
- Author: 28creation Desk
- Format: Guide
- URL: https://28creation.com/articles/excel-csv-dates-leading-zeros/

## The short read

**In our test, one LibreOffice import read 1/2 as January 2 under US settings and February 1 under UK settings.**

- On a sample CSV with 12 trap columns, guessing importers changed up to 18 of 24 cells; importing as text changed none.
- Excel 365 and Excel 2024 can now switch off four of these conversions; Power Query can load every column as text.

### What you can do with this

> Before you open a CSV with IDs, ZIP codes or codes like 3-4, import it with every column set to Text, then convert only the columns you know are numbers or dates.

---

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](https://support.microsoft.com/en-us/office/keep-leading-zeros-and-large-numbers-1bf7b935-36e1-4985-842f-5dfa51f85fe7) 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](https://www.rfc-editor.org/rfc/rfc4180.txt), 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](https://support.microsoft.com/en-us/office/set-automatic-data-conversions-33d1d872-9926-4ef3-9364-492444d0c7ea) 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](https://support.microsoft.com/en-us/office/excel-specifications-and-limits-1672b34d-7043-467e-8e27-269d656771c3).
- `123E5` becomes `1.23E+07`, read as scientific notation.
- `JAN1` becomes the date January 1.

The damage happens on the way in. [Microsoft says](https://support.microsoft.com/en-us/office/keep-leading-zeros-and-large-numbers-1bf7b935-36e1-4985-842f-5dfa51f85fe7) 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](https://pmc.ncbi.nlm.nih.gov/articles/PMC4994289/) 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](https://pmc.ncbi.nlm.nih.gov/articles/PMC7494048/) 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](https://help.libreoffice.org/latest/en-US/text/shared/00/00000208.html) 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](https://support.microsoft.com/en-us/office/keep-leading-zeros-and-large-numbers-1bf7b935-36e1-4985-842f-5dfa51f85fe7) are:

1. Choose **Data**, then **From Text/CSV**, and pick the file.
2. In the preview, open the data type detection list. Microsoft's [Power Query documentation](https://learn.microsoft.com/en-us/power-query/connectors/text-csv) 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.
3. 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.
4. 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](https://support.microsoft.com/en-us/office/import-or-export-text-txt-or-csv-files-5250ac4c-663c-47ce-937b-339e391393ba) 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](https://support.microsoft.com/en-us/office/text-import-wizard-c5b02af6-fda1-4440-899f-f78bafe41857) 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](https://help.libreoffice.org/latest/en-US/text/shared/00/00000208.html), select the columns in the preview and set **Column type** to **Text**. On the command line, the [CSV filter options](https://help.libreoffice.org/latest/en-US/text/shared/guide/csv_params.html) take column/format pairs in the fifth token, where format code 2 means Text. This is the string we ran for our 12 columns:

```sh
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](https://pandas.pydata.org/docs/reference/api/pandas.read_csv.html). 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](https://support.microsoft.com/en-us/office/set-automatic-data-conversions-33d1d872-9926-4ef3-9364-492444d0c7ea) 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](https://support.microsoft.com/en-us/office/stop-automatically-changing-numbers-to-dates-452bd2db-cc96-47d1-81e4-72cec11c4ed8) 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](https://openpyxl.readthedocs.io/en/stable/). 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.

```python
"""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:

1. Install the library once: `pip install openpyxl`.
2. Save the script as `csv_to_xlsx.py`.
3. 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](https://support.microsoft.com/en-us/office/excel-specifications-and-limits-1672b34d-7043-467e-8e27-269d656771c3), 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](/articles/email-data-into-excel/) and [bank statement PDFs to Excel](/articles/bank-statement-pdf-to-excel/) cover the step before this one. More on the [automation topic page](/topics/automation/).

## 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"](https://docs.python.org/3/library/csv.html) 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](https://support.microsoft.com/en-us/office/keep-leading-zeros-and-large-numbers-1bf7b935-36e1-4985-842f-5dfa51f85fe7), read October 21, 2026
- Microsoft Support, [Set automatic data conversions](https://support.microsoft.com/en-us/office/set-automatic-data-conversions-33d1d872-9926-4ef3-9364-492444d0c7ea), read October 21, 2026
- Microsoft Support, [Stop automatically changing numbers to dates](https://support.microsoft.com/en-us/office/stop-automatically-changing-numbers-to-dates-452bd2db-cc96-47d1-81e4-72cec11c4ed8), read October 21, 2026
- Microsoft Support, [Excel specifications and limits](https://support.microsoft.com/en-us/office/excel-specifications-and-limits-1672b34d-7043-467e-8e27-269d656771c3), read October 21, 2026
- Microsoft Support, [Import or export text (.txt or .csv) files](https://support.microsoft.com/en-us/office/import-or-export-text-txt-or-csv-files-5250ac4c-663c-47ce-937b-339e391393ba), read October 21, 2026
- Microsoft Support, [Text Import Wizard](https://support.microsoft.com/en-us/office/text-import-wizard-c5b02af6-fda1-4440-899f-f78bafe41857), read October 21, 2026
- Microsoft Learn, [Power Query Text/CSV connector](https://learn.microsoft.com/en-us/power-query/connectors/text-csv), read October 21, 2026
- LibreOffice Help, [Text Import](https://help.libreoffice.org/latest/en-US/text/shared/00/00000208.html), read October 21, 2026
- LibreOffice Help, [CSV Filter Options](https://help.libreoffice.org/latest/en-US/text/shared/guide/csv_params.html), read October 21, 2026
- Python, [csv module documentation](https://docs.python.org/3/library/csv.html), read October 21, 2026
- pandas, [read_csv reference](https://pandas.pydata.org/docs/reference/api/pandas.read_csv.html), read October 21, 2026
- openpyxl, [documentation](https://openpyxl.readthedocs.io/en/stable/), version 3.1.5
- IETF, [RFC 4180: Common Format and MIME Type for Comma-Separated Values (CSV) Files](https://www.rfc-editor.org/rfc/rfc4180.txt)
- Ziemann, Eren and El-Osta, [Gene name errors are widespread in the scientific literature](https://pmc.ncbi.nlm.nih.gov/articles/PMC4994289/), Genome Biology, 2016
- Bruford et al., [Guidelines for human gene nomenclature](https://pmc.ncbi.nlm.nih.gov/articles/PMC7494048/), Nature Genetics, 2020
- 28creation test, October 21, 2026: one generated CSV, 12 columns, 24 cells, scored by our own script