CSV opens in one column? Check for a semicolon delimiter

You receive a CSV export, open it, and see id;memo;count in a single header cell. The file may use semicolons while the reader expects commas. Changing the reading setting is the first thing to try; replacing every semicolon can damage the data.

Why it happens

The .csv extension does not tell every reader which delimiter to use. In this example, semicolons separate fields. A comma-configured parser reads the entire header as one field because the header contains no commas.

That does not mean every data record will also have one field. A memo can contain a comma, so the wrong setting may split other records differently. Inspect several records and their actual values.

Reproduce it without using a real customer export

Download the synthetic UTF-8 file 04-semicolon.csv:

id;memo;count
A1;"red, blue; green";2
A2;"say ""hello""";3

With semicolon selected, there are three records including the header, two data records and three fields per record. A1's memo must remain red, blue; green. A2's memo must remain say "hello".

Check the delimiter in CSV Helper

Open the CSV checker and select the sample.

1. If Auto detects the file correctly, keep that successful result. Do not describe the sample as an automatic-detection failure. 2. Inspect the detected encoding and delimiter in the result. For this sample, they should be UTF-8 and semicolon. 3. To reproduce the mismatch, deliberately select comma and inspect the one-field header. This is an intentional wrong setting. 4. Select semicolon, keep the header setting enabled, and check the three columns and two data records. 5. Compare the memo values with the original file, including punctuation and quotes.

The checker changes how the file is read; it does not convert the source into comma-separated CSV. If you need JSON, the CSV-to-JSON tool has its own input delimiter setting. Check that setting and its output separately.

Developer check: compare the two settings

Put the downloaded sample in your working directory and run Python 3:

import csv

def read_with(delimiter):
    with open('04-semicolon.csv', encoding='utf-8', newline='') as f:
        return list(csv.reader(f, delimiter=delimiter, strict=True))

wrong = read_with(',')
correct = read_with(';')
print(len(wrong[0]))
print([len(row) for row in correct])
print(correct[1][1])

Expected output:

1
[3, 3, 3]
red, blue; green

Python's CSV reader lets you specify the delimiter explicitly. Use a writer when a different output format is required. Python csv documentation

Need a comma-separated copy? Parse, then write

Continue in the same Python session:

with open('04-comma.csv', 'w', encoding='utf-8', newline='') as f:
    csv.writer(f, lineterminator='\r\n').writerows(correct)

with open('04-comma.csv', encoding='utf-8', newline='') as f:
    reread = list(csv.reader(f, strict=True))
assert reread == correct
print('Cell values preserved:', reread == correct)

Expected output: Cell values preserved: True. The supplied 04-comma.csv is an existing verified output sample. The writer quotes fields as needed instead of treating punctuation inside a value as a separator.

A global ; → , replacement changes A1's memo to red, blue, green. Even if the resulting table opens, the original memo has changed. This is why opening successfully is not enough to prove a conversion is correct.

Limits and Excel-specific advice

Selecting a delimiter cannot restore missing values, repair every quotation error or fix text decoded with the wrong encoding. Automatic detection is a convenience; compare its result with the sender's format and multiple records.

Excel provides a Text/CSV import workflow for choosing how a file is read. The exact interface depends on the Excel version and platform; this guide's verified reproduction uses Python, not an Excel screen recording. Microsoft import documentation

RFC 4180 describes common comma-separated CSV syntax. A semicolon-separated dialect is not made compliant with every part of that document merely by using quotes. Confirm the receiving application's expected dialect before sending a converted copy. RFC 4180

Reviewed: 2026-10-18. Synthetic fixtures, wrong/correct delimiter results, punctuation preservation and writer round-trip verified with Python. No Excel GUI or browser-interface test was performed.

AI assistance: AI helped draft this guide and its code. The examples use synthetic data and were checked by executing the code. No customer files were used.