Why Excel opens CSV incorrectly: encoding, delimiters and zeros

Start with the symptom

You download a customer export and Excel shows mangled city names. A second file lands entirely in column A. A third looks fine until customer ID 00123 becomes 123. These symptoms point to different decisions: how bytes become characters, how records become columns, and how cell contents become typed values. Renaming a file or running a global replacement cannot settle all three. Keep an untouched copy of the export before experimenting. Open that copy in a text editor and compare its contents with the spreadsheet. A correct value in the original file means the import process still has a chance to preserve it.

What UTF-8 and a BOM actually change

CSV is text containing records and fields, rather than an Excel workbook. An encoding specifies which bytes represent a character. If the importing application chooses a different encoding, intact bytes can display as the wrong letters. A UTF-8 byte order mark consists of the three bytes EF BB BF at the beginning of a file. Microsoft documents it as a way to help Excel open UTF-8 CSV files directly, with text import as the alternative. That mark does not describe the field separator, the meaning of a date, or whether an identifier is text. Treat it as an encoding hint with a particular consumer in mind.

Adding a BOM cannot repair text that was already decoded incorrectly and then saved over the original. If the file now contains literal question marks, the missing letters may no longer be recoverable from that file. Return to the source export, database or backup. When the producer uses an older encoding such as Windows-1254, decode that source correctly before exporting UTF-8. Do not repeatedly change encoding settings and overwrite the evidence. A successful encoding conversion also leaves a delimiter mismatch untouched. Work on a small sample, verify meaningful values, and only then apply the confirmed settings to the full export.

Import deliberately in Excel

In desktop Microsoft 365, start with an empty workbook and use Data → From Text/CSV to choose the original file. In the preview, select UTF-8 as the file origin when the export is UTF-8. Inspect actual names such as İzmir, Iğdır and Şırnak rather than checking only an ASCII header. Menu wording and available import features vary by platform and Excel version. The useful principle is to review the decoding, field boundaries and types before loading. Double-click opening delegates those decisions to defaults that may suit a different export. If the preview has the wrong number of columns, stop there and correct the separator.

For identifiers, choose Transform Data and inspect the Power Query steps. If an automatically generated Changed Type step has already converted the field to a number, remove or adjust that conversion while the source text is still available. Then assign Text to customer codes, phone numbers and similar fields. Changing the already converted number 123 into a text cell does not recreate 00123. Dates and decimal values need their own locale decisions: a decimal comma is not a column separator in every dialect. Save the verified result as a workbook when you need to retain the column types for the next person who opens it.

Comma, semicolon or tab?

Despite the name, real CSV exports also use semicolons or tabs. In locales with decimal commas, semicolons are common, but a language setting does not prove which dialect a particular producer chose. Inspect a few records and check that the preview separates the expected headings. Select the delimiter used by the file rather than the one implied by its extension. A global comma-to-semicolon replacement is unsafe because commas can belong to addresses, notes or decimal amounts. Changing a delimiter means parsing records and writing them again with another delimiter. It is a structural conversion, not a search-and-replace operation.

01id;city;note0200123;İzmir;"Istanbul; office"0300456;Iğdır;"He said ""hello"""

The semicolon inside the note is data because the entire field is quoted. A quote inside a quoted field is represented by two quotes. Fields can also contain line breaks, so splitting the text into lines and then splitting each line on the delimiter is not a general CSV parser. Physical line counts can differ from record counts for the same reason. Read an exported file back through a CSV parser with the intended dialect. Compare the field count and important values, including punctuation and embedded quotes. A small round-trip check catches structural mistakes that can stay invisible in a spreadsheet with many columns.

A reproducible UTF-8 BOM export

This Python example uses only the standard library. The utf-8-sig encoding writes the BOM and consumes a leading BOM while reading. Passing newline leaves newline handling to the CSV module, and the chosen line terminator produces CRLF records. Identifiers start as strings. The program writes the sample and reads it back to compare its contents exactly, including zeros and embedded quotes. This establishes a file round trip in Python; it does not establish how a particular Excel installation renders it. Excel was not opened for this article and there is no claimed application screenshot. Import the resulting file in your own Excel version to verify that separate step.

01import csv02from pathlib import Path03 04rows = [05    ["id", "city", "note"],06    ["00123", "İzmir", "Istanbul; office"],07    ["00456", "Iğdır", 'He said "hello"'],08]09with open("customers.csv", "w", encoding="utf-8-sig", newline="") as f:10    csv.writer(f, delimiter=";", lineterminator="\r\n").writerows(rows)11 12raw = Path("customers.csv").read_bytes()13print("BOM:", raw[:3].hex(" "))14with open("customers.csv", encoding="utf-8-sig", newline="") as f:15    restored = list(csv.reader(f, delimiter=";"))16print("rows:", len(restored), "columns:", len(restored[0]))17print("first record:", restored[1])18assert restored == rows19print("round trip: OK")

01BOM: ef bb bf02rows: 3 columns: 303first record: ['00123', 'İzmir', 'Istanbul; office']04round trip: OK

Leading zeros and long identifiers

An identifier is not necessarily a number you should calculate with. Your system may distinguish 00123 from 123. Surrounding a CSV field with quotes protects its field boundaries; it does not instruct Excel to assign the Text type. Explicitly import phone numbers, postal codes and long order identifiers as text. Long digit sequences can also lose precision when converted into spreadsheet numbers. A custom number format that displays padding changes the appearance rather than proving the underlying identifier was preserved. Compare with the original string in the export, and keep the type as text in downstream APIs and databases whenever the field represents a label rather than a quantity.

If zeros have disappeared, first inspect the original export. Reimport if it still contains 00123. If it now contains only 123, the value alone cannot tell you how many zeros were lost. Padding is justified only when an independently confirmed business rule defines a fixed width. Without that rule it is a guess. CSV also carries no spreadsheet date formatting, so a value such as 03/04/2026 needs an agreed interpretation. Include encoding, delimiter, column types and a representative sample in the export contract. That short contract gives both the producer and the importer a concrete standard to check instead of relying on personal spreadsheet settings.

Account for spreadsheet formula injection

User-controlled notes and company names introduce another concern when exported to a spreadsheet: certain leading characters can make a cell interpreted as a formula. CSV quoting does not make that interpretation safe. Escaping field separators and controlling formula evaluation are separate layers. Review untrusted values beginning with equals, plus, minus or @, including relevant leading control characters. Blindly prefixing every value with an apostrophe can change legitimate numeric data, so define an export policy for your actual consumers and field types. Test it in the spreadsheet programs that receive the file. The example above demonstrates encoding and structure; it is not a complete sanitizer for arbitrary user data.

Move from JSON to a table with a clear contract

When the source is an API response, first check its syntax with the JSON formatter. Then use the JSON ↔ CSV converter to move records into columns, matching the export delimiter and BOM choice to the receiving application. Decide what nested objects mean before flattening them: a JSON value inside a cell, a set of flattened fields, and a separate child table are different models. Arrays converted casually to display strings may not reconstruct the original JSON on the return trip. Keep identifier fields as strings in the source response too; the export stage cannot recover precision already lost earlier in the pipeline.

The final check should cover more than readable accents. Compare expected record counts, headings, column counts, quoted delimiters, empty fields and several long identifiers. When handing the file to someone else, state its contract briefly: for example, UTF-8 with BOM, semicolon delimiter, and id imported as Text. If the only problem involves changing İ/i or ı/I between upper and lower case, investigate language rules as well as encoding; the Turkish I article explains that separate issue. A repeatable agreement between the exporter and importer is more useful than a spreadsheet that needs the same manual repairs after every download.