TL;DR / Summary: A CSV export that opens with one column containing everything is a delimiter problem, and a CSV that turns phone numbers into scientific notation is a formatting problem. Fix both at the source rather than repairing the sheet by hand.
The one-column problem
Opening a CSV and seeing every field crammed into column A means the spreadsheet guessed the delimiter wrong. This is common with European exports that use semicolons instead of commas.
Excel and similar tools default to the system list separator. A machine configured for a comma sees a semicolon-delimited file as one column.
The fix is to import rather than open. Use the data import wizard and specify the delimiter explicitly, rather than double-clicking the file.
- Comma — the standard, but ambiguous when fields contain commas
- Semicolon — common in European exports
- Tab — often used for pasted data
- Pipe — used where commas appear in content
Quoting and embedded delimiters
The real reason CSV is fragile is that fields contain the delimiter. A product name like “Smith, Jones and Co” breaks a naive comma split.
The CSV specification handles this with quoting: a field containing a comma is wrapped in double quotes. A field containing a quote has the quote doubled.
When writing a CSV from a script, apply quoting properly rather than joining values with commas. A one-line change to use a proper CSV library prevents an entire class of downstream bugs.
Newlines inside fields
Multi-line text in a field is legal CSV and catastrophic for line-based tools. A single record can span several physical lines.
Either strip newlines from field content before export, or use a parser that understands quoting. A line-based script that splits on newline will produce a corrupted record and no error message.
The formatting problem
Spreadsheets auto-convert values that look like numbers or dates. A product code of 00123 becomes 123. A phone number with a leading zero loses it. A date like 03/04/2026 becomes a date object resolved by locale.
These conversions are lossy, and they happen silently on open. The original file is fine; the spreadsheet’s interpretation is the problem.
- Leading zeros — set the column to text before opening, or prefix with an apostrophe
- Long numbers — identifiers above fifteen digits lose precision
- Dates — use ISO format in the export to avoid locale ambiguity
- Scientific notation — a symptom of a column being treated as numeric
Never open a raw CSV by double-clicking. Import it, with the types specified.
Exporting for other people
If the CSV is going to someone else, assume they will double-click it. That means the file should survive the default behaviour of the most common spreadsheet.
Use ISO 8601 dates. Avoid leading-zero identifiers or quote them. Keep the header row simple and free of punctuation that could be misread.
Consider an accompanying note describing the delimiter and the encoding. It takes one line and prevents the most common support question.
Encoding
Accented characters and non-Latin scripts turn into mojibake when the encoding is guessed wrong. UTF-8 is the correct default; some spreadsheet versions assume a local codepage.
A byte order mark can help a spreadsheet detect UTF-8, and it can also break naive parsers that read the first field literally. Choose deliberately rather than by accident.
Test with a file containing at least one accented name before sending it anywhere important.
Sanitising for spreadsheets
Spreadsheet applications interpret cells beginning with certain characters as formulas. A field starting with an equals sign can execute a formula when opened, which is a security problem as well as a data problem.
Prefix such values with an apostrophe, or quote them, or strip the leading character. This is worth doing for any CSV generated from user input.
Check fields starting with equals, plus, minus, and at signs. Those are the common formula prefixes.
A practical checklist
Before exporting: choose a delimiter, quote fields containing it, strip newlines, use ISO dates, and protect identifiers.
Before importing: specify the delimiter and the encoding, and set identifier columns to text.
For converting small datasets between formats without writing a script, master tool covers the common transformations, but the export rules above matter more than the tool.
CSV is a simple format with subtle rules. Respecting the rules costs a few minutes and saves an afternoon of manual repair in a spreadsheet that should never have needed it.
Large files and the import limit
Spreadsheets have row limits and memory limits, and a CSV that exceeds them fails in ways that look like corruption. Check the size before sending a file to someone who will open it in a spreadsheet.
If the file is large, offer a split export or a database file instead. Sending a file that cannot be opened is worse than sending none, because the failure looks like the recipient’s error.
Compression helps with transfer and not with import. A zipped CSV must be extracted before it can be read, which is a step people forget.
Validating before delivery
Open the file in a text editor before opening it in a spreadsheet. Check the header, the first record, and the last record, and confirm the field count matches everywhere.
A mismatch in field count is the clearest sign of a quoting bug, and it is visible in seconds with a proper parser or a quick script.
Send a small sample first when the file matters. A ten-row sample confirms the format, and it costs nothing compared with reissuing a broken export to a hundred recipients.