🧩 Data Cleaning · 7 min read

CSV Opens in One Column? How to Split It Back Into Columns

Your file is not broken and no data is missing. The whole line landed in column A because the file uses a different separator than the one your app is looking for. Convert the file to the delimiter you need and the columns come back in about a minute. The two usual suspects are a semicolon-delimited export and a tab-separated file that was renamed with a .csv extension. Both are single-step fixes once you know which one you have.

This is one of the most common questions people ask about CSV files, in every one of its phrasings: “csv opens in one column”, “why is my csv all in one column”, and “csv not separating into columns” describe the same screen. Worth knowing: if the columns did split but the numbers or accents came out as junk, you have a different problem, and the encoding guide covers that one.

What you see, and what is actually happening

Three of these six are the same root cause wearing different clothes, which is why the delimiter is the first thing to check rather than the last.

Find out which separator the file really uses

Do this before you start changing settings, because the wrong setting produces a second confusing screen instead of a fix. Open the file in a plain text editor. Notepad on Windows, TextEdit in plain text mode on macOS, or a code editor if you have one. Look at the first line.

One clue beyond the separator: a file whose values are wrapped in double quotes was probably written by a real CSV library rather than stitched together by hand, and those rarely have broken quoting. If the quoting is inconsistent, the export is the thing to fix, not the import.

Fix 1: Convert the delimiter, in the browser

The delimiter converter parses the file, lets you name the separator it currently uses and the one you want, and gives you a normal comma CSV back. Because the parsing happens in the browser, a file with customer names or invoice amounts in it never gets uploaded anywhere, which matters when the export came out of a system you do not administer.

This is the right first move when you only need the file to open correctly once, or when you need to send it to somebody who will double-click it. It also handles the encoding at the same time, so a file that is semicolon-delimited and UTF-8 comes out comma-delimited and clean instead of needing two passes. If you would rather understand the conversion, changing a delimiter by hand walks through the same job with Find and Replace and with a text editor.

Fix 2: Import it properly in Excel

There are three routes inside Excel and they behave differently, mostly because two of them let Excel guess and one does not.

Get Data, then From Text/CSV

This is the reliable route in every modern version. Choose Data → Get Data → From Text/CSV, select the file, and the dialog shows a preview before anything lands on a sheet. Set Delimiter to the character you found in the text editor and File Origin to the encoding, then load. If the preview looks right, the import will look right.

Excel is unusual among spreadsheet applications here: it treats a CSV as an import problem rather than as a file to parse directly, and the import dialog is where the delimiter gets decided. Double-clicking a .csv hands that decision to a guess based on your Windows list separator. That is the entire mechanism behind a file opening in one column on one machine and correctly on another, with the same file.

Text to Columns, for a file that is already open

If the data is already sitting in column A, Data → Text to Columns, choose Delimited, tick the separator, and finish. This re-parses in place and is the quickest fix when you cannot be bothered to close and re-import. Pick the whole column first, and keep in mind that any value longer than 32,767 characters will be cut, since that is the cell limit in every spreadsheet format.

Change the separator Windows reports

In Region settings, the List separator field is what Excel uses when it guesses. Setting it to a comma makes comma files open correctly on a double-click. The cost is that the setting is system-wide, and some regional conventions use the comma as a decimal mark, which is exactly why semicolon CSVs exist in the first place. Fix the file rather than the operating system, unless this is a recurring annoyance with files you control.

Fix 3: Google Sheets

Sheets usually detects the separator on import, and when it gets it wrong you can tell it directly. Use File → Import → Upload, then set Separator type to Custom and type the character. A second route for data already on the sheet: Data → Split text to columns, which asks for the separator and re-parses the selection the way Text to Columns does.

Sheets has its own ceiling worth keeping in mind: 10 million cells per spreadsheet on the free tier, which a six-column file reaches at roughly 1.6 million rows. Delimiter problems and size problems both show up as a file that will not display properly, so check which one you have before chasing the wrong fix. If the file is genuinely too large, the oversized-CSV guide covers that case instead.

Fix 4: Two lines of Python

For a folder of exports rather than one file, a script beats a dialog. pandas reads the separator you name, and its sniffer will guess the dialect when you do not know it:

