Excel to CSV Pitfalls: Dates, Leading Zeros, Encoding and More

Excel to CSV goes wrong in two separate places. The export can lose things, such as other sheets, formulas and cell formatting. Reopening the CSV in Excel then damages what survived, by turning ZIP codes into numbers and long IDs into rounded ones. Most “the export corrupted my data” problems are really that second step, and the CSV itself was fine.

A CSV is only rows of text separated by a delimiter. RFC 4180 describes the common format, and it has no concept of data types, formulas, sheets, fonts or encoding. Everything that makes a spreadsheet more than text has to be decided at export time.

A worked example

We built a small workbook with openpyxl, containing the sort of data that causes trouble:

The workbook also had a hidden sheet and a third sheet. Running it through our Excel to CSV tool’s function gave this:

Order,Date,Zip,Price,Discount,Total,Tracking,Note
A-1,2026-03-14,02134,19.5,0.25,,123456789012345678,"Größe, blau"
A-2,2026-03-15 14:30:00,2134,7,0.1,6.3,123456789012346000,

Each pitfall below can be seen in those three lines. Different exporters make different choices here. Excel’s own Save As writes each cell as it is displayed, so it would have written $19.50 and 25%, for instance.

Dates

Excel stores a date as a plain number: the count of days since its start date, with the time as a fraction. Our first date is stored as 46095 in the file, and the second as 46096.604166… Only the cell’s number format makes them look like dates.

There are three ways this goes wrong.

Raw serial numbers. An exporter that ignores number formats writes 46095. If you see five-digit numbers in a date column, this is what has happened. To convert in Excel, apply a date format to the column. In code, add the serial to the start date of 30 December 1899, which is correct for dates from March 1900 onward. The odd start date comes from Excel treating 1900 as a leap year, a deliberate compatibility decision, so serial 60 is a 29 February that never existed.

Two date systems. Workbooks can use the 1904 date system instead, an old Mac convention. The same date is then 1,462 lower, so converting with the wrong system gives dates about four years off.

Ambiguous text. 03/04/2026 is March in the US and April in much of the world, and CSV gives no hint which. Excel reads it using your system’s regional settings. A file made in one country and opened in another can swap day and month for some rows, and leave the others as text, when the “month” is above 12.

What to do: export dates as ISO 8601 (2026-03-14), which sorts correctly and has only one reading. Our tool writes date cells this way, as above. In Excel, apply the custom format yyyy-mm-dd to the column before saving.

Leading zeros

Look at the Zip column in our output. The text 02134 kept its zero, and the number 2134 could not, since a number has no leading zeros to keep. If the sheet already stored it as a number, the zero was lost before any export.

The more common problem comes later. The CSV contains 02134, and then you double-click it. Microsoft notes that Excel opens a CSV directly using its default settings for each column, and those settings treat 02134 as the number 2134. If you then save, the zero is gone for good. This hits ZIP codes, phone numbers, part numbers and employee IDs.

What to do:

• Check with a plain text editor. It shows you what the CSV really holds.
• Import rather than open. In Excel, use Data > From Text/CSV, and set that column’s type to Text. The older Text Import Wizard also does this.
• On current Microsoft 365 versions, turn off the conversion under File > Options > Data > Automatic Data Conversion.

Long numbers lose digits

Look at our Tracking column. The text version kept all 18 digits. The numeric version became 123456789012346000. Excel keeps 15 significant digits of precision, so everything after the 15th becomes zero. And that is permanent once stored as a number.

Card numbers (16 digits), bank references, tracking numbers and database IDs are all at risk. Anything that is an identifier, and not a quantity, should be stored as text from the start. A helpful test: if you would never add two of them together, it is text.

Numbers can also display as 1.23E+17 when opened in Excel, which is a display format hiding the real digits. If you save the CSV from there, the exported value is what is displayed, rounded digits included.

Formulas

A CSV has no formulas, so an export keeps the last calculated value. There are two catches.

Values can be missing or stale. Our output has an empty Total on row A-1 and an empty Note on row A-2. Those two cells were formulas in a workbook generated by a script (openpyxl), which writes formulas but never calculates them, so there was no stored value to export. Open such a file in Excel and save it, and the values appear. Workbooks with calculation set to manual can similarly hold old results. Press F9 before you export.

You lose the logic. After export, the numbers are static, so keep the XLSX as your source. CSV is a copy for transfer, not a place to do further work.

There is also a security angle in the other direction. A CSV cell beginning with =, +, - or @ can be treated as a formula when opened in a spreadsheet, which is known as CSV injection. If your system exports CSVs containing text typed in by the public, and staff open them in Excel, read OWASP’s advice.

Encoding

Our Größe survived because our output is UTF-8. Excel’s plain “CSV (Comma delimited)” option uses your system’s older character set instead, so characters outside it can turn into ?, and a program reading that file as UTF-8 shows Gr��e. The reverse mistake is just as common: Excel can read a UTF-8 CSV that lacks a byte order mark (BOM) in the older character set, and Größe appears as Größe. We reproduced both by decoding those bytes with the wrong encoding in Python.

What to do: use “CSV UTF-8 (Comma delimited)” in Excel’s Save As list if you have it. That version adds a BOM, which Excel uses to recognise the encoding. A BOM can in turn trip up programs that do not expect it, since it may appear as odd characters at the start of the first column name. If a script reports a column name like Order, this is why. In Python, reading with encoding="utf-8-sig" handles it. Our tool writes UTF-8 and has an optional marker for files that will be reopened in Excel, so choose based on who reads the file next.

Delimiters by locale

Despite the name, Excel’s CSV is not always comma-separated. Microsoft’s page says the delimiter follows your Windows region’s list separator. In much of Europe, where the comma is the decimal mark (19,5), that separator is a semicolon, since a comma delimiter would split each decimal in two.

So a colleague in Germany may send you Order;Price with A-1;19,5, and US Excel will show it all in a single column. To handle this:

• Import with Data > From Text/CSV, and choose the delimiter and locale for the file.
• For your own exports, use a tool where you choose the delimiter. Our Excel to CSV tool offers comma, semicolon, tab and pipe.
• Keep decimals in a consistent format, and quote any field that contains the delimiter, as our output did for "Größe, blau".

If your data may go between countries, tab-separated values avoid most of this, because tabs rarely appear in the data.

Multiple sheets and hidden data

A CSV holds a single table. Excel warns you that “only the current worksheet will be saved”, and it is easy to click through that warning. Our example workbook had three sheets, and the output above contains only the first.

Check which sheet you are on before you export, and export each one separately if you need them. Our tool lets you pick any worksheet, or export all of them as a ZIP of CSV files. If you want the whole workbook in a single file, Excel to JSON can hold every sheet in one object.

Also watch these:

• Merged cells: the value usually belongs only to the top-left cell, and the others export empty.
• Hidden rows and columns: these generally do export, and may include data you did not mean to send.
• Blank header rows and notes at the top: these become data rows, and break imports. Remove them first.
• Line breaks inside cells: these are valid when quoted, but plenty of simple parsers treat them as new rows.

A checklist before you send a CSV

1. Format dates as yyyy-mm-dd.
2. Store identifiers as text.
3. Recalculate, and check that formula columns have values.
4. Export as UTF-8, and agree on the delimiter with whoever receives it.
5. Check that you are on the right sheet.
6. Open the result in a text editor, not Excel, and read the first few rows.

If someone hands you a CSV and you want a real spreadsheet back, CSV to Excel is one way to do that without Excel guessing as it opens the file. Check the converted columns anyway, especially IDs and dates.