How to Validate a CSV structure and Template ?

0
I am working on a CSV Structure Validation requirement where the system must:1.Validate that the uploaded file is a valid CSV and follows the expected import template structure.2.Verify that all required columns are present and that the column names match the expected template.3.Reject the entire file and prevent any processing if the CSV/template structure is invalid (for example, if a required column is missing).For context, I am using the Data Importer module and have already configured the import mapping. I have successfully implemented field-level validations that run row by row after the import process begins.However, I am unsure how to validate the CSV template structure/header before processing starts. Specifically, I need to determine how to verify that the uploaded file matches the expected template (required columns, column names, and overall structure) and reject the file upfront if it does not.Has anyone implemented a similar template-level validation with the help of Data Importer module? Any guidance or best practices would be appreciated.
asked
1 answers
0

Hi Nihanth,

I recommend implementing a separate CSV template validation step before executing the Data Importer activity.

The important point is that row-level validation and file-structure validation should be handled separately.

1. Define the expected CSV structure

Maintain the expected column names in a configuration entity or a predefined template.

For example:

EmployeeId,EmployeeName,Email,Department

Define which columns are mandatory and whether additional columns are allowed.

2. Validate the uploaded CSV header before importing

Create a microflow named ACT_ValidateCSVStructure.

Before calling the Data Importer activity:

  • Read the uploaded CSV file.

  • Extract the first CSV record (header row).

  • Compare the actual headers against the expected template.

  • Check for missing required columns.

  • Check for duplicate column names.

  • Check for unexpected columns if your template requires an exact match.

  • Validate the expected column count.

For reliable CSV parsing, use a Java Action with a CSV parser such as Apache Commons CSV. Avoid using simple StringSplit because CSV values can contain quoted delimiters.

Also handle whitespace and UTF-8 BOM characters when comparing header names.

3. Return validation results

The validation step should return a clear result containing:

  • Validation status

  • Missing columns

  • Unexpected columns

  • Duplicate columns

  • Invalid header information

For example, if the uploaded file contains:

EmployeeId,EmployeeName,Email

Expected template:

EmployeeId,EmployeeName,Email,Department

Return:

Validation Status: Failed
Missing Column: Department

4. Execute the import only when validation succeeds

Configure your main microflow as follows:

Upload CSV
    |
    v
Validate CSV Structure
    |
    v
Validation Successful?
    |
    +---- No ----> Display Validation Errors
    |              Stop Processing
    |
    +---- Yes ---> Import with Mapping
                       |
                       v
                 Row-Level Validation
                       |
                       v
                 Process / Commit Data

Important: Do not execute Import with Mapping or start processing records before the structure validation succeeds. This ensures that an invalid template is rejected before any business data is processed.

5. Keep row-level validation separate

Once the header validation passes, continue using your existing Data Importer and field-level validation logic for individual rows.

This gives you two validation levels:

  • File-level validation: Checks CSV format, headers, required columns, duplicates, and template compatibility.

  • Row-level validation: Checks individual field values and business rules.

Official Mendix documentation:

Hope this helps! If you find this answer helpful, please mark it as the accepted answer.

answered