THE DATA DESK · 03

Convert a semicolon CSV to comma-separated

A “CSV” from a European Excel or a bank export often uses semicolons; a database dump may use tabs or pipes. Commafield reads the file as CSV, so quoted cells and decimal commas survive, and writes it back with the separator you choose.

Steps

Open the CSV cleaner and load the file with Open CSV…, paste it, or drop it on the panel. The status line under the table names the separator it found, for example Separator: semicolon.
If the separator is wrong, open Change separator under the file name and pick it yourself.
For a pure conversion, untick Trim extra spaces, Remove empty rows and Remove duplicate rows. Leave them on if you want a cleaned file as well.
Set Output to Comma, Semicolon, Tab (.tsv) or Pipe. Same as input keeps the original separator.
Select Download cleaned CSV. A tab-separated download is named .tsv; the others are .csv.

Worked example: prices with decimal commas

Download semicolon.csv. The prices use a decimal comma, one product name contains a comma, and one note contains a semicolon inside quotes.

Input (semicolon)

sku;product;price;note
A-100;Canvas tote;24,00;
A-101;Enamel mug;14,50;"Ships in 2; 3 days"
A-102;"Linen apron, blue";32,00;

Output: Comma

sku,product,price,note
A-100,Canvas tote,"24,00",
A-101,Enamel mug,"14,50",Ships in 2; 3 days
A-102,"Linen apron, blue","32,00",

Still 4 columns in every row. Cells that contain the new separator are quoted; the semicolon note no longer needs quotes.

A find-and-replace of ; with , would have produced five columns on every data row, because each decimal comma such as 24,00 splits a price in two. With Output: Tab, no quotes are needed at all, because no cell contains a tab:

sku→product→price→note
A-100→Canvas tote→24,00→
A-101→Enamel mug→14,50→Ships in 2; 3 days
A-102→Linen apron, blue→32,00→

→ marks a tab character. The real file starts with a UTF-8 byte order mark and uses Windows line endings (CRLF).

Change the delimiter of your CSV

Why some CSV files use semicolons

In many European locales the comma is the decimal separator, so Excel uses the system list separator, a semicolon, when it saves “CSV”. The file is still a delimited text file; it just does not follow the comma convention in RFC 4180. Problems appear when the file moves between systems:

An import tool or script that expects commas reads each semicolon row as a single column.
Excel set to a comma locale opens a semicolon file with everything in column A. Use Data → From Text/CSV and choose the delimiter, or convert the file first.
Pipes and tabs are common in database and log exports because they rarely appear inside values.

Quoting rules in the output

A cell is quoted when it contains the output separator, a double quote or a line break. Other cells are written as they are.
A double quote inside a cell is doubled: Sam "Sammy" Ito becomes "Sam ""Sammy"" Ito".
A line break inside a quoted cell stays inside the cell; it does not start a new row.
Empty cells stay empty. A row shorter than the header gets empty cells so every row has the same number of columns, and the table marks them.
A row with more filled cells than the header, or a quote that is never closed, pauses the download and is named by line number, so values are not silently dropped.
With Protect against spreadsheet formulas on, cells starting with =, +, - or @ (other than plain numbers) get a leading apostrophe. It is off by default so a conversion does not change values.

Detection reads the first non-empty line and picks the separator (comma, tab, semicolon or pipe) that splits it into the most columns. A one-column file is read as comma-separated.

Questions

Why can’t I just replace every semicolon with a comma?

Because cells can contain commas and semicolons. A decimal value such as 24,00 or a quoted note such as "Ships in 2; 3 days" would be split into extra columns. Reading the file as CSV and writing it again quotes those cells correctly.

Does it convert decimal commas to decimal points?

No. Cell values are kept as text, so 24,00 stays 24,00 and is quoted in the comma-separated output. Convert number formats in your spreadsheet or import tool if you need 24.00.

What if the separator is detected wrongly?

Open Change separator under the source file and pick comma, semicolon, tab or pipe. Detection looks at the first non-empty line and chooses the separator that splits it into the most columns.

Will the clean-up steps change my data during conversion?

They can. Trimming, empty-row removal and duplicate removal apply to the download when they are on. For a conversion only, untick all three; short rows are still padded with empty cells so every row has the same number of columns.

Which encoding does the converted file use?

UTF-8 with a byte order mark, which helps Excel recognise accented characters. Input files must be UTF-8; if characters look wrong, re-save the original as CSV UTF-8 first.

Reference: RFC 4180 — common CSV format. Files are processed in this browser tab and are not uploaded. Read the privacy notice.