Toolium

Excel to CSV: The BOM, the Date Format, and Everything Else That Goes Wrong

Hemanth Gedda8 min read

The Excel-to-CSV conversion problem has a surprising amount of depth once you get past the happy path. The happy path is: take an .xlsx file, extract the cell values from the first sheet, write them out with commas between columns and newlines between rows. That works fine for 90% of files. The other 10% have one of several gotchas - Unicode characters that Excel mangles when re-reading the CSV, dates that get reformatted in ways the user did not expect, formulas that turn out to be stored without cached values, merged cells that have no good CSV representation. The Toolium tool handles these, but knowing what is going on helps you spot when the conversion is doing the right thing versus when it needs adjustment.

The basic conversion, with what gets preserved and what does not

The Excel to CSV tool uses SheetJS to read the .xlsx file and write CSV. The flow is straightforward: parse the workbook, pick a sheet, walk the cells, write the values delimited by commas and newlines. The output is RFC 4180 compliant - values containing commas or newlines get wrapped in double quotes, and embedded double quotes get escaped as two consecutive double quotes.

What gets preserved:

  • Cell values, including the calculated results of formulas (not the formulas themselves).
  • Number values as numbers, with full precision up to JavaScript's float limits.
  • Text exactly as it appears.
  • Date values formatted as ISO 8601 strings (YYYY-MM-DD), which is the only format every downstream tool reliably understands.

What gets lost:

  • Cell formatting (colors, fonts, borders, alignment) - CSV is plain text and has no concept of formatting.
  • Formulas themselves - only the cached values come through.
  • Hidden columns and rows - they get written like visible ones.
  • Comments, data validations, conditional formatting.
  • Multiple sheets - CSV is a single-table format, so you pick one sheet per export.

The UTF-8 / BOM problem

This is the bug that ate the most user-reported time. A user converts an Excel file with Spanish or Japanese text, opens the resulting CSV in Excel, and sees gibberish where the accented characters should be. The file is correct UTF-8; Excel just is not detecting the encoding.

The cause: Excel on Windows defaults to assuming CSV files are in the system's legacy codepage (Windows-1252, basically Latin-1) rather than UTF-8, and it has no good way to detect the actual encoding without a marker. The fix is to write a UTF-8 byte-order mark (BOM) at the start of the file - three bytes (EF BB BF) that signal "this file is UTF-8." Excel sees the BOM and switches to UTF-8 parsing.

The Toolium tool writes a BOM by default. If your downstream tool dislikes the BOM (some Python scripts treat it as a literal character in the first cell), strip the first three bytes of the file. On a Mac, tail -c +4 input.csv > output.csv removes them. On Linux, sed -i '1s/^\xEF\xBB\xBF//' input.csv.

The date format question

Dates are the most common silent failure in spreadsheet conversions. Excel stores dates as floating-point numbers (the number of days since 1900-01-01) with formatting metadata that says "display this as a date." When you read the cell, the raw value is a number. When SheetJS sees the formatting metadata says "date," it converts to a JavaScript Date.

The Toolium tool writes dates in ISO 8601 format: YYYY-MM-DD for date-only, YYYY-MM-DDTHH:MM:SS for date-time. This is the format every modern downstream tool understands - SQL imports, Python pandas, JavaScript Date constructors, R. If you need a different date format for a specific destination, edit the CSV in a text editor or change the column type in your destination tool.

The trap that surprises users: if your Excel column says "3/15/2024" but you wanted "March 15, 2024," that is a display format setting in Excel, not a data setting. The underlying value is the same date; the CSV output uses ISO format regardless of how Excel chose to display it.

Formulas: cached values vs not

When you write a formula in Excel and save the file, Excel stores both the formula (=SUM(A1:A10)) and the cached calculation result (55, say). The cached result is what most downstream tools want. If you have ever opened an .xlsx file in Numbers on Mac or a script and seen formula text instead of values, the file was saved without cached values.

This happens occasionally when files are saved by automated tools that produce .xlsx output but do not run the calculation engine. LibreOffice in some configurations does this. So do some Python xlsx-writing libraries.

The fix is to open and re-save the file in Excel itself (or any tool that runs the calc engine). The cached values get filled in, and the conversion works correctly. The Toolium tool reads what is in the file; it cannot recalculate.

Merged cells

Merged cells are an Excel display feature: visually, multiple cells appear as one big cell with a single value. In the underlying data model, the value is stored in the top-left cell of the merge region, and the other cells are empty.

CSV has no concept of merging. The Toolium tool writes the value in the corresponding top-left position and empty strings for the other cells. This is the standard behavior, but it can surprise people who see "blank rows" in their CSV output where the original spreadsheet looked complete.

The workaround, if you need the merged value to appear in every row of the merge region, is to unmerge the cells in Excel first (Format > Cells > Alignment > uncheck "Merge cells") and then copy the value down before exporting. Or do the fill-down after import in the destination tool.

The "huge file" question

Browser memory is finite. Files up to about 100,000 rows convert smoothly. Past that the browser tab can run out of memory or hang for a while. For larger files, a command-line tool like csvkit or Python with pandas is more reliable: pandas.read_excel('input.xlsx').to_csv('output.csv', index=False).

The Toolium tool does not warn before attempting large files; it just tries and either succeeds or fails. If it fails, you will see the browser tab become unresponsive. Closing and reopening the tab is the fix.

Privacy

The conversion runs entirely in your browser. SheetJS reads your Excel file from a local File object; no network request carries the file's contents. If you are converting financial data, customer records, or anything else sensitive, the contents do not leave your computer.

Try the tool mentioned in this article

Open tool