CSV Import Validation

The recommended method of customizing your AppGini-generated application is through hooks. But sometimes you might need to add functionality not accessible through hooks. You can discuss this here.
Post Reply
angus
Veteran Member
Posts: 109
Joined: 2020-05-28 22:27

CSV Import Validation

Post by angus » 2022-08-08 15:56

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.
AppGini 22.13

User avatar
jsetzer
AppGini Super Hero
AppGini Super Hero
Posts: 1495
Joined: 2018-07-06 06:03
Location: Kiel, Germany
Contact:

Re: CSV Import Validation

Post by jsetzer » 2022-08-09 06:12

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
chrome_6jFLRnAMjH.png (91.97 KiB) Viewed 306 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
chrome_qOJBTErbFA.png (15.73 KiB) Viewed 306 times

Various functions including custom function lookup_exists
chrome_cTqzfIGlWk.png
chrome_cTqzfIGlWk.png (15.9 KiB) Viewed 306 times

Custom SQL-WHERE condition
chrome_2lBJDTYTBK.png
chrome_2lBJDTYTBK.png (6.84 KiB) Viewed 306 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
chrome_uPkTEMBTVD.png (17.65 KiB) Viewed 306 times

---
Kind regards,
<js />

My AppGini Blog:
https://appgini.bizzworxx.de/blog

Post Reply