Data validation engine

Generic Data validator

Data file:

Name,Sal,Dept
ABC,1234,D1
AYX,12356,D2
DHF,345,ED3
123,4565,FGJG

Config File:

Delimiter-","
Rule1-Name-[:upper:]
Rule2-Sal-[:digit:]
Rule3-Dept-[D]*

Can be used to match any regex including date different format and numbers.

Depending on the Delimiter the file would be read.
The validator would check for the columns against the regex for each column specified.
If any column record doesnt match then the row should be flagged with the related rule no for the failure

Eg.

DHF,345,ED3(doesnt begin with D),Rule3

if multiple failure then
123,4565,FGJG,Rule2;Rule

What would be the best coding language awk or perl?
Examples would be appreciated

---------- Post updated at 02:42 PM ---------- Previous update was at 02:32 PM ----------

Post validation,we might have a requirement to convert the columns to format as specifier in the config.

Config File:

Delimiter-","
Rule1-Name-[:upper:]
Rule2-Sal-[:digit:]-0.00
Rule3-Dept-[D]*

Data File Output:

Name,Sal,Dept
ABC,1234.00,D1
AYX,12356.00,D2
DHF,345,ED3,Rule3
123,4565,FGJG,Rule2;Rule3

Notice the Sal column has changed to 1234.00 from 1234

Regards,D
Dikesh Shah.

Shouldn't that be '123,4565,FGJG,Rule1;Rule3'?

Yes.My typo.

I don't get it.

You say the 3rd field in Rule* lines in your config file is a regular expression, but none of your input fields match any of your regular expressions. (You also don't say what type of regular expression, but since you mentioned awk I'll assume that you want extended regular expression. Rule1's [:upper:] would match a single character from the set ":", "e", "p", "r", and "u"; not three uppercase characters. To match "ABC", "AYX", and "DHF", you would need an ERE something like ^[[:upper:]]{3}$ . Similarly, Rule2's [:digit:] would match a single character from the set ":", "d", "g", "i", and "t". To match the values you have in field 2, you would need an ERE similar to ^[[:digit:]]+$ . And, Rule3's [D]* matches every string that contains zero or more copies of the letter "D". (In other words, the ERE in Rule3 will match EVERY input string.) If you're looking for a "D" followed by a single digit or by one or more digits, you would need EREs similar to ^D[[:digit:]]$ or ^D[[:digit:]]+$ , respectively. You could simplify this somewhat if you were to specify that all EREs are anchored at both ends (i.e., the "^" at the beginning of the ERE and the "$" at the end of the ERE are assumed and should not be explicitly mentioned).

Then there is the question of how your format string works. In what formatting language does the format string "0.00" transform "1234" to "1234.00"? I could understand "%d.00", "%s.00", or" %.2f", but it seems to me that "0.00" should change any input string to the string "0.00".

Also note that using "-" as the field delimiter in your Config file severely restricts the EREs and format strings users can easily specify. Do you have the ability to change the format of the Config file? If you use the same EREs and format strings that awk uses, it would be much better to use <tab> as your config file field delimiter (in awk, "\t" can be easily used in both EREs and format strings anywhere a <tab> character is needed). This is especially a problem if you ever want to match dates of the form YYYY-MM-DD .

Are there any other commands (besides "delimiter" and "Rule*") allowed in a Config file? Is there a default delimiter if a "delimiter" command is not specified in a Config file?