CSV Opens With Garbled Text or Lost Leading Zeros in Excel? Check UTF-8 BOM and Text Import First

When a downloaded CSV displays garbled non-ASCII characters, merges columns into column A, or drops leading zeros in Excel, close the sheet without saving so you do not overwrite the original file. Inspect the byte encoding, delimiter, and ID columns first, then import via Data > From Text/CSV as UTF-8 (65001) with ID columns set to Text.

First safety rule: never press Ctrl+S on a broken CSV view

If Excel opens a UTF-8 file using a legacy regional code page or converts 00124 into 124 and an 18-digit tracking number into 9.40011E+17, saving writes those damaged values back to disk permanently.

Scope and verification date: Checked November 14, 2026 KST on Windows 11 with Python 3.14.3 standard library (csv, codecs, pathlib). Twelve automated unit tests and the CLI output below were executed locally.

Quick diagnostic route

1. Match the exact symptom to its root cause
2. Choose the right fix: UTF-8 BOM copy vs. Data > From Text/CSV
3. Inspect encoding and ID truncation risks in Python before opening Excel
4. Verify the imported sheet before saving

1. Separate three different CSV import failures

When double-clicking a .csv file in File Explorer, desktop spreadsheet apps make assumptions about byte encoding, column separators, and numeric types before showing you a single dialog box. Distinguishing the three failure modes prevents applying a fix that solves one problem while breaking another:

Table 1. Three common CSV symptoms when opening files in Excel
Observed symptom	Technical cause and safe fix
Korean, accented, or CJK names turn into `Ã©` or broken symbols	The file is UTF-8 without a 3-byte Byte Order Mark (`EF BB BF`). Import with 65001: Unicode (UTF-8) or write a separate `utf-8-sig` copy.
Every column is jammed into Column A	Your regional list separator (such as `;`) differs from the file delimiter (`,`). Select the matching delimiter in Data > From Text/CSV.
`00124` becomes `124`, or an 18-digit ID ends in `000`	Automatic numeric conversion strips leading zeros and enforces IEEE 754 15-digit significant precision. A BOM does not stop this; import the column as Text.

2. When a UTF-8 BOM copy is enough—and when it is not

Python’s standard codecs documentation distinguishes plain utf-8 from utf-8-sig, which prepends the three-byte UTF-8 BOM (EF BB BF). If your CSV only contains names, descriptions, and standard decimal prices without leading-zero codes or 16+ digit integer identifiers, writing a separate utf-8-sig copy lets Excel detect UTF-8 on double-click.

However, if any column holds postal codes (01234), employee or order IDs (00124), or 16-to-20-digit tracking and card identifiers, Microsoft’s official Text/CSV import guide recommends opening a blank workbook, choosing Data > From Text/CSV, setting File Origin to 65001: Unicode (UTF-8), and setting those identifier columns to Text in Power Query before loading.

3. Run a read-only Python preflight check before opening the file

Save the sample CSV below as sample.csv (plain UTF-8 without BOM). It includes both non-ASCII characters (지수 Kim, Renée Laurent), leading-zero order IDs (00124), and 18-digit tracking numbers:

order_id,customer_name,tracking_code,amount_usd
00124,지수 Kim,940011189922384716,49.50
00125,Renée Laurent,940011189922384717,120.00

Save the diagnostic script below as check_csv_import.py. It uses only the Python standard library (csv.reader and pathlib), never modifies the input file, and reports both encoding/BOM status and any columns at risk of silent numeric truncation:

import csv
import json
import sys
from pathlib import Path


def detect_encoding_and_text(raw_bytes):
    if raw_bytes.startswith(b'\xef\xbb\xbf'):
        return 'utf-8-sig', True, raw_bytes.decode('utf-8-sig')
    if raw_bytes.startswith(b'\xff\xfe') or raw_bytes.startswith(b'\xfe\xff'):
        return 'utf-16', True, raw_bytes.decode('utf-16')
    try:
        return 'utf-8', False, raw_bytes.decode('utf-8')
    except UnicodeDecodeError as exc:
        raise ValueError('file is not valid UTF-8 or UTF-16; specify source encoding before converting') from exc


