Using one awk output into another awk command

Question

I have one file (excel file) which has some columns (not fixed, changes dynamically) and I need to get values for couple of particular columns. I'm able to get the columns using one awk command and then printing rows using these columns numbers into another awk command. Is there any way I can combine into one?

awk -F',' ' {for(i=1;i < 9;i++) {if($i ~ /CLIENT_ID/) {print i}}} {for(s=1;s < 2;s++) {if($s ~ /SEC_DESC/) {print s}}} ' <file.csv> | awk -F "," '!($5~/...[0-9]L/ && $21~/FUT /) {print $0}' <file.csv>

Gives me output as 5 and 9 for columns (client_idandsec_desc`), which is their column number (this changes with different files).

Now using this column number, I get the desired output as follows:

awk -F "," '!($5~/...[0-9]L/ && $21~/FUT /) {print $0}' <file.csv>

How can I combine these into one command? Pass a variable from the first to the second?

Input (csv file having various dynamic columns, interested in following two columns)

CLIENT_ID   SEC_DESC
USZ256      FUT DEC 16 U.S.
USZ256L     FUT DEC 16 U.S. BONDS
WNZ256      FUT DEC 16 CBX
WNZ256L     FUT DEC 16 CBX BONDS

Output give me rows- 2 and 4 that matched my regex pattern in second awk command (having column numbers as 5 & 21). These column numbers changes as per file so first have to get the column number using first awl and then giving it as input to second awk.

Answer 1

I think I got it.

awk -F',' '
    NR == 1 {
        for (i=1; i<=NF; ++i) {
            if ($i == "CLIENT_ID") cl_col = i
            if ($i == "SEC_DESC") sec_col = i
        }
    }
    NR > 1 && !($cl_col ~ /...[0-9]L/ && $sec_col ~ /FUT /) {print $0}
' RED_FUT_TST.csv

Answer 2

To solve your problem you can test when you're processing the first row, and put the logic to discover the column numbers there. Then when you are processing the data rows, use the column numbers from the first step.

(NR is an awk built-in variable containing the record number being processed. NF is the number of columns.)

E.g.:

$ cat red.awk
NR == 1 {
  for (i=1; i<=NF; ++i) {
    if ($i == "CLIENT_ID") cl_col = i;
    if ($i == "SEC_DESC") sec_col = i;
  }
}

NR > 1 && $cl_col ~ /...[0-9]L/ && $sec_col ~ /FUT /


$ awk -F'\t' -f red.awk RED_FUT_TST.csv
USZ256L FUT DEC 16 U.S. BONDS
WNZ256L FUT DEC 16 CBX BONDS