CSV Email Data Cleaning with Natural Language: A Practical Workflow

CSV is a common data exchange format in daily operations, customer management, market analysis, and internal collaboration. It is simple to import and export, but it also tends to accumulate problems: email addresses may contain typos, fields may include spaces or invisible characters, the same address may appear multiple times, and unrelated content such as names, notes, or phone numbers may be mixed in.

In the past, handling such files often required manual line-by-line checks. When the data volume was small, staff might still be able to filter records in spreadsheet software. But when a file contains thousands, tens of thousands, or even more records, manual work is not only time-consuming but also difficult to keep accurate. A seemingly simple task may involve filtering, copying, sorting, deduplication, review, and counting. Any mistaken operation can lead to missing or contaminated data.

With intelligent coding tools, users do not necessarily need to master complete programming syntax first. They can describe the goal in natural language and have the tool generate a script for data processing. The important point is not to let the tool replace thinking, but to turn a vague manual task into a clear, verifiable, and repeatable processing workflow.

1. State the Natural Language Requirements Accurately

Many data processing failures are not caused by difficult code, but by incomplete requirements. For example, “help me clean up the email addresses” may mean several different things:

• Remove content that clearly does not match an email structure;
• Trim leading and trailing spaces;
• Normalize case;
• Remove duplicate addresses;
• Preserve the original order;
• Generate a new cleaned result file;
• Count valid records;
• Record deleted data and the reason for deletion.

If these requirements are not specified in advance, the script may follow a default interpretation, and the final result may not match the actual business goal. Therefore, when describing a task to an intelligent coding tool, it is recommended to use a four-part structure: input, rules, output, and exception handling.

For example, the requirements can be organized as follows:

1. The input file is a CSV file;
2. Read the specified email field;
3. Remove empty values and trim whitespace before and after the field;
4. Perform basic validity checks based on a common email structure;
5. Convert email addresses to lowercase;
6. Remove duplicates while keeping the first occurrence;
7. Output a new CSV file;
8. Output the total number of valid email addresses;
9. Count invalid data, empty values, and duplicate data;
10. Do not overwrite the original file directly.

This description is clearer than “help me clean the email data” and makes it easier to generate a script that meets expectations.

2. Email Validity Checks Should Not Be Overly Complex

Email format validation looks simple, but there are edge cases. Usually, basic checks are enough:

• The address contains exactly one @ separator;
• There are valid characters before and after the separator;
• The domain part contains the necessary structure;
• The address does not contain obvious spaces, line breaks, or illegal control characters;
• Empty strings or content with only the separator are not accepted.

For ordinary business data, this basic validation is often sufficient. However, the full specification for email addresses is complex. Some valid addresses may contain special characters, internationalized characters, or uncommon structures. If an overly strict regular expression is used, addresses that are actually usable may be incorrectly marked as invalid.

Therefore, “valid” in data cleaning should be defined according to the business goal:

• If the goal is to remove obvious errors, looser validation can be used;
• If the goal is to import data into a business system, the field rules of that system should take precedence;
• If the goal is to send email, further domain resolution, address verification, or actual delivery confirmation may be needed.

Format validation only shows that an address “looks structurally correct.” It cannot prove that the mailbox really exists or that it can currently receive email.

3. A Reusable Processing Workflow

The following is a general script example. It uses common data processing methods to read, standardize, validate, deduplicate, and count records. The field names, file names, and paths in the example should be adjusted to actual conditions.

import csv
import re
from collections import Counter
INPUT_FILE = "raw_data.csv"
OUTPUT_FILE = "cleaned_results.csv"
EMAIL_FIELD = "email"
EMAIL_PATTERN = re.compile(
    r"^[^@\s]+@[^@\s]+\.[^@\s]+$"
)
valid_rows = []
invalid_count = 0
empty_count = 0
duplicate_count = 0
seen = set()
with open(INPUT_FILE, "r", encoding="utf-8-sig", newline="") as source:
    reader = csv.DictReader(source)
    if not reader.fieldnames or EMAIL_FIELD not in reader.fieldnames:
        raise ValueError(f"Email field not found in file: {EMAIL_FIELD}")
    for row in reader:
        raw_value = row.get(EMAIL_FIELD, "")
        email = raw_value.strip().lower()
        if not email:
            empty_count += 1
            continue
        if not EMAIL_PATTERN.fullmatch(email):
            invalid_count += 1
            continue
        if email in seen:
            duplicate_count += 1
            continue
        seen.add(email)
        row[EMAIL_FIELD] = email
        valid_rows.append(row)
with open(OUTPUT_FILE, "w", encoding="utf-8-sig", newline="") as target:
    writer = csv.DictWriter(
        target,
        fieldnames=reader.fieldnames
    )
    writer.writeheader()
    writer.writerows(valid_rows)
