How to Validate CSV Files Before ERP Import: A Step-by-Step Guide

Submitting a CSV file to an ERP without prior validation is the most common cause of import delays and data quality incidents. A structured pre-validation process — one that business users can run without developer involvement — eliminates the majority of errors before they ever reach the ERP. This guide walks through each step.

Step 1: Verify file structure

Before any data-level checks, confirm that the file itself is structurally sound. A structurally broken file will fail at every row, making row-level errors misleading or impossible to interpret.

• Confirm the separator (comma, semicolon, pipe) matches what the ERP expects
• Check that every row has the same number of columns as the header row
• Ensure there are no merged cells, hidden rows or extra sheets (applies to Excel exports)
• Verify the encoding — UTF-8 recommended; avoid UTF-8 BOM for most ERPs

Step 2: Check column names against the ERP specification

Column names in the CSV must match exactly what the ERP import template expects — case, spacing and special characters included. A column named "Product Code" and one named "product_code" are different columns to most import engines, even if they contain identical data.

Step 3: Validate required fields for every row

Required fields must be populated on every row without exception. Run checks for both empty values and values that appear populated but are not valid — a single space, "N/A", "TBD" or a placeholder value from a template are all empty from the ERP's perspective.

Step 4: Check data types and formats

The ERP expects specific data types for each field. A price field expects a number; a date field expects a specific format; a yes/no field expects a controlled vocabulary. Type mismatches are a leading cause of row-level rejections.

1. 1Numeric fields: verify no currency symbols, spaces or commas used as decimal separators unless your ERP uses comma-as-decimal notation
2. 2Date fields: confirm format matches the ERP expectation — DD/MM/YYYY, YYYY-MM-DD, etc.
3. 3Boolean fields: check that yes/no, true/false or 1/0 values are consistent and match the ERP vocabulary
4. 4Code fields: verify against the reference lists maintained in the ERP — supplier codes, unit codes, category codes

Step 5: Detect and resolve duplicate rows

Duplicate product references in the same file will cause one of two outcomes: the ERP rejects the entire file, or it silently overwrites the first occurrence with the second. Both outcomes are damaging. Cross-row duplicate detection requires checking the entire column, not just adjacent rows — making it impractical to do reliably in Excel on large files.

Step 6: Review the validation report before submitting

Whether you use a dedicated tool or a manual checklist, the output of validation should be a structured report that identifies each error by row number, column name and error description — with a suggested fix where possible. A report that says "34 errors found" without specifying which rows and which fields is not actionable.