Excel/CSV Customer Data Cleaning Pipeline

This project demonstrates an end-to-end Python workflow for cleaning customer Excel and CSV files containing duplicates, missing values, invalid emails, mixed dates, inconsistent phone formats, province variations, and currency-formatted values.

Business problem

Customer data exported from multiple systems is often inconsistent and cannot be used safely for reporting, CRM imports, marketing, or customer analytics.

Solution

The pipeline validates, standardizes, and separates records into clean, invalid, and duplicate outputs. It also creates a multi-sheet data-quality report with a before-and-after comparison.

Example results

• Original records: 248
• Clean records: 143
• Invalid review rows: 69
• Duplicate review rows: 36

Deliverables

output/
├── cleaned_customer_data.xlsx
├── invalid_records.csv
├── duplicate_records.csv
└── data_quality_report.xlsx

Data-quality issues included

• Exact duplicate rows
• Blank required fields
• Invalid email formats
• Mixed and invalid dates
• Multiple Vietnamese phone-number formats
• Province-name variations
• Currency symbols and thousands separators
• Unsupported non-VND currency values
• Conflicting customer IDs
• Duplicate normalized emails and phone numbers

Cleaning rules

• Valid Vietnamese phone numbers are normalized to +84xxxxxxxxx.
• Province aliases are mapped to canonical names.
• Valid dates are converted to a consistent date type.
• VND values are converted to numeric amounts.
• Invalid values are not guessed and are exported for manual review.
• Duplicate customer candidates are separated from the clean dataset.

Run locally

python -m venv .venv
.venv\Scripts\Activate.ps1
python -m pip install -r requirements.txt
python run_pipeline.py
python -m pytest

Project structure

excel-csv-data-cleaning-pipeline/
├── data/
│   ├── raw/raw_customer_data.xlsx
│   └── reference/province_mapping.csv
├── output/
├── src/
├── tests/
├── run_pipeline.py
├── requirements.txt
└── README.md

Portfolio note

All customer information in this repository is synthetic and was created specifically for demonstration purposes. It does not contain real company, employee, or customer data.