awk file to read values from Db2 table replacing hard coded values

Shell Programming and Scripting
1

Hi,
I want to replace a chain of if-else statement in an old AWK file with values from Db2 table or CSV file. The part of code is below...

 if (start_new_rec=="true"){
    exclude_user="false";
    user=toupper($6);
    match(user, "XXXXX.[1,2,3]");
    if (RSTART ==2 ) {
        exclude_user="true";
    }

    else {
        match(user, "YYYY[AAAA,BBBB,CCCC]");
        if (RSTART ==2 ) {
            exclude_user="true";
        }
        else {

            match(user, "ZZZ[AAAA,BBBB,CCCC]");
            if (RSTART ==2 ) {
                exclude_user="true";
            }

May be I'm looking to populate an array and loop through it.Is is even possible ? any help is greatly appreciated.

Thanks

2

Seeing from the response I think its more complicated than I thought.To make it simple can we program an .awk file to read a one column CSV file and loop through it?

3

I really did not understand your query, please show input and desired output with brief description.

4

Sorry for not being clear in my question.

The input file will look like this, name it input.txt

a1,b1,c1,d1,e1
a2,b2,c2,d2,e2
a3,b3,c3,d3,e3

The file that contains list to compare will look like below, name it compare.txt

c2,
c5,
c20,

The awk file should compare column 3 in input.txt with compare.txt and delete rows that is in the list.

In the example above, since c2 is there in the list the row containing c2 should be deleted.
The final output is

a1,b1,c1,d1,e1
a3,b3,c3,d3,e3

=======================================================================

The input.txt will contain around 10 million records and compare.txt will have around 100 entries.

Hope I made it clear.

Thanks,

5 
$ awk -F, 'FNR==NR{A[$1];next}!($3 in A)' compare.txt input.txt
6

Thanks Akshay for the prompt reply but I'm getting an error as below(The name of the KSh file is remove_admin.ksh) ...

remove_admin.ksh[6]: $: not found.

#!/usr/bin/ksh
inputfile="/home/xxxx/input.csv"
comparefile="/home/xxxx/compare.csv"
touch $inputfile
touch $comparefile
$ awk -F, 'FNR==NR{A[$1];next}!($3 in A)' $comparefile $inputfile
7

The $ is a shell prompt for illustrative purposes, not actual shell code, remove it from the front of awk.

8

Awesome! Thanks everyone for the help

---------- Post updated 05-21-14 at 05:14 PM ---------- Previous update was 05-20-14 at 05:46 PM ----------

Hi Akshay,
A slight modification in the structure of data. Actually the input file is in this format below

"a1","b1","c1","d1"
"a2","b2","c2","d2"
"a3","b3","c3","d3"
"a4","b4","c4","d4"

The compare file will be

C2,
C4,

The provided code doesn't work if it is enclosed in double quotes.
How can I check for only the text in upper case ignoring the double quotes?

Thanks

9

No surprise that code doesn't work, as your latest sample input is different from earlier one (case & double quotes).

Use this code

$ awk -F, 'FNR==NR{A[tolower(q $1 q)];next}!(tolower($3) in A)' q='"' compare.txt input.txt
10

Akshay you are a genius. It worked like a charm, thanks for the help...