Shell Programming and Scripting

Hi,
I have basic knowledge on unix shell scripting(not an expert).

My requirement is reading the csv file using the schema defined in the configuration file and if the condition is not mached then move the unmatched record to a error file and matched good records into other file.

In brief:
Here i'm defining the schema of an input file in normal text file and I'm calling it as configuration file. My configuration file looks like :
col1 integer NN
col2 string
col3 string NN

Also I'm having a data file ( say its a .csv file) and it looks like :

id,name,location
1,John,FL
2,Merry,,
3,Taylor,CA
a,George,MI

So here i need to write unix sript such a way that while reading the csv data file ,it should refer to the configuration file for the datatype and whether that field is NULL or not. If it is not satisfying the condition it should drop that whole record and move for the next row for the validation check.

Here it has to read the 1st row value from the csv file and check the datatype of that value from the configuration file. If the value type of the 1st row is matched with the datatype of the 1st column , then it has to move the matched record into a new file and if unmatched record is found it has to move those error records into an error file.
How this can be done using UNIX shell scripting.

Here my expected result is :

id,name,age
1,John,FL
3,Taylor,CA

Rest of the records are dropped because :

2,Merry,, ---> here 3rd filed is NULL but in configuration file it is NOT NULL(NN)

a,George,MI --> here 1st field value is STRING where in conf file it is INTEGER.

So how this could be achieved in unix script and what are the commands need to be used to do this.

Please let me know if the scenario is not clear.

Thanks,
Shree11

You only listed two conditionals but hopefully you can take the syntax of this script and modify it to your needs.

Code:

#!/bin/bash

> BADFILE
> GOODFILE

cat sourcefile | awk -F, '
  BEGIN { badfile = "BADFILE"; goodfile = "GOODFILE" }
  {
    if($1 !~ /^[0-9]*$/)print $0 >> badfile
    else if($3 == "")print $0 >> badfile
    else print $0 >> goodfile
  }'

try also:

Code:

awk -F, '{x=$1; if (((x+=0) && ($3)) || NR==1) {of="good"} else {of="bad"} ; print > of}' in

I will let you know what i have tried.
I created one script file called validate.sh

Code:

#!/bin/bash

awk -F"|" ' 
BEGIN {
getline var < "configFile"
getline var < "configFile"
n = split( var , arr , "|" )
for ( i = 1 ; i <= n ; ++i )
{
        if( arr[i] == "Numeric" )
                regexp[i] = "^[0-9][0-9]*$"
        if( arr[i] == "String" )
                regexp[i] = "^[a-zA-Z0-9][a-z[A-Z0-9]*$"
}
}
{
for( i = 1 ; i <= NF ; ++i )
{
        if ( ! match ( $i , regexp[i] ) )
                break;
        if ( i == NF )
                print $0
}
}' dataFile

Here the above code is to validate the datatype. Eventhough in my configuration file if i have only 4 columns and in datafile if i have 5 columns, then 5th column is also coming as output which is not expected.

Here my congiguration file is :
col1|col2|col3|col4
Numeric|String|String|Numeric

And Data file is
col1|col2|col3|col4
510|abc|xxx|450
510|abc11|yyy|350
510|pqr|zz11z|670
312|22a2|439|110
312|qqq|rrr|11a0
3$33|sss|bbbb|110
333|A11ss|aAa|110

Also here i have to check for the NULL and field length conditions.
how it can be done?

How my above code changes for the below configuration file(configFile) .

col1,Numeric
col2,String
col3,String
col4,Numeric

Please help me on this.

If both schjema file (conf) and data file (file) as comma separated
Below code will do the job and send eligible records to a file "goodrec" and bad data to "badrec"

Code:

awk -F "," 'NR == FNR {a[NR] = $2; next}
  FNR == 1 {print $0 > "goodrec"; print $0 > "badrec"}
  FNR > 1 {for(i = 1; i <= NF; i++)
  {if((a[i] == "integer" && ($i + 0) == $i) || (a[i] == "string" && $i != "")) {f = 1} else {f = 0};
  if(f == 0) {print $0 > "badrec"; next}} print $0 > "goodrec"}' conf file

Hi SriniSho,

The code suggested by you, we I'm passing the config file?

Does the code taking both the data file and config file into account?

When i do the below , I'm not getting the proper output in the good and bad files:

Code:

awk -F "," 'NR == FNR {a[NR] = $2; next}   FNR == 1 {print $0 > "goodrec"; print $0 > "badrec"}   FNR > 1 {for(i = 1; i <= NF; i++)   {if((a[i] == "integer" && ($i + 0) == $i) || (a[i] == "string" && $i != "")) {f = 1} else {f = 0};   if(f == 0) {print $0 > "badrec"; next}} print $0 > "goodrec"}' dataFile

Tahnks,
Shree