Checking CSV Identifier Columns Before a Spreadsheet Import

A product code such as 00123 is text, even though it looks like a number. Check selected CSV columns for leading zeros and long digit strings before deciding how to import them into a spreadsheet.

You'll need Python 3.10 or later, a plain-text editor and a command window. No extra packages are required. This check reads the file; it never rewrites cells or opens a spreadsheet.

1. Make a practice export

CSV means comma-separated values. Quotes allow commas and line breaks inside a field. Save this as sample.csv in UTF-8, the text encoding used here:

Code,Note
00123,first item
1234567890123456,second item
123.45,not an integer string

Choose Code as an identifier column: its values name things rather than amounts to calculate. The program does not guess which columns are identifiers. You name them in the command.

Microsoft documents that Excel can remove leading zeros when converting numerical text, and that numerical precision is limited to 15 digits. The CSV text itself still contains the characters; interpretation during import or later re-saving is the risk. A warning here is a prompt to review the import settings, not proof that your chosen importer will change the value.

2. Save the checker

Save this as CheckIdentifiers.py beside the export:

import csv as Csv
import io as Io
import json as Json
import sys as Sys
from pathlib import Path


def CheckIdentifiers(FileName, Columns):
    if len(Columns) != len(set(Columns)):
        raise ValueError("choose each column once")
    with Path(FileName).open("rb") as Input:
        Data = Input.read(1048577)
    if len(Data) > 1048576:
        raise ValueError("input exceeds 1 MiB")
    Csv.field_size_limit(65536)
    Reader = Csv.reader(Io.StringIO(Data.decode("utf-8-sig"), newline=""), strict=True)
    try:
        Header = next(Reader)
    except StopIteration:
        raise ValueError("empty input") from None
    if not Header or any(not Name.strip() for Name in Header):
        raise ValueError("blank header name")
    if len(Header) != len(set(Header)):
        raise ValueError("repeated header name")
    if any(Name not in Header for Name in Columns):
        raise ValueError("selected column not found")
    Indices = [Header.index(Name) for Name in Columns]
    Warnings = []
    Records = 0
    for Records, Row in enumerate(Reader, start=1):
        if Records > 1000:
            raise ValueError("more than 1000 data records")
        if len(Row) != len(Header):
            raise ValueError(f"record {Records} has wrong field count")
        for Index in Indices:
            Value = Row[Index]
            if not Value or any(Character not in "0123456789" for Character in Value):
                continue
            Risks = []
            if len(Value) > 1 and Value.startswith("0"):
                Risks.append("potential leading-zero conversion")
            if len(Value) > 15:
                Risks.append("potential numeric precision loss")
            if Risks:
                Warnings.append({"Record": Records, "EndingLine": Reader.line_num,
                                 "Column": Index + 1, "Risks": Risks})
    Report = {"RecordsChecked": Records,
              "SelectedColumns": [Index + 1 for Index in Indices],
              "FlaggedCells": len(Warnings), "Warnings": Warnings}
    print(Json.dumps(Report, indent=2, ensure_ascii=True))
    return 1 if Warnings else 0


def Main():
    if len(Sys.argv) < 3:
        print("Usage: python3 CheckIdentifiers.py input.csv COLUMN [COLUMN ...]", file=Sys.stderr)
        return 2
    try:
        return CheckIdentifiers(Sys.argv[1], Sys.argv[2:])
    except (OSError, UnicodeError, ValueError, Csv.Error) as Error:
        print(f"Check stopped: {Error}", file=Sys.stderr)
        return 2


if __name__ == "__main__":
    raise SystemExit(Main())

The checker parses the default comma/double-quote CSV format. Header names must be nonblank and unique, and each selected name must match a header exactly. Code and code are different. A CSV-reader error or a record with the wrong number of fields stops the whole check. Python's reader reports some parsing errors, but this program does not validate every CSV format or quoting rule. For example, it accepts a quote inside an unquoted field such as a"b.

Only nonempty strings made entirely of ordinary digits 0 to 9 are checked. Two patterns create potential warnings:

