How to Convert a Semicolon CSV to a Comma CSV (and Keep Quoted Names Intact)

To convert a semicolon CSV to a comma CSV, read the file with a parser that respects quotes, then write it again with a comma between cells, putting quotes around any cell that contains a comma. A plain find and replace of semicolons with commas looks like it works and then breaks the first time a company name contains a comma. This post explains why files use semicolons, what goes wrong with a quick replace, and how to switch separators safely before you import or verify a list.

Convert a CSV separator →

Why some CSV files use semicolons

CSV stands for comma-separated values, so a semicolon file looks like a mistake. It is not, and it happens a lot with exports from European tools and from spreadsheets used in many regions.

In many spreadsheet programs the separator written into a CSV follows the computer's regional settings. Some regions write the decimal mark as a comma, so 1,5 means one and a half. A comma between cells would then be ambiguous with the comma inside a number, and the program uses a semicolon between cells instead.

The effect is that a file saved on one computer opens as a single long column on another. Every cell in a row is stuffed into column A, separated by semicolons, because the second computer is looking for commas.

RFC 4180 describes the comma-separated form: fields separated by commas, and quotes around any field that holds a comma, a double quote or a line break. It also says implementations differ, which is why you see semicolons, tabs and pipes in the wild.

The four separators you will meet

• Comma. The default for most importers, sequencers and CRMs.
• Semicolon. Common in files from regions that use decimal commas.
• Tab. Often called TSV. Common in database and analytics exports.
• Pipe. A pipe character between cells. Some systems ask for it because pipes rarely appear in names.

Your target decides which to produce. If the importer you are loading says comma, give it a comma. Do not guess; read its import page.

Why find and replace is risky

Say your semicolon file has this row:

Jane;Cooper;"Northwind, Inc.";jane@example.com

Replace each semicolon with a comma and you get:

Jane,Cooper,"Northwind, Inc.",jane@example.com

That one is fine, because the company name was already in quotes. Now imagine the original file left the quotes off, which semicolon files often do because commas do not need protecting there:

Jane;Cooper;Northwind, Inc.;jane@example.com

A replace gives:

Jane,Cooper,Northwind, Inc.,jane@example.com

That row now has five cells where the header has four. The company name has split into Northwind and Inc., and the email address has moved one column to the right. Every column after the company is wrong, and a verification tool reading the Email column now sees the wrong thing.

Doing it properly means reading the file with the old separator, so the cells are known, and writing it with the new one, adding quotes where a cell holds the new separator. That is what the converter does. When it writes a cell, it puts it in quotes only if the cell contains the new separator, a quote or a line break, unless you ask for every cell to be quoted.

How to convert, step by step

1. Paste the text or upload the file. CSV, TSV and TXT files are accepted, up to 25 MB. The file is read in your browser and is not uploaded to SimpleVerifier.
2. Check the current separator. Leave it on automatic, which works it out from the first lines, or choose it. Choose by hand if the file has a single column, because automatic detection can guess wrongly there.
3. Choose the separator you want. Comma, semicolon, tab or pipe.
4. Read the counts. The tool tells you if some rows have a different number of cells from the first row. That is your warning that the separator was read wrongly, or that a cell contains the separator without quotes.
5. Download and import.

What a mismatch in cell counts means

If most rows have the right number of cells and a few do not, the problem is the rows, not the separator. Typical causes:

• A cell contains the separator and was never put in quotes.
• A cell contains a line break, such as an address with two lines, and the quotes around it were lost.
• A stray quote character opened a quoted region that was never closed.

The CSV broken row fixer repairs rows like these. Do that first if the counts are wrong, then convert.

If every row has the wrong number of cells, the separator was probably detected wrongly. Choose it by hand and run it again.

Decimal commas and numbers

The converter changes the separator between cells and nothing else. A number written 1,5 stays 1,5. When you convert to comma, that cell is quoted so the comma inside it does not split the cell.

If you also need to change decimal marks, that is a different job. It matters for lead lists less than for price lists, but be aware that a revenue or employee-count column written with decimal commas will import as text in a system that expects a dot.

The UTF-8 marker and accented names

Names such as Müller, Núñez and Łukasz are common in lead lists. If your file shows odd characters in place of them, the problem is the encoding, not the separator.

The converter can add a UTF-8 marker, a three-byte mark at the start of the file that some versions of Excel use to recognise UTF-8. With it, accented names open correctly in those versions.

Two cautions:

• Some importers treat the marker as part of the first header. The first column header then reads as an invisible character plus its name, and the import cannot match it. If that happens, leave the marker off.
• It does not repair a file that is already garbled. If characters are already wrong, use the CSV encoding fixer on the original.

What this means for verification

Verification tools read one email column. If the separator is wrong, the column the tool finds may be a mix of first names, companies and half an address, and the results will be meaningless.

So convert first, check that the Email column holds addresses, and then verify. Upload the converted file at /verify and SimpleVerifier checks each address against the mail server and tells you which are valid, risky or invalid, with the reason.

SimpleVerifier Pro is $39 a month, with the first 100,000 checks a month at full speed, then slower, never cut off. Our post on cleaning CSV columns before an import covers what else to tidy while the file is open.

When you need an Excel file instead

The converter writes text files. If the next step is a spreadsheet, use CSV to Excel, and if you have an .xlsx file that needs to become a CSV, use Excel to CSV. The converter reads text files only, so it will not open a workbook directly.

Limits

• It changes the separator only. It does not fix encodings, dates or number formats.
• On a file with one column, the separator may be detected wrongly, so choose it by hand.
• A cell that contains the separator without quotes splits into two cells before the conversion happens.
• It reads text files, not .xlsx workbooks.

A short checklist

1. Open the file in a plain text view and look at the first row. Which character is between the cells?
2. Check the target importer's page for the separator it wants.
3. Convert, and make sure the cell counts agree.
4. Decide on the UTF-8 marker.
5. Check that the Email column holds addresses, then verify the list.

Five minutes spent on the separator saves a verification run on garbage.