earlyaidopters/gumroad-resources/csv-data-pipeline

CSV Data Pipeline

Instructions

Step 1: Inspect the Data

When the user provides a CSV file:

1. Read the first 10 rows to understand structure
2. Count total rows and columns
3. Identify column types (text, number, date, boolean, email, URL)
4. Report findings to the user:

"Your file has {rows} rows and {cols} columns.
Here's what I found:
- {col_name}: {type} ({null_count} missing values)
- ...
{issues_count} potential issues detected."

Step 2: Identify Issues

Check for these common problems:

Present issues to the user and ask which to fix.

Step 3: Apply Fixes

For each approved fix:

python scripts/validate_csv.py --input {filename} --checks all

If the script finds issues:

• Apply automatic fixes (whitespace, duplicates, date normalization)
• For ambiguous fixes (missing values, outliers), ask the user:

"Column 'revenue' has 12 missing values. Should I: (a) drop those rows, (b) fill with column median, (c) fill with 0, or (d) leave as-is?"

Step 4: Export Clean Data

Save the cleaned file with a descriptive name:

• {original_name}_cleaned.csv — processed data
• {original_name}_report.txt — summary of changes made

Tell the user what changed:

"Done! Here's what I fixed:
- Removed 23 duplicate rows
- Standardized 4 date formats to YYYY-MM-DD
- Trimmed whitespace in 'name' and 'email' columns
- Flagged 3 outliers in 'revenue' (not changed)
Clean file: data_cleaned.csv"

Examples

Example 1: Messy Customer List

User: "Clean this customer spreadsheet"

Found: 500 rows, columns [name, email, signup_date, plan, revenue] Issues:

• 12 duplicate emails
• signup_date has "01/15/2025", "2025-01-15", "Jan 15, 2025" formats
• 3 rows with missing email
• "Pro", "pro", "PRO" in plan column

Actions: Remove dupes, normalize dates, flag missing emails, lowercase plans.

Example 2: Large Dataset Validation

User: "Validate this CSV before I import to our database"

Run full validation suite. Report schema compatibility issues. Flag any values that would violate database constraints (nulls in required fields, strings in numeric columns, dates out of range).

Troubleshooting

"File too large to process" Cause: CSV exceeds available memory. Solution: Process in chunks of 10,000 rows. Ask user if sampling is acceptable for initial analysis.

"Encoding error on read" Cause: File isn't UTF-8. Solution: Try latin-1 encoding. If that fails, ask user what encoding the file uses.

"Column count mismatch between rows" Cause: Unescaped commas or newlines in data. Solution: Try reading with different quoting settings. Show the problematic rows to the user.