struggling to combine awk in one pass

[kristo5747]

After running the command

find . -name "R*VER" -mtime +1 -exec sh -c 'grep -H ^err "{}" || echo "{}:No error"' \;

my files look like this

RRR1~COS~COSMETICS~40048~jgmdtv113~1~P~R22-200~029053662549~20110607~102151.VER No error
    RRR1~COS~COSMETICS~ETT03~jgm14652.~3~F~R16-500~000907009757~20110607~085109.VER err 3922

Using the tilde (~) symbol as delimiter in the file name, can I extract the fields I want so I get an output like this

RRR1~COS~COSMETICS~40048~jgmdtv113~1~P~R22-200~029053662549~20110607~102151.VER No error RRR1 COS P
    RRR1~COS~COSMETICS~ETT03~jgm14652.~3~F~R16-500~000907009757~20110607~085109.VER err 3922 RRR1 COS F

I tried the following

find . -name "R*VER" -mtime +1 -exec sh -c 'grep -H ^err "{}" || echo "{}:No error"' \;|awk -F~ '{print $0}{print $1"\t"$2"\t"$7"\t"$8"\t"$9"\t"$10}'

but it does not work; Instead, it produces this output (not all fields shown here...)

RRR1~COS~COSMETICS~40048~jgmdtv113~1~P~R22-200~029053662549~20110607~102151.VER No error
    RRR1~COS~COSMETICS~40048~jgmdtv113~1~P~R22-200~029053662549~20110607~102151.VER RRR1 COS P
    RRR1~COS~COSMETICS~ETT03~jgm14652.~3~F~R16-500~000907009757~20110607~085109.VER err 3922
    RRR1~COS~COSMETICS~ETT03~jgm14652.~3~F~R16-500~000907009757~20110607~085109.VER RRR1 COS F

I'd like to do this is one pass so I can generate a SQL script with INSERT statements...Can it be done?

[thekashyap]

Why don't you simplify your code? It's looking too complex to understand really.

for myfile in `find . -name "R*VER" -mtime +1`
do
    echo "Processing $myfile"
    # did you mean to use -l instead of -H by any chance?
    # Two conditions on RHS and LHS do not produce similar output.
    somestr=`grep -H ^err $myfile || echo "$myfile:No error"`
    echo "     somestr=$somestr"
    echo $somestr | awk -F~ '{print $1"\t"$2"\t"$7"\t"$8"\t"$9"\t"$10}
done

Usually I recommend to make it work first using simpler syntax, before you try to make it look more nerdy by putting it all on one single line to impress others. :)

Using the tilde (~) symbol as delimiter in the file name, can I extract the fields I want so I get an output like this

Yes. I do not see any restrictions on which char can and can't be used as delim.

I'd like to do this is one pass so I can generate a SQL script with INSERT statements...Can it be done?

Yes, you're already on the correct path. You just need to adjust print statement inside the awk once you get it to work.

[kristo5747]

Thank you. Your solution is very elegant and works like a charm...but in two passes i.e.

RRR1~COS~COSMETICS~ETT04~jgmdtv175~2~P~R15-300~000876523911~20110607~102159.VER	No error
RRR1	COS	P	R15-300	000876523911	20110607
RRR1~COS~COSMETICS~99537~jgmdtv132~2~P~R22-200~029051946829~20110607~101331.VER	No error
RRR1	COS	P	R22-200	029051946829	20110607

I am looking to generate dynamically a SQL script with insert statements like so:

INSERT INTO MYTABLE (COL1,COL2,COL3,COL4,COL5,COL6,COL7,COL8)
VALUES ('RRR1~COS~COSMETICS~ETT04~jgmdtv175~2~P~R15-300~000876523911~20110607~102159.VER','No error','RRR1','COS','P','R15-300','000876523911','20110607')

All I would need to do is wrap both lines but I am not clear how to do it (still learning awk/sed).

Any input welcome. Thanks.

[thekashyap]

Each file is parsed only once. So I didn't quite understand what you mean by 2 passes? If you mean two statements inside the loop instead of one, then they can be combined by removing the temp variable somestr .

>> To generate the SQL:
As I said "You just need to adjust print statement inside the awk once you get it to work."
I.e. the awk on line 8 in my script.

Post your code along with a couple of example input files if you need further help.

[kristo5747]

After enlisting the help of local friends (much, much smarter) than me, this is what we got:

find . -name "R*VER" -exec sh -c 'grep -H ^err "{}" || echo "{}:No error"' \; |
        awk -F: '
                BEGIN { q="\047" }
                {
                        file=$1
                        result=$2
                        sub(/^.*\//,"",file)
                        split(file,a,/~/)
                        print "INSERT INTO MYTABLE (COL1,COL2,COL3,COL4,COL5,COL6,COL7,COL8) VALUES (" q file q "," q result q "," q a[1] q "," q a[2] q "," q a[7] q "," q a[8] q "," q a[9] q "," q a[10] q ")"

}

It works like a charm. Thanks for taking the time. Much appreciated.

'