CSV Import Validation

[angus]

Does anyone know if its possible to do any validation when using the CSV Upload feature?

On the web page, I have drop downs that restrict the values, but when a user loads a csv they can import anything.

[jsetzer]

Just as an idea for your own implementation:

In certain projects we are doing such "risky" imports in separate steps:

1. Upload CSV file into a separate table
2. Validate all imported rows
   • If any record is invalid, we offer different actions:
     • Delete all newly imported rows ("Cancel upload")
     • Change CSV file and upload again ("Import & Validate")
     • Delete invalid record(s), then validate again
     • Change record values directly in database, then validate again
   • If all records are valid, we offer different actions:
     • Delete all validated rows ("Cancel upload")
     • Import all validated rows
       There we move rows from import-table into production table and do some changes like setting record-owner, updating status, do calculations or resolving foreing keys (lookup tables)...

Validation can simply be done in PHP.

I hope you got the idea. If imports are risky for your process, I strongly recommend adding some kind of status_code-column (or status_id,which I prefer) having options like NEW | INVALID | VALID | OK and then, for calculations or other processing, only using records having status_code = 'OK'

---

Showcase "Validation Engine"

In two projects I have created a table for validation rules with a nice UI (my personal opinion). UI contains a "Query Builder" (based on https://querybuilder.js.org). Using this "Validation Engine" we can add, modify, disable or delete rules. After import, that "Validation Engine" validates every imported record against all enabled rules ("Ruleset") and counts and lists all imported rows, not matching the "Ruleset".

For example this (see screenshot below) is one validation rule which checks for existence of an order-number and for a valid country_id.

chrome_6jFLRnAMjH.png (91.97 KiB) Viewed 609 times

The Query Builder UI, based on https://querybuilder.js.org and integrated into DV, automatically offers all available colums of the import-table for selection and a bunch of functions for comparing data. Additionally I have extended the query builder by AppGini-specific custom functions, for example for checking existence of lookup values in different tables or for a completely customized SQL-WHERE condition. At any time you can add/change/remove conditions or subgroups and join them with AND or OR or inverse a condition/subgroup with NOT. Immediately on change you can see the resulting SQL Query and the list of invalid records at the bottom. This makes writing conditions very comfortable and straight-forward.

Columns of orders-table

chrome_qOJBTErbFA.png (15.73 KiB) Viewed 609 times

Various functions including custom function lookup_exists

chrome_cTqzfIGlWk.png (15.9 KiB) Viewed 609 times

Custom SQL-WHERE condition

chrome_2lBJDTYTBK.png (6.84 KiB) Viewed 609 times

Next to drag&drop building a query, at any time I can mark a rule as "custom query" and provide my custom SQL command.

In TableView we can quickly see failed validations:

chrome_uPkTEMBTVD.png (17.65 KiB) Viewed 609 times

---