Data & spreadsheets · CSV Cleaner
Semicolon-Separated CSV Explained: Decimal Commas and Locale Settings
· Background
csv delimiters interoperability
In much of Europe a comma is the decimal separator, so the 'comma-separated' file uses semicolons instead. This post explains the locale logic behind the split, how spreadsheets decide, and what happens when files cross the border.
A file from a colleague abroad opens as a single column — the moment most people discover CSV has dialects
A semicolon export opened under a comma-only assumption appears as one wide text column. That symptom reflects a parser choice, not proof that the data is corrupt. ToolAcre offers semicolon alongside comma, tab and pipe and writes the character it actually used back into the control.
The first response should be structural: inspect the raw separator and preview. Do not replace every semicolon blindly, because quoted text can contain punctuation and changing bytes without parsing can alter values. A dialect-aware read distinguishes separators from cell content.
Decimal commas — why '1,5' is a valid number in many locales, and why a comma delimiter would make it unparseable
The workbook explains semicolons through decimal commas. That is useful exchange context, but this implementation never parses `1,5` as a localized number. It preserves the cell as a string. Comma meaning inside a value and semicolon meaning between fields therefore remain separate at parse time.
Do not convert decimal marks while repairing delimiters unless a destination schema explicitly requires it. A code or prose field may legitimately contain a comma. ToolAcre handles table boundaries and leaves numerical interpretation to the receiving application.
Decimal-comma context is plausible, but ToolAcre treats cell values as strings and does not parse localized numbers
The tool does not consult the operating system’s regional or list-separator setting. Auto-detection parses a ten-row sample under each supported candidate, measures maximum width and rewards candidates producing a consistent rectangle. If none creates at least two columns, it falls back to comma.
This file-driven method is reproducible across locations. It can still choose poorly on unusual or single-column data, which is why the manual selector exists. The control reflects the detected value and changing it re-parses the original text, discarding cleaning steps already applied.
ToolAcre detects file shape; it does not read operating-system list-separator settings
Under the wrong delimiter, a semicolon record remains one cell instead of becoming several columns. ToolAcre does not interpret localized numbers, so it cannot silently change `1,5` to another numeric value during parsing. The failure is visible in column count and preview shape.
The reverse mismatch can split unquoted commas into extra cells and trigger ragged-row warnings. Quoted commas remain protected under the state machine. Read the warnings and compare expected header width rather than assuming a plausible first row proves all later records are aligned.
The parser shows structural readings, not silent numeric changes
Use `item;price;note` followed by `A;1,5;"small, red"` and `B;2,0;plain`. Semicolon parsing yields three columns, preserving both comma-bearing strings. Comma parsing leaves semicolons inside fields and can create misleading splits around the commas instead.
The detector should favor semicolon because that candidate yields a consistent three-column shape while commas do not. This example proves parser behavior without claiming that 1,5 has any numeric value. The receiving system must apply its own documented decimal convention.
Worked example: semicolon detection with comma-bearing string values
Cross-border exchange improves when sender and receiver name delimiter, encoding, header policy and value conventions. “CSV” alone does not settle any of them. Include a small fixture and verify it through the exact receiving parser before sending a production export.
If comma output is required, select comma for serialization after the semicolon source has parsed correctly. Fields containing decimal commas will then be quoted automatically, preserving them as cell text. Syntax conversion does not translate decimal notation or currency formats.
What this does not cover — converting number formats inside the data; the delimiter and the decimal mark are separate problems
Number-format conversion lies outside ToolAcre’s delimiter repair. It does not turn commas into decimal points, recognize thousands separators or cast amounts. This is deliberate string preservation, not incomplete numeric inference. The destination schema owns that transformation.
The route also cannot guarantee how a spreadsheet application chooses separators when a file is opened. It can only produce the delimiter selected in its output and standard quoting around conflicting characters. Test the target application rather than inventing vendor behavior.
The delimiter follows the locale, not the file — how the ToolAcre CSV Cleaner's delimiter repair gives you a file with one known separator
In this tool, the delimiter follows parsed file shape or a manual choice, not locale settings. That correction matters because two analysts in different offices should see the same detected source character when they load identical text. Environment does not rewrite the parser’s candidate list.
Confirm the control, inspect row widths and export for the known consumer. Semicolon is neither broken comma CSV nor automatic proof of decimal-comma data. It is one supported structural separator, and ToolAcre treats every value between separators as text.