Validate Identifier Columns Before Trusting a CSV Export

Disclosure: Prepared with AI assistance for CommentTok, whose editorial team is affiliated with TokViewer. All sample records and identifiers are fictional.

Before using an identifier column for joins or deduplication, check the values actually stored in the CSV. A column can contain missing IDs, exponent notation, unexpected characters, or repeated strings while still looking tidy in a spreadsheet.

This tutorial builds a read-only Python validator for those observable problems. It cannot establish that an ID is authentic, detect every rounded value, or reconstruct lost digits. A passing result means only that this file passed the checks we defined.

Define the contract first

Use Python 3.9 or later, a terminal, and a comma-delimited UTF-8 CSV with a header. No packages, accounts, or network access are needed. Pick one required identifier column whose values should be unique within this file. Additional columns are allowed.

Our contract accepts only nonempty strings of ASCII digits. Leading zeros stay significant: 00042 and 42 are different identifiers. Whitespace-only values are blank; spaces around digits are invalid. We do not convert values to integers or floats, remove punctuation, or silently trim IDs into matches.

Uniqueness is a separate business rule. A repeated video ID is normal when many comments belong to one video. An optional parent ID may legitimately be blank. Do not apply this required, unique-column contract to either without changing the policy. For IDs unique only within a source, validate a properly scoped file or implement a composite key.

Save the validator

Save the complete program as validate_ids.py. Python's official CSV reader documentation specifies string fields by default and recommends opening files with newline="". Those choices keep numeric-looking identifiers as text and support quoted commas and embedded newlines.

import argparse
import csv
import re
from pathlib import Path

SCIENTIFIC = re.compile(r"[+-]?(?:[0-9]+(?:\.[0-9]*)?|\.[0-9]+)[eE][+-]?[0-9]+")


def validate(path, column):
    issues = []
    seen = {}
    count = 0
    with Path(path).open(encoding="utf-8-sig", newline="") as source:
        reader = csv.reader(source, strict=True)
        header = next(reader, None)
        if not header or any(not name.strip() for name in header):
            raise ValueError("Expected nonempty column names")
        if len(set(header)) != len(header) or column not in header:
            raise ValueError("Expected unique headers and the requested column")
        position = header.index(column)
        for count, row in enumerate(reader, 1):
            if len(row) != len(header):
                raise ValueError(f"Record {count}: wrong field count")
            value = row[position]
            if not value.strip():
                reason = "blank"
            elif SCIENTIFIC.fullmatch(value.strip()):
                reason = "scientific_notation"
            elif not re.fullmatch(r"[0-9]+", value):
                reason = "non_digit"
            else:
                reason = None
            if reason:
                issues.append(f"record {count}: {reason}")
            if value.strip():
                if value in seen:
                    issues.append(f"record {count}: duplicate of record {seen[value]}")
                else:
                    seen[value] = count
    if count == 0:
        raise ValueError("Expected at least one data record")
    return count, issues


def main():
    parser = argparse.ArgumentParser()
    parser.add_argument("csv_file", type=Path)
    parser.add_argument("column")
    args = parser.parse_args()
    try:
        count, issues = validate(args.csv_file, args.column)
    except (OSError, UnicodeError, ValueError, csv.Error) as error:
        parser.exit(2, f"Input error: {error}\n")
    for issue in issues:
        print(issue)
    print(f"{count} records; {len(issues)} issues")
    return 1 if issues else 0


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

The reader uses the default comma-separated dialect. Duplicate headers, missing columns, empty datasets, and incorrect record widths are input errors. Python documents strict parsing as raising an error on bad CSV input; our explicit header and width checks add the tutorial's structural rules. This is not a universal validator for every CSV dialect.

Record numbers count parsed data records, starting after the header. They are not physical line numbers: one quoted field can span several lines. A completely empty line fails the width check rather than disappearing from the audit.

The exponent pattern recognizes conventional scientific notation, including signed exponents. Other invalid spellings still fail the digit check. Exact nonblank duplicates are reported even when the repeated value is malformed; a row can therefore generate two issues. Blank values receive only the blank finding.

Run an explicitly fictional fixture

Create fictional-ids.csv exactly as shown. These invented numbers have no asserted relationship to real platform comments.

comment_id,note
9000000000000000001,fictional baseline
,fictional missing value
abc123,fictional letters
9.000000000000001e18,fictional exponent
9000000000000000001,fictional repeated value
9000000000000000000,fictional rounded-looking value
00042,fictional leading zeros
42,fictional distinct string

Run the validator directly, without redirecting output into the input file:

python3 validate_ids.py fictional-ids.csv comment_id

Expected terminal output:

record 2: blank
record 3: non_digit
record 4: scientific_notation
record 5: duplicate of record 1
8 records; 4 issues

The exit status is 1 because findings exist. Status 0 means no findings under this contract; status 2 means an input or parsing error prevented a complete report. The program finishes reading before printing findings, so malformed input does not produce a misleading partial success summary.

Record 6 deliberately passes. Its trailing zeros could be genuine, or its digits could have changed earlier. The validator has no evidence to choose between those explanations. Records 7 and 8 also pass because exact string comparison preserves their different spellings.

Test the boundary, not just the happy path

Replace one ID in a disposable fixture with whitespace, 12x, or 1E+18 and expect blank, non-digit, and scientific-notation findings respectively. Repeat an existing ID and expect a duplicate referencing its first record. Repeating malformed text should report both its format problem and its duplication.

Also test quoted multiline notes, an initial UTF-8 byte-order mark, missing headers, short rows, and an unterminated quoted field. Compare the input bytes before and after execution: this program opens the CSV only for reading. It prints diagnostics without writing a cleaned file or deleting questionable records.

Local editorial validation exercised these cases, subprocess exit statuses, and the intentional inability to distinguish two different, unique digit-only IDs. The script retains a dictionary of seen values and a list of findings, so memory grows with the file. Keep this minimal version for manageable exports.

Decide what a passing check permits

A clean report is a useful gate before attempting a join, not proof that the join keys survived every previous application. To establish preservation, compare exact ID strings with an original immutable source retained before conversion, matching records through an independently reliable association. Matching on the suspect ID alone is circular; row position is unsuitable if ordering changed.

For the spreadsheet handoff, the related guide to keeping TikTok comment IDs intact in Excel addresses preserving identifiers during that workflow. Run this check on the actual exported bytes, not merely the displayed cells.

If the original is unavailable, record that identity preservation is unverified. Do not expand exponent notation and label the result recovered, or infer missing digits from neighboring rows. Preserve the suspect input, investigate findings against the source, and write any justified corrections to a separate derivative.