Semicolon or comma: the European CSV, explained.

Why semicolons

Where the comma is the decimal separator — Germany, France, Italy, Spain, the Netherlands and many more — a comma-separated file could not tell 12,50 the amount from 12 and 50. So spreadsheet programs in those regions write CSV with semicolons, following the list separator in the system's region settings. Switzerland adds its own twist: amounts are often written 1'234.50, with an apostrophe between thousands and a decimal point.

The symptoms

• Everything in one column: a semicolon file opened with comma settings, or the other way round.
• Amounts turned into text or wrong numbers: 1.234,50 read with a decimal point, or 1,234.50 read with a decimal comma.
• A file that changes separator after a colleague in another country opens and saves it.

Reading any of them

Intact CSV Editor detects the separator from the file itself, whatever your Mac's region is set to, and shows it in the Format inspector. Numbers are kept as written; where arithmetic is needed — the status bar, sorting, filtering with at least, SQL — 1'234.50, 1.234,50 and 1,234.50 are each read as the amount they are.

Writing the one that is wanted

• Saving keeps the file's own separator and number format.
• For another audience, export with the built-in Excel (European) or Excel (US) presets, or choose the separator and the decimal separator yourself.
• Write the Numbers Another Way converts a selection between 1234.50, 1234,50, 1,234.50, 1.234,50 and 1'234.50, keeping each value's decimals.

The sep= line

Some files start with a line sep=;. Excel reads it as an instruction about the separator; most other programs see it as a first row of data. It is best not relied on for files that travel between programs.