A cleaned CSV should preserve the rows you still cannot resolve

A CSV can look cleaner after a few formatting changes and still contain decisions that a script cannot make. Two rows might share an email address without representing one record that should be deleted. A required field might be empty without giving anyone permission to invent its value.

For a small cleanup job, I want the output to make those decisions easier to review. That means keeping the source records, applying only agreed transformations, and leaving a visible explanation of what remains unresolved.

The six-row example below is fictional. It is an existing demonstration I prepared for a CSV cleanup portfolio, not a client delivery or a successful import into a customer system.

Define the rules before transforming the file

In this example, the permitted changes are deliberately narrow:

• Trim leading and trailing whitespace from company, website, and email.
• Lowercase email under the agreed demonstration rule. This is not a universal instruction to rewrite every email field in every dataset.
• Keep record_id, country, notes, row order, and all six records unchanged.
• Use the cleaned, non-empty email as the exact duplicate key.
• Treat an empty email as missing; do not fetch a replacement or infer one from a website.
• Add qa_status to explain the result under these rules.

The source has six columns. The demonstration output has seven because it adds the status column. Adding a column is a schema decision to agree before delivery, especially when a receiving system expects a fixed column order.

Keep a readable before-and-after record

Here is the original CSV. The spaces around some values are intentional.

record_id,company,website,email,country,notes
DEMO-001,  Hikari Demo  ,https://example.com/hikari, INFO@EXAMPLE.COM ,Japan,Fictional sample; retain this record
DEMO-002,Hikari Demo,https://example.com/hikari,info@example.com,Japan,Fictional sample; keep separate pending decision
DEMO-003, Sakura Demo ,https://example.com/sakura,sales@example.com,Japan,Fictional sample
DEMO-004,Maple Demo,https://example.com/maple, contact@example.com ,Canada,Fictional sample
DEMO-005,River Demo,https://example.com/river,,Japan,Fictional sample; email not supplied
DEMO-006,Ocean Demo, https://example.com/ocean ,team@example.com,United Kingdom,Fictional sample

And here is the existing demonstration output:

record_id,company,website,email,country,notes,qa_status
DEMO-001,Hikari Demo,https://example.com/hikari,info@example.com,Japan,Fictional sample; retain this record,DUPLICATE_EMAIL_REVIEW
DEMO-002,Hikari Demo,https://example.com/hikari,info@example.com,Japan,Fictional sample; keep separate pending decision,DUPLICATE_EMAIL_REVIEW
DEMO-003,Sakura Demo,https://example.com/sakura,sales@example.com,Japan,Fictional sample,READY_UNDER_DEMO_RULES
DEMO-004,Maple Demo,https://example.com/maple,contact@example.com,Canada,Fictional sample,READY_UNDER_DEMO_RULES
DEMO-005,River Demo,https://example.com/river,,Japan,Fictional sample; email not supplied,MISSING_EMAIL_REVIEW
DEMO-006,Ocean Demo,https://example.com/ocean,team@example.com,United Kingdom,Fictional sample,READY_UNDER_DEMO_RULES

The result still has six data rows. Five original cells changed across four rows: two cells in DEMO-001, and one each in DEMO-003, DEMO-004, and DEMO-006. The new qa_status values are additional metadata, not changes to the original six fields.

DEMO-001 and DEMO-002 now have the same non-empty email. They remain separate rows, with separate notes and IDs. Exact equality on a chosen key is a review signal; it does not establish which record should survive a merge.

DEMO-005 keeps its empty email. The other three rows are READY_UNDER_DEMO_RULES, meaning they pass this example's narrow checks. That label does not certify valid addresses, permission to contact anyone, or compatibility with a buyer's import process.

Separate the transformation from the decision

One way to express the demonstration is to build a new list of row dictionaries, then count the non-empty normalized keys before assigning statuses:

from collections import Counter

cleaned = []
for source_row in rows:
    row = source_row.copy()
    for field in ("company", "website", "email"):
        row[field] = row[field].strip()
    row["email"] = row["email"].lower()
    cleaned.append(row)

email_counts = Counter(row["email"] for row in cleaned if row["email"])

for row in cleaned:
    if not row["email"]:
        row["qa_status"] = "MISSING_EMAIL_REVIEW"
    elif email_counts[row["email"]] > 1:
        row["qa_status"] = "DUPLICATE_EMAIL_REVIEW"
    else:
        row["qa_status"] = "READY_UNDER_DEMO_RULES"

Here, rows means records already read with a CSV parser. This snippet assumes the demonstrated schema and non-null string values; it is not a general cleanup program for arbitrary inputs. A missing header or a malformed row needs separate handling rather than being silently converted into an empty value.

For file parsing and writing, Python's standard csv module provides dictionary readers and writers. Use a parser that handles quoted fields, commas, and line breaks inside values instead of splitting each line on commas. Keep identifiers as text when that is the agreed schema, so formatting does not erase meaningful leading zeroes.

The order of operations matters. Counting emails before trimming and lowercasing would miss this example's duplicate pair. Counting empty emails as a duplicate key would confuse a missing-value issue with a repeated address.

Review the unresolved rows before an import

A useful handoff contains the cleaned CSV, a change summary, and the unresolved-record list. In this demonstration, that list contains two rows for duplicate-email review and one for missing-email review.

The person responsible for the data can then decide whether the duplicate-key records should stay separate, whether a documented merge rule is appropriate, or whether a missing value can be supplied from an authorized source. Until that decision exists, the uncertainty remains in the output.

Before applying the approach to another file, agree the output columns and order, permitted transformations, exact duplicate key, required fields, and what counts as an acceptable result. A buyer-side import is a separate check in the actual receiving system. This demonstration does not perform one.

A small, agreed cleanup scope

My published CSV cleanup package is $100 for one CSV, up to 500 data rows and 10 columns, with one agreed output format, a change summary, and an unresolved-record list. It covers up to two hours of work, delivery in three days after all required materials are submitted through Upwork, and one revision under the original agreed rules.

Before ordering, discuss a representative redacted sample and a completed target example. File size alone does not establish that the work fits the package. New files, added rows, or changed rules need a separate scope.

The package excludes CRM imports or updates, live spreadsheet changes, fuzzy merging, manual research, and filling unknown values. I have not established client results, buyer-side import success, sales, or measured time savings from this demonstration.

If this matches the cleanup you need, discuss your sample and target format through the $100 CSV cleanup project on Upwork before ordering.

AI disclosure: An AI agent prepared this article from the owner's existing fictional before-and-after CSV files and published offer materials. The example uses fictional records and reserved example.com addresses. It does not describe a real client engagement or certify an import.