import pandas as pd, csv

# Name the separator you found:
df = pd.read_csv("export.csv", sep=";", encoding="utf-8-sig")

# Or let the standard library guess the dialect:
with open("export.csv", newline="") as fh:
    dialect = csv.Sniffer().sniff(fh.read(4096))
    print(dialect.delimiter)

Two details are worth the extra keystrokes. Passing encoding="utf-8-sig" strips a byte order mark if the file has one, so the first column name does not gain a stray character. Reading with newline="" leaves line-ending handling to the CSV parser, which is what lets it treat a quoted line break as data instead of as the end of a record.

If you would rather not write it yourself, a script that walks a folder, detects the delimiter and re-saves every file as comma-separated is small enough to generate and check in an afternoon. That is the sort of job a coding subscription earns its keep on, and OpenCode Go is the one we use (link in the tools section below).

When the columns come back, check three things

A repaired file can still be wrong in ways that are not obvious on the screen. Worth two minutes:

1. Row count. The number of rows should match the original. Counting rows without opening the file in a spreadsheet is the fast way to confirm it, and it catches a bad conversion that quietly dropped the last line.
2. Leading zeros. Part numbers, ZIP codes and account numbers beginning with a zero are the classic casualty of a round trip through a spreadsheet, and keeping leading zeros explains how to stop it happening again.
3. Empty rows. Conversions sometimes leave blank lines behind. Removing blank rows takes a minute and prevents an off-by-one in every later calculation.

Once the file is clean, the columns are usually the beginning rather than the end of the job. If the point was to answer a question about the data, the analyzer will read the file and give you the answer and a chart without you having to build a pivot table first.

Frequently asked questions

Why does my CSV open in one column instead of separate columns?

The file uses a delimiter the application is not looking for. A common case is a semicolon-delimited export, which is what many European and Latin American systems write, opened in Excel on a machine whose list separator is a comma. The data is intact; only the separator is wrong. Change the delimiter to the one your app expects and the columns return.

How do I open a CSV file in Excel with columns automatically?

Use Data, then From Text/CSV, which shows you a preview and lets you set the delimiter and the file origin before anything is loaded. Double-clicking the file skips that dialog and lets Excel guess, and the guess is where the one-column import comes from. You can also change Windows list separator in Region settings so the guess matches your files, though that affects every other program too.

How can I tell which delimiter a CSV file actually uses?

Open it in a plain text editor and read the first line. A comma-delimited file shows commas between values, a semicolon file shows semicolons, and a tab file shows wide gaps where the tabs are. If the values are also wrapped in double quotes, the file follows RFC 4180. A CSV whose first characters are the bytes EF BB BF carries a UTF-8 byte order mark, which shows up as a stray character before the first column name.

What is the difference between a CSV and a tab-separated file?

Only the delimiter. TSV uses a tab between fields, CSV uses a comma, and both can quote fields that contain the delimiter or a line break. This matters because a tab file renamed to .csv still contains tabs, so a comma-based parser reads the whole line as a single value.

Can I fix a one-column CSV without Excel?

Yes, and it is usually faster. A browser-based delimiter converter reads the file, lets you pick the separator and the encoding, and writes out a normal comma CSV that opens correctly everywhere. Nothing is uploaded, so the file never leaves your machine.

Why is my CSV showing weird characters as well as one column?

Those are two separate faults. The single column comes from the delimiter; the strange characters come from text encoded as UTF-8 being read as a legacy single-byte encoding, or the reverse. Fix the encoding first, because a wrongly decoded file can hide or alter the delimiters themselves.

Does a CSV file with commas inside text values break the columns?

It can, and the result is the opposite symptom: too many columns rather than one. RFC 4180 requires a value containing a comma, a double quote or a line break to be wrapped in double quotes, with an internal quote written twice. If the export skipped the quoting, the parser splits text that was meant to stay together.

Is a one-column import a sign the file is corrupt?

Almost never. A corrupt file fails to open at all, or opens with replacement characters scattered through it. A file that opens cleanly with every line in column A is well-formed text with an unexpected separator, and the values are still there in full.