Fix a CSV that opens in one column
Choose the delimiter from the file, then check field boundaries before loading.
In this guide
A report can look like one long line of text in Excel even when the file is intact. The application may be expecting commas while the export uses semicolons or tabs. Choosing the separator explicitly is more reliable than repeatedly opening the same file or changing your computer's regional settings.
Start with three matching files
Download the comma, semicolon and tab examples. They contain the same three orders and six columns: order_id, customer, city, product, quantity and status. The first order has the product description Folder, blue. That comma belongs inside a value; it is not an extra column.
Inspect a copy in a plain text editor. The semicolon file begins with order_id;customer;city;product;quantity;status. Tabs may appear only as wide gaps. A filename ending in .csv does not prove that commas are the separators.
Import with an explicit separator
- Open a blank workbook. Use Data → From Text/CSV where available, or the Text Import Wizard in versions that expose it.
- Select the downloaded file and explicitly select UTF-8 as the encoding if the import dialog asks, then check multilingual text in the preview and loaded cells.
- Choose Comma for delimiter-comma.csv, Semicolon for delimiter-semicolon.csv, or Tab for delimiter-tab.tsv. In a wizard with checkboxes, clear the other delimiters.
- Examine the preview before loading. There should be six columns, and
Folder, blueshould occupy one cell. A wizard's text qualifier should be the double quote. - Set order_id to Text before completing the import. In Power Query, inspect automatically added type steps and remove or replace a numeric conversion before loading.
- Load into a new sheet. Save the working workbook separately from the original export.
Choose the separator from the source, not the filename
All three practice downloads describe the same table. Preview the comma, semicolon and tab versions separately with their matching separators. Accept a choice only when the headers occupy six columns and each description remains one field. A comma inside a quoted description is part of that value, not an extra column boundary. If the preview fails this check, cancel and change the import settings before loading.
Check the result
| Check | Expected result |
|---|---|
| Data rows, excluding the header | 3 |
| Columns | 6 |
| First order ID | 0001, stored as text |
| First product | Folder, blue, in one cell |
| Quantity total | 6 |
| Status of order 0002 | hold |
Repeat with the other two files. All three should produce the same logical table. If the comma inside the product creates another column, stop: the importer is not respecting quoted fields. Do not delete that comma to make the preview look tidy.
Compare the loaded table
| order_id as text | customer | city | product | quantity | status |
|---|---|---|---|---|---|
0001 | Ana | Lisbon | Folder, blue | 2 | ready |
0002 | Bo | Oslo | Notebook | 1 | hold |
0003 | Chen | Taipei | Pen | 3 | ready |
This is the expected sample table for comparison, not a screenshot. Confirm that the comma in Folder, blue stays inside the product cell.
Recognize the wrong delimiter
If order_id;customer;city remains part of one header cell, try the semicolon setting for the semicolon sample. If Folder, blue becomes two fields, the import settings or quoting are wrong. Do not proceed merely because several columns appeared: this sample must have exactly six columns, three data rows and a quantity total of 6.
When choosing a delimiter is not enough
A one-column worksheet that has already been edited may no longer contain the original quoting. Reimport the source instead of using Text to Columns on that altered sheet. If every record has different field counts, inspect the export for broken quotes or a changing schema. Quoted fields can also contain line breaks. In our Mac Text Import Wizard check, an embedded CRLF was incorrectly split into another row; do not accept that result or repair it by deleting the newline. Use an importer that preserves the logical records and verify its output.
Do not replace every semicolon with a comma in a text editor. That operation can change punctuation inside real descriptions. Delimiter selection tells a parser how to read boundaries without rewriting the underlying data.
Reference
Microsoft describes delimiter selection and the available text-import paths in Import or export text files.
Verification scope
On September 29, 2026, we imported the semicolon fixture with Power Query in Excel for Mac 16.112.2, selected the semicolon delimiter, changed order_id to Text before loading, and observed three records in six columns with 0001 preserved. The comma and tab fixtures have parser checks; their complete native import paths were not repeated for this article. Windows menu paths have not been tested. Check all values in the result table before using a different importer.