How to convert XLS to CSV without breaking your data

An XLS to CSV conversion looks trivial. You open the workbook, choose Save As, pick CSV and move on. Then the file reaches your ERP, your database or a colleague in another country, and the problems start. Order dates arrive as five digit numbers, product codes like 00412 become 412, and the customer named Müller is now Müller. None of these are random bugs. Each one has a precise cause, and each one has a precise fix.

This guide walks through the four failures that account for almost every broken CSV export, explains what is happening inside the file, and shows the settings that prevent them. If you only need a clean file right now, the XLS to CSV converter applies these defaults for you.

Why CSV loses information that XLS keeps

An XLS workbook stores typed cells. A cell knows whether it holds a number, a string, a boolean or a date, and it carries a display format on top. CSV stores none of that. It is plain text, one row per line, values separated by a delimiter. Every value in a CSV file is a string until some program decides otherwise.

That decision is where things go wrong. The exporting program has to turn each typed cell into text, and the importing program has to guess the type back. Two guesses, two chances to lose information. The fix is to make the export explicit, so the importing side has nothing left to guess.

Dates and the Excel serial number

Excel does not store dates as dates. It stores a serial number, the count of days since a fixed starting point, and applies a date format for display. In the default 1900 date system, serial 1 is January 1, 1900, and serial 45,931 is October 1, 2025. Times are the fractional part, so 45,931.5 is noon on that day.

When an exporter writes the raw value instead of the formatted one, your CSV contains 45931. A database column of type DATE will reject it, and a person reading the file will not recognize it.

The 1900 leap year bug

There is one more trap. Excel treats 1900 as a leap year and accepts February 29, 1900 as serial 60, a date that never existed. This was inherited from Lotus 1-2-3 for compatibility and has been kept ever since. The practical effect is that every serial below 61 is off by one day if you convert it with a naive formula. Code that turns serials into dates has to subtract an extra day only for serials of 61 and above, or use a library that already accounts for it.

The 1904 date system

Workbooks created on older Mac versions of Excel can use the 1904 date system, where serial 0 is January 1, 1904. The same serial number in the two systems differs by 1,462 days. The workbook stores a flag that says which system applies, and a correct converter reads it before translating any date.

The safe output is ISO 8601 text, such as 2025-10-01 or 2025-10-01T12:00:00. Every database and every programming language parses it without ambiguity, unlike 01/10/2025, which means January 10 in the US and October 1 in most of Europe. XlsConverter reads the date system flag, applies the leap year correction and writes ISO dates by default, with an option to choose another pattern when your target needs one.

Leading zeros and long identifiers

ZIP codes, product SKUs, phone numbers, bank sort codes and employee IDs often start with zero. If the cell was typed as a number, the zero was never stored at all. 00412 entered into a General formatted cell becomes the number 412. If the cell was typed as text, the zero is there, but a careless exporter or importer can still drop it.

The same thing happens to long numeric identifiers. Excel stores numbers as double precision floats with 15 significant digits. A 16 digit card reference or an 18 digit order ID loses its last digits, and large values may be written in scientific notation such as 1.23457E+17.

Three rules keep identifiers intact.

• Export text cells as text, exactly as stored, never through a numeric conversion.
• For numeric cells with a custom format like 00000, export the formatted value, so the padding survives.
• Quote identifier columns in the CSV, which tells most importers to keep them as strings.

If the zeros are already gone in the source workbook, no converter can recover them by itself. You can restore them with a padding rule, for example pad the SKU column to six digits, which is one of the column cleanup steps available in XlsConverter.

UTF-8, the BOM and garbled characters

Older Excel versions save CSV in the legacy code page of the operating system, often Windows-1252 in the US and Western Europe. Characters outside that code page, such as Polish, Czech, Greek or Japanese letters, are replaced with question marks and are lost for good.

UTF-8 solves the storage problem, but creates a reading problem. When Excel opens a UTF-8 CSV without a marker, it may assume the legacy code page and display Müller as Müller. The marker is the byte order mark, three bytes (EF BB BF) at the very start of the file. With a BOM, Excel recognizes UTF-8 and shows the text correctly.

The BOM is not always welcome. Some command line tools and older import scripts treat those three bytes as part of the first column name, so a header called id becomes a field that does not match. The rule of thumb is simple.

Where the CSV goes next	Encoding
Back to Excel users	UTF-8 with BOM
Database import, scripts, APIs	UTF-8 without BOM
Legacy system with a fixed code page	The code page it documents, after checking every character fits

Delimiters depend on the locale

CSV means comma separated values, but Excel does not always use a comma. In locales where the comma is the decimal separator, such as Germany, France, Poland or Brazil, Excel writes semicolons instead, because 3,50 would otherwise split into two fields. A file exported in Berlin and opened in Chicago lands in a single column.

Decide the delimiter from the receiving system, not from the machine that happens to run the export. Comma is the safest default for software. Semicolon is right when the file returns to European Excel users. Tab separated output (TSV) avoids the question entirely when your data contains many commas, such as addresses. Whatever you pick, the decimal separator in number columns should be consistent with it, and quoting must follow the usual rule: a field containing the delimiter, a quote or a line break is wrapped in double quotes, and inner quotes are doubled.

Other details worth checking

Multiple sheets

CSV holds a single table. A workbook with five sheets needs either five CSV files or a decision about which sheet matters. Save As in Excel exports only the active sheet, which is how data quietly goes missing.

Formulas and formatting

CSV stores values, not formulas. The export should write the last calculated result of each formula. Colors, merged cells and comments are dropped, so merged header cells in particular deserve a look, since only the first cell of a merged range holds the value.

Size limits

An XLS file holds at most 65,536 rows and 256 columns. XLSX raises that to 1,048,576 rows and 16,384 columns. CSV has no limit, which is why converting large CSV files back into XLS can truncate them. If you are going the other direction, see CSV to XLSX, which warns before any row is cut.

A checklist for a clean export

1. Pick the sheet or sheets you need and confirm the header row.
2. Write dates as ISO 8601 text, using the workbook date system.
3. Keep identifier columns as text and quote them.
4. Choose UTF-8, with a BOM only if Excel users open the result.
5. Choose the delimiter the receiving system expects.
6. Open the output in a plain text editor once and read the first ten lines.

Doing this by hand once is fine. Doing it every week for the same supplier file is where mistakes creep in. With XlsConverter you save these choices as a mapping template and reuse them on every file, in the browser or through a scheduled pipeline on the automation page. Newer workbooks follow the same rules through the XLSX to CSV converter.