More than one digit beginning with 0, such as 00123.
More than 15 digits, such as 1234567890123456.

One cell can have both warnings. Single 0, decimal amounts, negative amounts, dates, formulas, non-English digit characters and strings with spaces are not flagged by these rules. Nothing is trimmed or converted. No warning is not a promise of safe import; this is a deliberately narrow check.

The file limit is 1 MiB (1,048,576 bytes), with at most 1,000 data records and 65,536 characters per parsed field. UTF-8 with an initial byte-order mark, an optional encoding marker, is accepted. Other encodings stop rather than being silently replaced. A semicolon-separated file can appear to be one column, so confirm the separator and header before relying on the results.

3. Run it on the named column

Open a command window in the folder:

python3 CheckIdentifiers.py sample.csv Code

Use your installation's Python 3.10-or-later command if it is not called python3. The output is:

{
  "RecordsChecked": 3,
  "SelectedColumns": [
    1
  ],
  "FlaggedCells": 2,
  "Warnings": [
    {
      "Record": 1,
      "EndingLine": 2,
      "Column": 1,
      "Risks": [
        "potential leading-zero conversion"
      ]
    },
    {
      "Record": 2,
      "EndingLine": 3,
      "Column": 1,
      "Risks": [
        "potential numeric precision loss"
      ]
    }
  ]
}

Record numbers start at the first data record, excluding the header. Column numbers start at 1. EndingLine is the physical line where the CSV record ends; a quoted multiline field still belongs to one record. The report uses JSON, a text format for named values and lists, and omits cell values and header names. Positions and counts can still reveal information, so keep the report private.

An exit code is the small result number a program returns. This checker returns 0 after a complete check with no flags, 1 after a complete check with potential warnings, and 2 when the check stops. It prints the report only after the reader finishes without an error and every record has the expected field count. A late CSV-reader error or wrong field count produces an error, not a partial clean report. A header-only file completes with zero records; an empty file stops.

If you want to choose the columns on screen rather than type their names, my CSV identifier import check checks these two warning patterns on your device. Its CSV reader is stricter than Python's, so unusual quoting may give different results.

4. Check two columns and a multiline record

Save this new practice file as two-columns.csv:

Code,OtherCode,Note
00123,9876543210987654,"one,two"
123,0004,"first line
second line"

Name both identifier columns:

python3 CheckIdentifiers.py two-columns.csv Code OtherCode

The report checks two records and flags three cells. Record 1 has a leading-zero warning in column 1 and a precision warning in column 2. Record 2 has a leading-zero warning in column 2. Its EndingLine is 4, because its quoted note spans physical lines 3 and 4. The note's comma and newline do not create extra records or identifier columns.

Now misspell OtherCode as Othercode in the command. The check stops with selected column not found; it does not guess a nearby name. Restore the command, then remove the comma between 00123 and the next value in the practice file. The wrong field count stops the check with no JSON report. Restore the file before using it again.

5. Review the spreadsheet import, not the CSV values

Keep the original export. Microsoft's leading-zero and large-number guidance explains text-column import through Get & Transform (Power Query). It also describes Automatic Data Conversions settings for Microsoft 365 and Excel 2024 on Windows and Mac; availability depends on your version.

Choose identifier columns as text during import, inspect the preview, and compare a few imported values with the original text before using or re-saving the workbook. If a conversion step has already changed a value, return to the original export and correct the import steps. Formatting a damaged number afterwards cannot recover digits or zeros that were lost.

The checker does not configure Excel, test another importer or prove its behavior. It does not add apostrophes, repair dates, validate account numbers or recover lost data. Do not infer that a 16-digit value is wrong, or that a 15-digit value is safe: the column's purpose and import settings matter.

The program passed 31 command-line checks on Python 3.10.12, covering both warning patterns, selected columns, multiline quoting, malformed records, encoding, size/field/record limits and missing input. Every file test confirmed unchanged bytes. Tests also checked that reports contain no cell values and that a stopped run prints no report.