CSV opens in one column? Identify the delimiter first

Direct answer

If each row lands in one spreadsheet column, check the file’s separator in the import preview. Select comma, semicolon, or tab to match the actual file. Do not replace punctuation throughout the file: commas and semicolons may be part of legitimate field contents.

A .csv extension does not tell you everything about how the exporter separated its fields. Your goal is a consistent table with meaningful headers and intact text, not merely a preview with more than one column.

Compare three equivalent files

Download the comma example, semicolon example, and tab example. Each describes exactly the same two synthetic records:

product	note	quantity
Notebook	Blue, lined	4
Folder	Archive; keep dry	2

The comma-delimited file looks like this:

product,note,quantity
Notebook,"Blue, lined",4
Folder,Archive; keep dry,2

In the semicolon file, the second record’s note is quoted because it contains that file’s separator:

product;note;quantity
Notebook;Blue, lined;4
Folder;"Archive; keep dry";2

The tab file uses a tab between fields. A plain-text editor may display that as spacing; enabling visible whitespace can help. Counting spaces is not a reliable way to identify a tab delimiter.

Use the preview as a structural check

Keep a copy of the source. Start Excel’s text/CSV import and inspect the delimiter setting. In the legacy Text Import Wizard, choose Delimited and then select the appropriate separator, with a double quote as the text qualifier for these samples. Microsoft documents both delimiter selection and the preview; availability and labels depend on your Excel version. Text Import Wizard documentation.

Try the known separator for each sample. The expected shape is three columns, a header row, and two data rows. The note Blue, lined must occupy one cell, as must Archive; keep dry. If a note breaks across columns, check the selected separator and quote handling before loading.

For an unfamiliar export, inspect the header and several records, especially any containing addresses, notes, or decimal punctuation. Confirm each header describes the values below it. A consistent two-column preview can still be wrong if the intended schema has five fields. If the source system offers an export specification, prefer that specification to a guess based on the first line.

Why splitting on every comma fails

The notebook row contains three comma characters but only three fields: the comma inside quotes is data. A basic find-and-replace operation cannot distinguish those roles. Likewise, applying a generic text split to each comma can create an extra cell and shift the quantity out of its column.

If you are checking files with Python, use the standard-library CSV parser with the chosen delimiter instead of line.split(','). The reader handles quoted fields. Open text files with newline='', as recommended in the Python CSV documentation.

Limits and testing notes

Some exports contain embedded newlines in quoted fields, preamble lines before the header, or inconsistent escaping. The three downloads here do not model all those cases. If no delimiter produces a sensible table, inspect whether the file is actually a spreadsheet, JSON, or HTML saved with a misleading extension. Do not assume an import setting can repair malformed records.

We parsed all three downloads with Python and confirmed their complete tables are identical, including both punctuation-bearing notes. We also checked that the wrong delimiter fails the expected three-column shape. Excel menu steps are documentation-based and were not tested in a local Excel session.

Once the columns are correct, check identifier preservation and character encoding separately. Correct separators do not guarantee correct data types or readable accents.

Source references

• Microsoft Text Import Wizard
• Python CSV reader

Verification scope: Python 3 standard-library fixture checks; application UI not tested. See the testing notes above for limitations.