CSV Validation in Python Before a Website Import

A CSV looks normal in a spreadsheet, but after importing it, a product name ends up in the price column and the first SKU cannot be found. In this learning scenario, the store may not be responsible: the file uses semicolons, includes quoted fields or contains an encoding marker. CSV validation in Python helps separate format errors from business rules before any data is written.

We will use a small test catalogue containing fictional products. This is not a client case and does not connect to WordPress, OpenCart or a database. You need local Python 3, a text editor and permission to read the file. There are no third-party packages: we use the standard csv module. Work on a copy without personal data; do not upload genuine orders to public online validators.

Agree on the format first

Open a copy of the export in a text editor, not just a spreadsheet. Check the first line, delimiter and quotation marks around values. The .csv extension alone does not guarantee comma-separated fields. Our example has a simple contract: UTF-8, semicolons and three columns named sku, name and price in that order. These names are illustrative; use an agreed schema for a real import.

Create products.csv in a new test directory with the following content and save it as UTF-8. The prices are fictional learning values, not an AriZone offer. The semicolon inside the name is deliberately kept within quotation marks. It should remain part of one name rather than create a fourth column. This makes a useful control case for checking your parser.

sku;name;price
DEMO-01;"Notebook; blue";120.00
DEMO-02;Pen;35.00

Do not split records with split(‘;’): it does not account for quoting rules or line breaks inside fields. Do not globally replace every delimiter in the text, because that changes the names themselves. If the exporter has settings, record them alongside the test file. Automatic format detection can help an investigation, but it does not replace an agreed contract between two systems.

Run validation without importing

Create check_csv.py beside the CSV and insert the code. The path is calculated relative to the script’s location, so it does not depend on the terminal’s working directory. If your file has another name, replace products.csv; if the schema differs, change expected and the sku check together. Do not insert an arbitrary path to production data. The script opens the file for reading only and does not automatically repair anything.

import csv
from pathlib import Path

path = Path(__file__).with_name("products.csv")
expected = ["sku", "name", "price"]

with path.open(encoding="utf-8-sig", newline="") as file:
    reader = csv.DictReader(file, delimiter=";", strict=True)
    if reader.fieldnames != expected:
        raise ValueError("Unexpected CSV header")
    count = 0
    for row in reader:
        if None in row or any(value is None for value in row.values()):
            raise ValueError(f"Wrong field count near line {reader.line_num}")
        if not row["sku"].strip():
            raise ValueError(f"Empty SKU near line {reader.line_num}")
        count += 1
    print(f"Checked records: {count}")

Run the command in the directory containing the two test files. On a system where the interpreter has another name, use your verified local Python 3. The command installs no dependencies and requires no administrator permissions. For this example, the expected message is Checked records: 2. This is the learning test’s expected outcome, not a report of execution on your server or validation of a real catalogue.

python3 check_csv.py

The newline parameter is needed for correct CSV handling of line breaks. DictReader takes column names from the header; extra fields and missing values are handled separately. The syntax is described in the official csv documentation. The utf-8-sig encoding removes an initial UTF-8 BOM when present, but does not convert a file from another encoding. The codecs documentation explains this mechanism further.

Confirm that errors really stop the process

Make a separate copy of the learning file. Change price in the header to cost: the header error should occur. Then restore the header and add an extra field to the second product: the field-count check should fail. Separately, leave sku empty. After each test, undo that particular change so the next check is not masked by the previous error.

The reader.line_num value counts physical lines read by the parser, not necessarily the product number. A quoted field can contain a line break, so one value may occupy several physical lines. The message deliberately says “near line”. For a real tool, add a safe record identifier, but do not print complete private data or secrets into a log.

If UnicodeDecodeError occurs, do not add errors=’ignore’ to hide the problem. That can silently discard characters from names or keys. First establish the encoding with the export source and create a new copy in the agreed format. If the file cannot be found, check its name and placement beside the script. That is different from an incorrect delimiter and should not be addressed by changing the parser.

A valid format does not mean ready-to-import data

A successful run confirms only the checks included in this example. It does not detect duplicate SKUs, an incorrect currency, a negative price or a product absent from the store. Values remain strings; 35.00 does not automatically become a monetary amount. Separately agree on rules for decimal separators, currency, empty fields and uniqueness. Do not treat this exercise as a production-ready importer.

Before genuinely updating a catalogue, you need a test environment, a backup and a plan to restore the particular data changed. For local Python work, the article on a virtual environment can help. Keep small control CSV files alongside the tests: after an exporter changes, check both the correct file and deliberately damaged examples, not merely the absence of a crash.

Conclusion: safe validation begins with an explicit contract and stopping on a mismatch, not automatically repairing the whole file. Confirm structure first, then business rules, and only then plan the write operation. If your website’s exporter and importer interpret columns differently, AriZone can help agree on the format and check the integration on a test copy.