A CSV is text. Excel is not a text editor: the moment it opens a .csv it parses every field into a value — a number, a date, a boolean — and the text you sent is gone. Three things go wrong in that parse, and all three look like corruption:
| What you sent | What Excel shows | Why |
|---|---|---|
01234 |
1234 |
it is a number, so the leading zero is not part of the value |
000123456789012 |
1.23457E+14 |
more than 15 significant digits; Excel keeps 15 and shows the rest in scientific notation |
03/04/2024 |
another day, depending on the machine | dates are parsed with the local regional setting |
The people who hit this have a common answer, and it is not a good one. In the r/excel thread about Excel changing values on open (2026-08, 47 comments), the top reply with 55 upvotes says: “Use Power Query … Set the column to Text, then import.” The 26-point reply is blunter: “Excel is not a CSV editor… if the exact CSV content matters, then don’t import to Excel at all.” An ex-Excel tester in the same thread adds that the CSV reader code is 20 years old and nobody dares change it.
So the real choice is not “which setting in Excel” but what you hand over.
1. Three ways out, and only one scales
Route one — Power Query, every time. It works, and it is the answer the subreddit gives. It is also four dialogs per file, done again for the next file, and it depends on the person on the other end knowing that Set column to Text is the point.
Route two — tell them not to use Excel. A text editor shows the raw characters. It also does not produce something you can send to a customer, and nobody is going to read a five-column file as text.
Route three — hand over a spreadsheet that is already typed. Convert the CSV to .xlsx once, with the identifier columns stored as text and the numeric columns stored as numbers. Excel does not re-parse an .xlsx: what is in the cell is what the person sees, and every downstream copy, pivot and filter keeps it. One conversion, no settings, everyone opens the same file.
That is the route this article is about, and it is a two-step job: decide the type of each column (this is where the CSV failed you), then write the workbook.
2. A CSV converted properly
Take a file that has all three problems in four rows:
order_id,zip,part_no,customer,note,qty,amount,shipped
1001,01234,0001234567,"ACME, Inc.",plain,3,0.10,2026-03-01
1002,00501,0009988776,Smith & Sons,"has, comma and ""quotes"" inside",1,0.50,2026-03-02
1003,90210,1234567890,Nguyen Ltd,,0,1234.56,
1004,00012,0000000001,Ó Briain Teo.,accented name,7,99.99,2026-03-05Open it as a source in DataFileConverter — the preview is the file as text, with the header row already detected:
On the Excel side there are only three settings that matter, and one of them is the heading:
Heading On Excel Sheet writes the first row as column names (leave it off and the names become a data row). The Columns Map gives one row per field, with Source Date Format where the field is a date — handy in §4. Then Convert Data:
3. What ends up in the cells
Here is the converted sheet, read back from the .xlsx itself (not from the screen):
| Column | Stored as | First value |
|---|---|---|
order_id |
number | 1001 |
zip |
text | 01234 |
part_no |
text | 0001234567 |
customer |
text | ACME, Inc. |
note |
text | has, comma and "quotes" inside (one cell) |
qty |
number | 3 |
amount |
number | 0.1 |
shipped |
text/date | 2026-03-01 |
The two decisions are local and mechanical: a column whose values start with a zero, or run to more than 15 digits, is an identifier and stays text; a column that is numeric throughout — and does not lose anything by being a number — is written as a number, so SUM() works in the workbook you send.
That last part is the difference between “export as text” and “convert properly”: a file where every column is text is safe and useless (nobody can total the amount column), and a file where every column is auto-typed is exactly the CSV problem again. A 19-digit id is text for the same reason as the leading zero: Excel holds 15 significant digits, so 1234567890123456789 written as a number comes back rounded.
4. When there are dates
Dates are the case where the reader’s machine decides the meaning. 03/04/2024 is 3 April in most of Europe and 4 March in the US, and the same file opens differently on two desks.
So the format is a setting, not a guess:
Source Date Format(on the Columns Map, per column) tells the converter how to read the text —
dd/mm/yyyy for 03/04/2024 = 3 April;
Excel Date Formattells it what to write —dd.mm.yyyyproduces03.04.2024.
Set both and the same input produces the intended day on any machine. Leave them alone and the value is carried through as written, which is also defensible: a date that stays text cannot move.
5. From the command line
DataFileConverter.exe convert --from=orders.csv --to=orders_safe.xlsxThe same session, with the same type decisions, for a nightly export. On Windows the desktop binary takes the verb directly — no separate command-line build.
FAQ
Why does Excel change 01234 to 1234?
Because it parses the field as a number, and a number does not have leading zeros. Nothing is corrupted: the value 1234 is what Excel believes you sent. Only a text cell keeps 01234.
Why does a 15-digit code turn into 1.23457E+14?
Excel keeps 15 significant digits and displays longer numbers in scientific notation. A 16-digit card or barcode number is already being rounded in the cell. Store identifiers of that length as text.
Can I stop Excel from doing this when opening a CSV?
Not with a global setting — there is no “always treat CSV fields as text” switch, which is what the r/excel thread is complaining about. Importing through Power Query with the column set to Text works per file; a converted .xlsx works without any step at all.
Will the converted file still open on a Mac or in Google Sheets?
Yes: .xlsx carries the cell type with the value, so every spreadsheet application shows the same thing. The type is in the file, not in the reader’s settings.
What about a CSV that has no header row?
Leave Field Name In File Header unticked, and the converted sheet has no heading row either — the first row of data stays the first row.
Can I put the data into a database instead of a spreadsheet?
That is the other direction: FileToDB imports CSV into MySQL, PostgreSQL, SQL Server and others, with the column types chosen in the same way.
Stop converting by hand
Hand over a spreadsheet that opens with the zeros, the digits and the dates intact. Try DataFileConverter — CSV, TSV, fixed-width, Excel, JSON, XML and SQL in either direction; Windows / macOS / Linux, one-time price.