A CSV file can be perfectly readable and still contain unusable data. A product quantity might be negative, an identifier might appear twice, and a date might be ambiguous. Importing every readable line turns those uncertainties into database records that other parts of the application may trust.

This workflow separates parsing, validation, writing and reconciliation. It uses a small product list and an application-side validator. The database write is deliberately a separate step: validate the file first, inspect what will be accepted, then use the documented OriginChainDB row or SQL interface.

1. Write an input contract

For this demonstration, each row needs an id, name and quantity. The ID must be nonempty and unique within the file. Quantity must be a nonnegative whole number written with ASCII digits and no thousands separator. This is a chosen import convention, not a universal number format.

Do not silently reinterpret a value such as 1,000. Depending on the export convention, that punctuation may represent grouping or a decimal separator. Either require an agreed format or implement an explicitly selected locale conversion before validation.

Use synthetic products and a fresh demo table. Keep the original input file intact so rejected rows can be corrected without losing the evidence of what arrived.

2. Build a small failing fixture

id,name,quantity
p1,Notebook,12
p2,Blue pen,8
,Envelope pack,4
p1,Notebook duplicate,9
p3,Stapler,-2

The intended result is two accepted rows and three rejected rows. The missing identifier, duplicate ID and negative quantity are different failures and should have different explanations. Do not reduce all three to an unhelpful “invalid file” message.

3. Validate before opening the write path

The following Python uses the standard library. Save the fixture as products.csv and run the validator locally:

import csv
import json
import re

accepted, rejected, seen = [], [], set()
with open('products.csv', newline='', encoding='utf-8-sig') as source:
    reader = csv.DictReader(source)
    if reader.fieldnames != ['id', 'name', 'quantity']:
        raise ValueError('Expected headers: id,name,quantity')
    for row_number, row in enumerate(reader, start=2):
        key = (row.get('id') or '').strip()
        name = (row.get('name') or '').strip()
        quantity = (row.get('quantity') or '').strip()
        reason = None
        if None in row or any(value is None for value in row.values()):
            reason = 'Unexpected number of columns'
        elif not key or not name:
            reason = 'Missing ID or name'
        elif key in seen:
            reason = 'Duplicate ID in this file'
        elif not re.fullmatch(r'[0-9]+', quantity):
            reason = 'Quantity must be nonnegative whole digits'
        elif int(quantity) > 9223372036854775807:
            reason = 'Quantity exceeds signed 64-bit range'
        if reason:
            rejected.append({'row': row_number, 'id': key, 'reason': reason})
        else:
            seen.add(key)
            accepted.append({'id': key, 'name': name, 'quantity': int(quantity)})
print(json.dumps({'accepted': accepted, 'rejected': rejected}, indent=2))

This policy accepts the first valid occurrence of an ID and rejects later occurrences. Another business may need to reject the entire duplicate group or the entire file. Decide that before writing; do not let iteration order accidentally become a business rule.

4. Create the target deliberately

CREATE SCHEMA csv_demo_20261009;

CREATE TABLE csv_demo_20261009.products (
  id TEXT PRIMARY KEY, name TEXT, quantity BIGINT
);

After reviewing the validator output, insert only the accepted records using the supported parameterized client or row-write interface. For this tiny, fixed fixture, the equivalent demonstrative SQL is:

INSERT INTO csv_demo_20261009.products (id,name,quantity)
VALUES ('p1','Notebook',12),('p2','Blue pen',8);
SELECT id,name,quantity FROM csv_demo_20261009.products ORDER BY id;

Do not turn the example into a production SQL string builder. Production input belongs in supported parameters or structured request bodies, with credentials kept outside the source file.

5. Reconcile the outcome

Record the input count, validation-accepted count, validation-rejected count and confirmed write count. A valid row can still fail to write because of permission, network or database errors. Keep those write failures separate from validation failures.

For the fixture, expect p1 quantity 12 and p2 quantity 8. Inspect both IDs. A successful request alone does not prove you wrote the intended values or selected the intended namespace.

6. Decide what a retry means

Preserve a batch identifier and stable row identities in your import job. Before retrying an uncertain write, check the write semantics of your chosen interface. Inserting, replacing and incrementing are not interchangeable actions. Never assume that replaying the same file is harmless for every operation.

Keep rejected rows recoverable: show the original row number, reason and a way to resubmit a corrected record. Avoid logging unnecessary customer details when the real file contains sensitive information.

A dependable importer can explain every row: accepted and confirmed, rejected with a reason, or waiting for a recoverable write. That is a stronger outcome than a single green “Upload complete” banner.

See OriginChainDB row CRUD for the supported write interfaces.

Treat the validation result as a reviewable artifact

The validator’s JSON is more useful when saved with a batch identity, source-file fingerprint and the agreed input contract version. A fingerprint identifies the file bytes that were reviewed; it is not proof that the values are true. Keep the original file and the review result together under the application’s normal access and retention rules.

For the five input rows, the reconciliation equation is simple: five parsed data rows equal two accepted plus three rejected. After import, the confirmed write count should equal two, unless a write failed. Do not combine validation rejection and remote write failure into a single rejected count, because they need different remedies.

An editor can fix a missing identifier or negative quantity in the source. An operator investigates a permission failure or a timed-out write. The interface should say which action is needed and retain the source row number so the person can find the problem without scanning the whole file.

Expand the fixture before processing a large export

Test a quoted product name containing a comma, an empty final line, an unexpected extra column, a missing column and a UTF-8 byte-order mark. The standard CSV reader handles quoted delimiters; splitting each line on commas would not. The validator opens with utf-8-sig so a byte-order mark does not become part of the first header.

Also test the quantity boundaries 0, 9223372036854775807 and 9223372036854775808. Under this article’s contract, zero and the signed 64-bit maximum are syntactically valid quantities; the larger value is rejected before a database request. Whether such a huge stock quantity is plausible is a separate business validation rule that you may impose.

Clarify whitespace and identity policy. The sample trims surrounding whitespace. If p1 and p1 appear, they become the same normalized identity. It does not merge P1 and p1; case folding would be another explicit rule. A normalization decision can change which records are considered duplicates, so preserve it in the contract and test it.

Plan the write phase as a resumable job

Review the accepted set before opening the write path. Give the batch a stable ID and retain per-row progress. For a large import, bounded batches make it easier to retry failures and inspect partial completion than one undifferentiated upload. The appropriate batch size depends on the supported interface and deployment limits; do not infer it from this five-row example.

For each intended write, record whether it is pending, confirmed or uncertain. If a request times out, query the stable record identity before deciding how to retry. Check whether the selected operation inserts, replaces or updates. Replaying a replacement of identical content has different consequences from replaying an increment, and the API’s documented behavior must guide the choice.

Avoid building SQL by interpolating names from the CSV. A product name may contain punctuation or malicious-looking text and still be ordinary data. Use supported parameter binding or structured row request bodies so values remain values. Keep the service credential outside the input file, generated review report and error messages.

Reconcile values, not only counts

After writing the accepted records, read p1 and p2 from the exact destination namespace and compare names and quantities. A count of two cannot detect that p1 was accidentally assigned p2’s quantity. For a larger batch, compare the intended stable IDs and selected normalized fields, using a process appropriate to the data’s scale and sensitivity.

Finish with a report that accounts for every input row and identifies remaining work. A corrected rejection should enter a new reviewed batch or a clearly recorded correction step. This gives the import a complete path from source file through validation and database writes to a verified outcome, rather than ending at a green request acknowledgement.