Delimiters, decimal commas and regional list separators

Separate a field delimiter from a decimal comma before a locale setting rewrites prices.

A decimal comma is not a column break

The guide for a CSV that opens in one column covers a separator the importer does not expect, such as a whole file of semicolons read as commas. This page is the case where the separator and the number format belong to the same regional setting and fight each other. A price written 12,50 uses a comma as a decimal mark. A file that also uses commas between columns cannot tell that comma from the decimal comma unless the price is quoted. Many European exports avoid the clash by separating columns with semicolons and keeping the comma inside the price.

Microsoft’s import and export notes describe the same pairing. The list separator used when Excel saves CSV defaults to a comma, and Windows regional settings can change it for every program. Excel can also be told to use a comma as its decimal separator and a period as its thousands separator; the documented consequence is that Excel then uses a semicolon as the list separator when it saves CSV. Those are Excel and Windows settings. They are not stored inside a CSV file. A CSV file is the characters you see, plus quoting. Opening it on a machine with the other region applies that machine’s habit.

sku;unit_price;quantity;note
NM-14;12,50;3;"Café; small"
NM-27;1.234,50;1;display set
NM-03;8,00;12;plain

Read with semicolon as the delimiter, each data row has four fields. The first price is the four characters 12,50. The second is 1.234,50, with a period as the thousands mark and a comma as the decimal mark. The note on the first product is Café; small, including the semicolon, because quotes hold that field together. Nothing in the file says “this comma is a decimal.” That is context you supply.

What the wrong delimiter does to this file

Auto detection on the sample prefers semicolon. The rows share a width of four, and comma is a worse structural fit. That preference is a score, not a certificate. If you force Comma, parsing stops. The second record contains a quotation mark after the field has already started: the bytes look like 12,50;3;"Café; small" once semicolons are no longer separators. The error is a quote inside an unquoted field, at record 2, line 2, field 2. It is not a tidy one-column import, and it is not an invitation to delete the quotes.

A second teaching file uses US-style thousands marks under a comma delimiter:

sku,amount,note
NM-14,"1,234.50",quoted thousands
NM-27,1,234.50,unquoted thousands

The quoted amount stays one field, the six characters 1,234.50. The unquoted amount splits. That data row has four fields: NM-27, 1, 234.50, and the note. The header has three. The repair tool keeps the uneven record visible and blocks download. Record numbers include the header, so the uneven row is source record 3. Do not “fix” it by deleting 234.50. Quote the amount, or remove the thousands separator only if you know the remaining characters are the number you want.

Confirm the locale before you convert

1. Open CSV check and repair and load locale-decimals.csv with the file picker. Leave the delimiter on Auto long enough to read the detected separator, then set Semicolon explicitly so the preview does not depend on the score.
2. Keep the header on. You should see three data records and four columns. The first note is one cell, Café; small. The prices still contain commas.
3. Switch the input delimiter to Comma and preview again. Expect the quoting error on record 2. Return to Semicolon. The menu does not edit the original file.
4. If you need a comma-separated file for a system that understands only commas, set the output delimiter to Comma and download. The parser has already split fields, so the decimal commas stay inside the price fields and the note’s semicolon stays inside the note. Open the download and check those three prices.
5. Load thousands-comma.csv with Comma. Confirm the quoted amount is intact and the unquoted row is reported as an uneven record. Leave it blocked until the source quotes that field or you regenerate it.

Check the delimiter against the preview →

Quoted fields follow the usual CSV rule, which the quoting guide walks through with commas inside sentences. A decimal comma does not need quotes when the delimiter is a semicolon. It does need quotes, or a different delimiter, when the delimiter is also a comma.

Common errors compared

Edge cases

A price can be empty, a dash, or the word n/a. Those are not decimal commas. Do not run a conversion that assumes every comma in the column is numeric. The sample’s quantity column is a plain integer with no grouping mark. Mixing 12 and 1,234 in one column is a second decision: the comma might be a thousands mark or a decimal mark depending on the exporter.

Spaces around the decimal comma, such as 12, 50, are a different string from 12,50. This tool does not remove that space unless you turn on trimming in CSV clean, and trimming removes edges of the field, not an internal space. Currency symbols and accounting parentheses are also outside the plain-decimal rule.

A file can use tab or pipe as the delimiter and still contain comma decimals. Auto detection only chooses among comma, semicolon, tab and pipe, and only when the widths support a winner. If two delimiters score the same, the tool asks you to choose. A one-column file has no width signal. Detection will not invent a decimal convention.

Quoted line breaks inside a note still count as one record. Physical line numbers and source record numbers can differ. The locale of the decimal mark does not change that. If the error you see is an unclosed quote, fix quoting before you debate commas. The quoting guide is the right page for that shape.

Excel may display 12,50 as twelve and a half after a later import even though TableMender stored the four characters. Display in Excel is not the CSV. Check the formula bar or a text editor when the region and the file disagree. The reverse also happens: Excel shows 12.5 because it converted text to a number and dropped a trailing zero you cared about in 8,00.

What TableMender will and will not calculate

The repair tool parses delimiters and writes the fields again. It does not parse locales. 12,50 stays the characters 1, 2, comma, 5, 0 through a semicolon-to-comma conversion. 1.234,50 is not rewritten as 1234.50 or as 1.234.50. There is no “European numbers” switch.

On the CSV to Excel page every column starts as Text, so those price strings survive in the workbook. If you explicitly choose Number or two-decimal display for the price column, export is blocked. The accepted numeric grammar is an optional minus, digits, and an optional dot fraction: 12 or -39.90. Commas, spaces, currency signs, plus signs and exponents are rejected, and the message names the source record. That block is intentional. A silent rewrite from 12,50 to 1250 or to 12.50 would be a guess.

Quantity in the sample can be a number if you select that column alone. Header cells stay text. Blank numeric cells stay blank. Two-decimal display changes how a stored number looks; it does not round the stored value, and it never runs if the text was illegal in the first place.

Limits you should believe

TableMender cannot see your Windows list separator or your Excel “use system separators” checkbox. Two analysts can download the same bytes and watch Excel split them differently. The preview in this browser is the structural reading under the delimiter you selected. It is the check to trust before you hand the file to Excel.

The tool supports four delimiters. It will not treat a space as a column break, and it will not honor a custom separator such as a colon or a double pipe. It does not import an existing xlsx file, so it cannot recover a price Excel has already turned into a binary number. Keep the CSV that still contains 12,50.

These rows are synthetic catalog lines. They do not show tax rules, currency conversion, or which of 1.234,50 and 1,234.50 a particular bank uses. If both shapes appear in one extract, stop and ask the exporter. No delimiter setting makes both grammars true at once.

← All guides