def inspect_csv(input_path, delimiter=','):
    if delimiter not in {',', ';', '\t'}:
        raise ValueError(f'unsupported delimiter {delimiter!r}')
    path = Path(input_path)
    raw = path.read_bytes()
    if not raw:
        raise ValueError('CSV file is empty')

    encoding, has_bom, text = detect_encoding_and_text(raw)
    lines = text.splitlines()
    reader = csv.reader(lines, delimiter=delimiter, strict=True)
    rows = list(reader)
    if len(rows) < 2:
        raise ValueError('CSV must contain a header row and at least one data row')

    header = rows[0]
    width = len(header)
    if width < 2 or any(not col.strip() for col in header):
        raise ValueError('header must have at least two non-empty column names')

    leading_zero_cells = []
    long_integer_cells = []
    non_ascii_rows = 0

    for row_index, row in enumerate(rows[1:], start=2):
        if len(row) != width:
            raise ValueError(f'row {row_index} has {len(row)} columns; expected {width}')
        if any(ord(ch) > 127 for cell in row for ch in cell):
            non_ascii_rows += 1
        for col_name, cell in zip(header, row):
            val = cell.strip()
            if len(val) > 1 and val.startswith('0') and val.isdigit():
                leading_zero_cells.append({'row': row_index, 'column': col_name, 'value': val})
            elif val.isdigit() and len(val) >= 12:
                long_integer_cells.append({'row': row_index, 'column': col_name, 'value': val})

    return {
        'file': path.name,
        'detected_encoding': encoding,
        'has_bom': has_bom,
        'delimiter': 'TAB' if delimiter == '\t' else delimiter,
        'columns': width,
        'data_rows': len(rows) - 1,
        'non_ascii_data_rows': non_ascii_rows,
        'leading_zero_risk_count': len(leading_zero_cells),
        'long_integer_precision_risk_count': len(long_integer_cells),
        'leading_zero_examples': leading_zero_cells[:5],
        'long_integer_examples': long_integer_cells[:5],
        'excel_safe_action': (
            'Use Data > From Text/CSV and set Text column type for IDs'
            if (leading_zero_cells or long_integer_cells)
            else ('Add UTF-8 BOM copy or use Data > From Text/CSV (65001 UTF-8)' if (non_ascii_rows > 0 and not has_bom) else 'Safe to open')
        ),
    }


def write_utf8_bom_copy(input_path, output_path, delimiter=','):
    src = Path(input_path).resolve()
    dst = Path(output_path).resolve()
    if src == dst:
        raise ValueError('refusing to overwrite original CSV file; provide a distinct output path')
    report = inspect_csv(src, delimiter=delimiter)
    _, _, text = detect_encoding_and_text(src.read_bytes())
    dst.write_bytes(text.encode('utf-8-sig'))
    report['bom_copy_written'] = dst.name
    return report


if __name__ == '__main__':
    try:
        if len(sys.argv) == 2:
            result = inspect_csv(sys.argv[1])
        elif len(sys.argv) == 4 and sys.argv[2] == '--write-bom-copy':
            result = write_utf8_bom_copy(sys.argv[1], sys.argv[3])
        else:
            raise ValueError('usage: python check_csv_import.py <input.csv> [--write-bom-copy <output.csv>]')
        print(json.dumps(result, ensure_ascii=False, indent=2))
    except (ValueError, OSError, csv.Error) as err:
        print(f'ERROR: {err}', file=sys.stderr)
        sys.exit(2)

Run the check from your terminal:

python check_csv_import.py sample.csv

Observed output on Windows 11 (Python 3.14.3, exit code 0):

{
  "file": "sample.csv",
  "detected_encoding": "utf-8",
  "has_bom": false,
  "delimiter": ",",
  "columns": 4,
  "data_rows": 2,
  "non_ascii_data_rows": 2,
  "leading_zero_risk_count": 2,
  "long_integer_precision_risk_count": 2,
  "leading_zero_examples": [
    {
      "row": 2,
      "column": "order_id",
      "value": "00124"
    },
    {
      "row": 3,
      "column": "order_id",
      "value": "00125"
    }
  ],
  "long_integer_examples": [
    {
      "row": 2,
      "column": "tracking_code",
      "value": "940011189922384716"
    },
    {
      "row": 3,
      "column": "tracking_code",
      "value": "940011189922384717"
    }
  ],
  "excel_safe_action": "Use Data > From Text/CSV and set Text column type for IDs"
}

4. Confirm the result before saving or sharing

Check non-ASCII text: Verify that Korean or accented characters render cleanly without replacement boxes or Latin-1 artifacts.
Check left-aligned Text IDs: Confirm that 00124 keeps its leading zeros and that 18-digit tracking numbers match the raw CSV text digit-for-digit rather than ending in 000.
Keep the raw CSV read-only: Save your analyzed workbook as a separate .xlsx file so the original .csv export remains untouched for scripts and audit logs.

Appendix: verification and related guides

All 12 unit tests in test_check_csv_import.py (UTF-8 without BOM detection, UTF-8-SIG detection, UTF-16 BOM detection, invalid byte rejection, empty/header-only rejection, delimiter mismatch detection, row-width validation, overwrite protection on --write-bom-copy, and CLI exit codes 0 and 2) passed on Windows 11 with Python 3.14.3.

For related hands-on Python and data-validation guides on Deoks, see:

Build a Memory-Earnings Comparison Sheet Without Mixing Units — normalizing tabular CSV inputs and missing values in Python.
Calculate LLM p50, p95 and SLO Goodput from Request Logs in Python — strict schema validation for log files before aggregation.
Build a DRAM Price-Change Calculator in Python — validating CSV columns and numeric precision with standard-library Python.

이 블로그 검색

Deoks