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.

BPMN 2.0

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
Email	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 "[email protected]" — 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 "[email protected]" — 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

Tip

Always offer a template download. Give users a pre-formatted Excel file with headers, sample data, and data validation dropdowns. This prevents most of the common 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 processing

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.

Intermediate