CSV delimiter problems in Excel

CSV means comma-separated values in the narrow sense, and RFC 4180 describes comma-separated records with double quotes for fields that contain commas, quotes, or line breaks. Real files often use semicolons, tabs, pipes, or locale-specific separators. The .csv extension alone does not declare which delimiter is present.

Excel has several open paths. Double-clicking a CSV can use system regional settings and older assumptions. Importing through Data From Text/CSV gives more control. This is why the same file can open correctly on one computer and as a single column on another.

In locales where comma is used as the decimal separator, semicolon is often used as the list separator. A row such as name;amount may be normal in that environment. On a US English machine, Excel may expect commas and treat the whole row as one field.

The reverse also happens: name,amount can split incorrectly if the import path expects semicolons. Do not fix this by global search and replace unless you know commas never appear inside quoted fields. A proper CSV parser understands quotes; a text replacement does not.

A field containing a delimiter should be quoted, such as "Smith, Ana",42. A literal quote inside a quoted field is normally doubled: "He said ""yes""". Some exporters get this wrong, and Excel may then shift columns after the first bad row.

Embedded newlines inside quoted fields are legal in common CSV dialects. They confuse simple line-based scripts that assume one record equals one physical line. If row counts differ between tools, check for quoted line breaks before assuming data loss.

Open the file in a plain text editor and inspect the first few records. Look for the delimiter, quote character, header row, and whether rows have a consistent field count. Avoid saving from the editor unless you know it will preserve encoding and line endings.

Command-line checks can help. head -5 data.csv shows the first rows, while file -I data.csv may report a character set. If the delimiter is tab, the extension should often be .tsv, but many systems still label it CSV.

Use Excel’s import flow and explicitly choose delimiter and encoding. For automated work, parse with a CSV library and write the target dialect. In Python, pandas.read_csv("in.csv", dtype=str, sep=";") keeps fields as text and selects semicolon as the delimiter.

If the destination is Excel, XLSX may be safer than CSV because the workbook stores cell boundaries without relying on delimiter guesses. CSV remains useful for simple exchange, but it needs explicit import choices.

If you publish CSV files for many countries, document the dialect instead of relying on the extension. State delimiter, quote character, encoding, newline style, and whether the first row is a header. That small note prevents many Excel-specific support cases.

For recurring imports, save an import recipe rather than asking users to remember settings. Excel, Power Query, pandas, and database loaders all support explicit delimiter choices. The right fix is repeatable parsing, not editing the raw file until one machine opens it.

A final CSV handoff should include a tiny sample row in documentation. Recipients can compare their import preview against the sample before loading thousands of rows into the wrong columns.