CSV to Excel: Delimiter Detection and the Type Inference Problem

The inverse of Excel-to-CSV has its own set of problems. If you read my other post about going the other direction, the punchlines are similar - encoding mismatches, date format ambiguity, the gap between what the user expects and what the underlying format actually stores. The CSV-to-Excel direction adds a few new ones: delimiter detection, type inference, and the BOM-as-apostrophe trap.

Delimiter detection: not all CSVs are comma-separated

The name is "CSV" (Comma-Separated Values) but the format is more accurately "Character-Separated Values" because the separator varies by region and tool. Comma is the standard in English-speaking countries, and the separator RFC 4180 specifies. Semicolon is the standard in most of Europe, because comma is the decimal separator there and using comma as both decimal and field separator is ambiguous. Tab is common for database exports. Pipe (|) shows up occasionally for content that includes commas.

The Toolium CSV to Excel tool auto-detects the delimiter by scanning the first few lines and picking the character that appears with the most consistent frequency per line. If you have a German CSV with semicolons, it should detect correctly and split on semicolon. If detection picks the wrong character, the resulting Excel file has one big column with the original separators visible in the cells.

The fix when detection fails: confirm the delimiter by opening the CSV in a text editor and looking at the first few rows. If the conversion produces one massive column, your delimiter is probably not what the tool guessed.

Type inference and the cost of guessing

CSV is a text format. Every cell is a string. Excel, on the other hand, has types - numbers, text, dates, booleans. When you convert CSV to Excel, the tool has to guess types for each column, because writing every value as text would make the spreadsheet much less useful (you could not sort or sum the columns).

The Toolium tool uses a simple heuristic: for each column, look at the first hundred or so rows. If most values parse as numbers, mark the column as numeric. If most parse as ISO dates or US dates, mark it as a date. Otherwise, text.

The heuristic gets most cases right. Where it fails:

• ZIP codes, phone numbers, product IDs. These look numeric but are conceptually text. ZIP code 02134 stored as a number becomes 2134 (the leading zero is lost). The fix is to wrap these values in quotes in the source CSV, which marks them as explicit strings. Or change the column type in Excel after import (Format Cells > Text, then re-enter the values).
• Mixed-type columns. A column that has mostly numbers but a few text entries (a "Quantity" column where some rows say "out of stock") gets marked numeric, and the text entries become #VALUE errors. The fix is to clean the source data or mark the column explicitly as text.
• Currency values with symbols. "$1,234.56" does not parse as a number because of the dollar sign and comma. The column gets marked text. To preserve numeric values, strip the symbols from the source data first.
• Dates in unusual formats. The tool recognizes ISO (YYYY-MM-DD) and US (MM/DD/YYYY) date formats. European DD/MM/YYYY may get mis-parsed because the heuristic cannot disambiguate "03/04/2024" between March 4 and April 3.

The apostrophe-in-first-column bug

You convert a CSV, open the result in Excel, and the first column has a single quote character at the start of every cell. The value is "'John" instead of "John."

The cause: the source CSV had a UTF-8 byte-order mark (BOM) at the start, and the conversion tool wrote those three bytes (EF BB BF) into the first cell. Excel renders some of those bytes as control characters and a leading apostrophe (which Excel uses as the literal-string escape).

The Toolium tool strips the BOM before parsing, so this shouldn't happen with our output. If you see it after using a different tool, the fix is either to strip the BOM from the source CSV before converting (in a text editor that supports it) or to do a find-and-replace on the resulting Excel file.

What the tool does not preserve

CSV is a flat-table format. Excel can hold things CSV cannot: multiple sheets, formulas, formatting (bold, colors, borders), charts, conditional formatting, data validation rules, and merged cells. None of these survive the conversion in either direction, because there is nothing in the CSV to derive them from.

If you need a multi-sheet workbook, the workflow is: convert each CSV individually, then open them all in Excel and use Move/Copy Sheet to combine them. There is no single conversion step that produces a multi-sheet workbook from multiple CSVs.

Header detection

By convention, the first row of a CSV is treated as headers and becomes the column titles in Excel. The tool assumes headers by default and there is no toggle to disable this in the current version. If your CSV does not have headers - say, it is a database export with no column-name row - the first row of data ends up as bold headers in Excel, and you have to delete that row manually.

A workaround if you do this often: prepend a header row to your CSV before pasting (just type "col1,col2,col3" or whatever names you want at the top), then delete that row in Excel if it really should not have headers.

Column widths and basic formatting

The tool sets reasonable default column widths based on the longest cell in each column, so you usually do not have to autofit manually. The header row is bold by default. No other formatting is applied. If you want colors, borders, or anything else, do it in Excel after import.

Privacy

Conversion runs in your browser. SheetJS produces the .xlsx file locally and triggers a download. Nothing leaves your computer. Financial data, customer records, internal logs - all safe to convert here.