{"id":45877,"date":"2026-11-10T08:22:08","date_gmt":"2026-11-10T08:22:08","guid":{"rendered":"https:\/\/www.withdata.com\/blog\/?p=45877"},"modified":"2026-11-10T08:22:08","modified_gmt":"2026-11-10T08:22:08","slug":"csv-opens-in-excel-wrong","status":"publish","type":"post","link":"https:\/\/www.withdata.com\/blog\/datafileconverter\/csv-opens-in-excel-wrong.html","title":{"rendered":"CSV in Excel: The Zeros, the 15-Digit Barcode, and the Date That Moves"},"content":{"rendered":"

A CSV is text. Excel is not a text editor: the moment it opens a .csv<\/code> it parses every field into a value \u2014 a number, a date, a boolean \u2014 and the text you sent is gone. Three things go wrong in that parse, and all three look like corruption:<\/p>\n\n\n\n\n\n\n\n
What you sent<\/th>\nWhat Excel shows<\/th>\nWhy<\/th>\n<\/tr>\n<\/thead>\n
01234<\/code><\/td>\n1234<\/code><\/td>\nit is a number, so the leading zero is not part of the value<\/td>\n<\/tr>\n
000123456789012<\/code><\/td>\n1.23457E+14<\/code><\/td>\nmore than 15 significant digits; Excel keeps 15 and shows the rest in scientific notation<\/td>\n<\/tr>\n
03\/04\/2024<\/code><\/td>\nanother day, depending on the machine<\/td>\ndates are parsed with the local regional setting<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

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 \u2026 Set the column to Text, then import<\/strong>.”<\/em> The 26-point reply is blunter: “Excel is not a CSV editor\u2026 if the exact CSV content matters, then don’t import to Excel at all.”<\/em> An ex-Excel tester in the same thread adds that the CSV reader code is 20 years old and nobody dares change it.<\/p>\n

So the real choice is not “which setting in Excel” but what you hand over<\/strong>.<\/p>\n

1. Three ways out, and only one scales<\/h2>\n

Route one \u2014 Power Query, every time.<\/strong> 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<\/em> is the point.<\/p>\n

Route two \u2014 tell them not to use Excel.<\/strong> 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.<\/p>\n

Route three \u2014 hand over a spreadsheet that is already typed.<\/strong> Convert the CSV to .xlsx<\/code> once, with the identifier columns stored as text and the numeric columns stored as numbers. Excel does not re-parse an .xlsx<\/code>: 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.<\/p>\n

That is the route this article is about, and it is a two-step job: decide the type of each column<\/strong> (this is where the CSV failed you), then write the workbook.<\/p>\n

2. A CSV converted properly<\/h2>\n

Take a file that has all three problems in four rows:<\/p>\n

order_id,zip,part_no,customer,note,qty,amount,shipped\n1001,01234,0001234567,\"ACME, Inc.\",plain,3,0.10,2026-03-01\n1002,00501,0009988776,Smith & Sons,\"has, comma and \"\"quotes\"\" inside\",1,0.50,2026-03-02\n1003,90210,1234567890,Nguyen Ltd,,0,1234.56,\n1004,00012,0000000001,\u00d3 Briain Teo.,accented name,7,99.99,2026-03-05<\/code><\/pre>\n
Open it as a source in DataFileConverter<\/a> \u2014 the preview is the file as text, with the header row already detected:<\/p>\n
\"Open<\/p>\n
On the Excel side there are only three settings that matter, and one of them is the heading:<\/p>\n
\"Set<\/p>\n
Heading On Excel Sheet<\/code> 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<\/code> where the field is a date \u2014 handy in \u00a74. Then Convert Data<\/code>:<\/p>\n
\"Convert<\/p>\n
3. What ends up in the cells<\/h2>\n
Here is the converted sheet, read back from the .xlsx<\/code> itself (not from the screen):<\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n
Column<\/th>\nStored as<\/th>\nFirst value<\/th>\n<\/tr>\n<\/thead>\n
order_id<\/code><\/td>\nnumber<\/td>\n1001<\/code><\/td>\n<\/tr>\n
zip<\/code><\/td>\ntext<\/strong><\/td>\n01234<\/code><\/td>\n<\/tr>\n
part_no<\/code><\/td>\ntext<\/strong><\/td>\n0001234567<\/code><\/td>\n<\/tr>\n
customer<\/code><\/td>\ntext<\/td>\nACME, Inc.<\/code><\/td>\n<\/tr>\n
note<\/code><\/td>\ntext<\/td>\nhas, comma and \"quotes\" inside<\/code> (one cell)<\/td>\n<\/tr>\n
qty<\/code><\/td>\nnumber<\/td>\n3<\/code><\/td>\n<\/tr>\n
amount<\/code><\/td>\nnumber<\/td>\n0.1<\/code><\/td>\n<\/tr>\n
shipped<\/code><\/td>\ntext\/date<\/td>\n2026-03-01<\/code><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
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 \u2014 and does not lose anything by being a number \u2014 is written as a number, so SUM()<\/code> works in the workbook you send.<\/p>\n
That last part is the difference between “export as text” and “convert properly”: a file where every<\/em> column is text is safe and useless (nobody can total the amount<\/code> 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<\/code> written as a number comes back rounded.<\/p>\n
4. When there are dates<\/h2>\n
Dates are the case where the reader’s<\/em> machine decides the meaning. 03\/04\/2024<\/code> is 3 April in most of Europe and 4 March in the US, and the same file opens differently on two desks.<\/p>\n
So the format is a setting, not a guess:<\/p>\n