CSV/Excel Import Validator
Accept file uploads (CSV, Excel), validate every row against business rules, report errors to the submitter, and import clean data into the target system. The essential pattern for bulk data intake.
On this page
Visual Flow
Rendering diagram…
When to Use This Pattern
Use CSV/Excel import when:
- Business users need to bulk import data (products, contacts, prices, inventory)
- Manual data entry is error-prone and time-consuming
- The imported data must meet quality standards before entering the system
- You need to provide feedback to the submitter about which rows passed and which failed
How It Works
| Stage | Action | Output |
|---|---|---|
| 1. Upload | User uploads CSV/Excel file | Raw file |
| 2. Parse | Read file, extract headers and rows | Structured data array |
| 3. Validate | Check each row against business rules | Valid rows + error report |
| 4. Review | User reviews errors, decides to proceed or fix | Decision |
| 5. Import | Insert/update valid rows into target system | Imported records |
| 6. Report | Generate summary with import results | Success/failure counts |
Implementation Guide
Step 1: Build the Upload Form
Create a form with:
- File upload field — accept .csv, .xlsx, .xls only
- Import type selector — "Contacts", "Products", "Prices" (determines validation rules)
- Update strategy — "Create new only", "Update existing only", "Create + update"
- Preview/dry run option — validate first without importing
Step 2: Parse the File
| File Type | Parsing Method |
|---|---|
| CSV | Split by delimiter (comma, semicolon, tab) |
| Excel (.xlsx) | Use Read Excel action or library |
| Excel (.xls) | Convert to xlsx first, then parse |
Handle parsing edge cases:
- Detect the delimiter automatically (some regions use semicolons)
- Handle quoted fields that contain the delimiter
- Detect and handle different encodings (UTF-8, Latin-1)
- Skip empty rows and header rows
- Trim whitespace from all cell values
Step 3: Validate Each Row
Run validation rules per column:
| Column | Rule | Error Message |
|---|---|---|
| Valid email format | "Row 5: Invalid email format" | |
| Phone | Valid phone number | "Row 12: Phone number too short" |
| Amount | Numeric, > 0 | "Row 8: Amount must be a positive number" |
| Date | Valid date, not in future | "Row 3: Date cannot be in the future" |
| Category | Must be in allowed list | "Row 15: 'Widgetz' is not a valid category. Did you mean 'Widgets'?" |
| Required fields | Not empty | "Row 22: Name is required" |
| Unique constraint | No duplicates in file or system | "Row 7: Email already exists in the system" |
Step 4: Generate the Error Report
Produce a clear, actionable error report:
📋 Import Validation Results
━━━━━━━━━━━━━━━━━━━━━━━━━━━
File: contacts_march_2025.xlsx
Total rows: 150
Valid: 142 (94.7%)
Errors: 8 (5.3%)
❌ Row 5: Email "john.doe@" — Invalid email format
❌ Row 12: Phone "+1555" — Phone number too short (minimum 10 digits)
❌ Row 18: Amount "-50" — Amount must be a positive number
❌ Row 23: Email "jane@company.com" — Duplicate: already exists in system
❌ Row 45: Category "Widgetz" — Not a valid category. Did you mean "Widgets"?
❌ Row 67: Name "" — Name is required
❌ Row 89: Date "2026-01-01" — Date cannot be in the future
❌ Row 134: Email "bob@acme.com" — Duplicate: same email on row 56
🔄 Options:
[Import 142 valid rows] [Download error report] [Fix and re-upload]
Step 5: Import Valid Rows
For each valid row:
- Check if record exists (by unique key — email, SKU, etc.)
- Create new or update existing based on the chosen strategy
- Log each operation (created/updated/skipped)
- Handle API rate limits — if importing to a cloud system, respect its rate limits
Step 6: Post-Import Summary
| Metric | Count |
|---|---|
| Rows in file | 150 |
| Rows validated | 150 |
| Rows with errors | 8 |
| New records created | 120 |
| Existing records updated | 22 |
| Skipped (no changes) | 0 |
| Import time | 45 seconds |
Tips & Best Practices
Always offer a template download. Give users a pre-formatted Excel file with headers, sample data, and data validation dropdowns. This prevents 80% of import errors.
- Show a preview. Before importing, show the first 5 rows so the user can confirm the columns mapped correctly.
- Handle duplicates gracefully. Don't just reject duplicates — offer options: skip, update, or flag for review.
- Provide a downloadable error report. For files with many errors, an on-screen list isn't enough. Let users download a CSV of just the failed rows (with error message in an extra column) so they can fix and re-upload.
- Support incremental imports. Large files (10,000+ rows) should be processed in chunks. Show progress and allow cancellation.
Related patterns
Data Enrichment Pipeline
Take a basic record and enhance it with data from external APIs — geocoding addresses, resolving company info, validating phone numbers, scoring leads. Transform minimal input into rich, actionable data.
Idempotency Key Deduplication
Tag every side-effecting request with a unique key, persist the outcome, and return the stored result on replays. Stops duplicate charges, duplicate tickets, and duplicate emails dead.
Cross-System Reconciliation
Compare records between two systems to find mismatches, missing entries, and data drift. The detective pattern that finds problems before they become costly — essential for finance, HR, and IT.