print("Cleaning completed")
print(f"Valid records: {len(valid_rows)}")
print(f"Empty records: {empty_count}")
print(f"Invalid format: {invalid_count}")
print(f"Duplicate records: {duplicate_count}")

The core of this code is not the syntax, but the processing order.

First, read the original file and confirm that the target field exists. Terminating immediately when the field is missing is safer than generating an empty result file. Then standardize each record by trimming leading and trailing spaces and converting to lowercase. Next, perform the basic format check. Finally, use a set to record addresses that have already appeared, completing deduplication with lower time complexity.

This example uses the strategy of “keep the first occurrence.” If the business requires keeping the last record, or if records need to be selected based on update time or data source priority, the deduplication rule must be redefined. The example code should not be applied blindly.

4. Why a Clean File Alone Is Not Enough

Many people focus only on how many records remain after cleaning and ignore the data that was removed. This creates two problems.

First, when the count is abnormal, it is difficult to trace the cause. For example, if the original file has 10,000 records and only 3,000 remain, users cannot tell whether the original data quality was poor or the script read the wrong field.

Second, business staff cannot review the filtered content. Some addresses may simply have an uncommon format and may not actually be invalid. Without an exception list, it is impossible to re-evaluate whether the rules should be relaxed.

A more robust approach is to output the following files or statistics together:

• Cleaned valid data;
• Data judged as invalid format;
• Duplicate data;
• Empty values or records with missing fields;
• Total records, valid records, invalid records, and duplicate records;
• Cleaning time, input file name, and rule version.

If the data volume is large, a reason field can also be added to each exception record, such as empty, invalid_format, or duplicate. This makes later review possible without rerunning the entire workflow.

5. How to Verify Before and After Running the Script

A script that runs successfully does not necessarily produce correct results. Data cleaning must include verification.

1. Check Record Counts

Check whether the following relationship holds:

Original records = valid records + empty values + invalid records + duplicate records

If the counts do not match, there may be a missing counting rule, an issue with empty-line handling, or overlapping duplicate classifications.

2. Check the Result File Structure

Confirm that the output file retains the necessary fields, that the column order meets downstream system requirements, and that the file encoding can be opened normally. Some spreadsheet software may automatically modify long numbers, dates, or special characters when opening a file, so visual inspection alone is not enough.

3. Check Uniqueness

Read the cleaned result again and verify that no duplicate values exist in the email field. Deduplication should be based on standardized values; otherwise, addresses that differ only by case or surrounding spaces may be incorrectly treated as different records.

4. Sample and Compare

Randomly select several original records and result records, then check whether fields are misaligned, whether non-email fields were accidentally modified, and whether any content has become garbled.

5. Keep the Original File

Any automation script should not overwrite the source file by default. Use a new name for the output file and back up the original data before formal processing. For important business data, processing logs and rule versions should also be retained for later auditing and reproduction.

6. Correct Use of Natural Language When Generating Scripts

Intelligent coding tools can lower the barrier to writing code, but they cannot take full responsibility on behalf of the user. A more reasonable approach is:

Step 1: Describe the business goal first, rather than directly asking for complex code.
Step 2: Specify the file format, field names, and exception rules.
Step 3: Ask the tool to explain the script’s processing flow.
Step 4: Ask for error handling, statistics, and safety notes.
Step 5: Test with a small desensitized sample.
Step 6: After confirming that the results meet expectations, process the full file.

In particular, avoid directly uploading raw data containing real customer information. Email addresses are part of personal information. When using external intelligent tools for analysis, prioritize desensitization, or provide only the field structure, fictitious samples, and error cases.

7. From a One-Off Script to a Maintainable Process

When similar tasks occur repeatedly, do not describe the requirements and generate a new script every time. Instead, consolidate the process. You can further add:

• Command-line parameters so input and output paths are configurable;
• A configuration file so the email field name and rules are maintained separately;
• Logging to save the time and results of each run;
• Automated tests covering empty values, duplicates, special characters, and encoding exceptions;
• Data quality reports showing changes before and after cleaning;
• Access control to restrict access to raw data and result files.

If an organization needs multi-person collaboration or regular processing, the script can also be packaged as an internal service and integrated with common capabilities such as object storage, task scheduling, logging, and monitoring alerts. Whether using a local script or a cloud workflow, the core principles remain the same: rules must be explicit, results must be verifiable, and the process must be traceable.

Conclusion

Email cleaning is a small data processing case, but it reflects the core value of automation: turning repetitive, time-consuming, and error-prone manual operations into workflows with clear rules, stable execution, and repeatability.

Natural language can help more people express requirements quickly, and intelligent coding tools can lower the barrier to script development. But high-quality results still depend on rigorous requirement definition, reasonable validation rules, complete exception handling, and necessary data security measures. What is truly worth promoting is not “running code without understanding it at all,” but enabling users to understand task boundaries, verify processing results, and adjust and maintain the workflow when needed.