How to pass filename and file content to awk

Question

tl;dr

Does anyone know how to pass a filename and the rest of the content of the file to awk? And make it run through all files in the directory and append the output of those actions to 1 final file?

long story:

I need to generate an SQL update file every week based on 2 variables and I used to copy paste a lot into a CSV file to get this awk command going. My setup is like this:

a very long manually pasted together CSV, row 1 looking something like this:

3afb6dad-352d-4c2a-b348-40fdb3c3d9a6;019f08dd-5017-43a1-b65b-c77cb90068ab

An AWK command that runs through the CSV:

cat list.csv  | awk -F\; '{print "update db01.CONTENT set locationid = \"" $1 "\" where cdbid = \"" $2 "\";";'}

I want to automate this by automatically generating that CSV file or even better by passing the right variables directly to the script.

I have several input files. The title of the file has to be $1 in my awk command and is constant. The file itself contains a variable number of UUIDs which needs to be $2

Input

I have a file called 3afb6dad-352d-4c2a-b348-40fdb3c3d9a6. The contents of this file look something like this:

019f08dd-5017-43a1-b65b-c77cb90068ab 
0479c914-6988-4038-ac74-f5b4adb123d0 
05a6b05a-dff9-4f7c-8a7e-92c8651b8cde 
05ad4a6a-e2c6-4074-adfd-0899c15a3600 
204b12af-42d8-48a0-83c6-10e02a051ed5 
20c4fb93-6ed2-4dee-87da-749b52c76d74 
27b2552a-1050-47fb-96fe-714b4231a067 
343f34be-b1cf-4cdf-8c35-344847a13837

I have another file named 72d799e8-ff97-4388-a498-47badd6ca7d8 containing something like this:

54b0623f-b5f0-47a1-bf90-9c8cb2054676 
8056e400-b809-4e08-bf0a-d5370f3e1b44

Desired output

what I need is to get a .sql file containing:

 update db01.CONTENT set locationid="3afb6dad-352d-4c2a-b348-40fdb3c3d9a6" where cdbid ="019f08dd-5017-43a1-b65b-c77cb90068ab"; 
update db01.CONTENT set locationid ="3afb6dad-352d-4c2a-b348-40fdb3c3d9a6" where cdbid="0479c914-6988-4038-ac74-f5b4adb123d0"; 
update db01.CONTENT set locationid ="3afb6dad-352d-4c2a-b348-40fdb3c3d9a6" where cdbid="05a6b05a-dff9-4f7c-8a7e-92c8651b8cde"; 
update db01.CONTENT set locationid ="3afb6dad-352d-4c2a-b348-40fdb3c3d9a6" where cdbid="05ad4a6a-e2c6-4074-adfd-0899c15a3600"; 
update db01.CONTENT set locationid ="3afb6dad-352d-4c2a-b348-40fdb3c3d9a6" where cdbid="204b12af-42d8-48a0-83c6-10e02a051ed5"; 
update db01.CONTENT set locationid ="3afb6dad-352d-4c2a-b348-40fdb3c3d9a6" where cdbid="20c4fb93-6ed2-4dee-87da-749b52c76d74"; 
update db01.CONTENT set locationid ="3afb6dad-352d-4c2a-b348-40fdb3c3d9a6" where cdbid="27b2552a-1050-47fb-96fe-714b4231a067"; 
update db01.CONTENT set locationid ="3afb6dad-352d-4c2a-b348-40fdb3c3d9a6" where cdbid="343f34be-b1cf-4cdf-8c35-344847a13837"; 
update db01.CONTENT set locationid ="72d799e8-ff97-4388-a498-47badd6ca7d8" where cdbid="54b0623f-b5f0-47a1-bf90-9c8cb2054676"; 
update db01.CONTENT set locationid ="72d799e8-ff97-4388-a498-47badd6ca7d8" where cdbid="8056e400-b809-4e08-bf0a-d5370f3e1b44";

I tried some things trying to combine

for file in ./output/*;
do
  echo ${file##*/}
done

and

while IFS='' read -r line || [[ -n "$line"]];
do
  #awk stuff
done <"$1"

But I couldn't get any result. all help is appreciated!

Answer 1

One solution using pure bash :

#!/bin/bash

cd ./output
for file in *; do
    while read -r line; do
        echo 'update db01.CONTENT set locationid="'$file'" where cdbid ="'$line'";'
    done < $file
done

Output :

update db01.CONTENT set locationid="3afb6dad-352d-4c2a-b348-40fdb3c3d9a6" where cdbid ="019f08dd-5017-43a1-b65b-c77cb90068ab";
update db01.CONTENT set locationid="3afb6dad-352d-4c2a-b348-40fdb3c3d9a6" where cdbid ="0479c914-6988-4038-ac74-f5b4adb123d0";
update db01.CONTENT set locationid="3afb6dad-352d-4c2a-b348-40fdb3c3d9a6" where cdbid ="05a6b05a-dff9-4f7c-8a7e-92c8651b8cde";
update db01.CONTENT set locationid="3afb6dad-352d-4c2a-b348-40fdb3c3d9a6" where cdbid ="05ad4a6a-e2c6-4074-adfd-0899c15a3600";
update db01.CONTENT set locationid="3afb6dad-352d-4c2a-b348-40fdb3c3d9a6" where cdbid ="204b12af-42d8-48a0-83c6-10e02a051ed5";
update db01.CONTENT set locationid="3afb6dad-352d-4c2a-b348-40fdb3c3d9a6" where cdbid ="20c4fb93-6ed2-4dee-87da-749b52c76d74";
update db01.CONTENT set locationid="3afb6dad-352d-4c2a-b348-40fdb3c3d9a6" where cdbid ="27b2552a-1050-47fb-96fe-714b4231a067";
update db01.CONTENT set locationid="3afb6dad-352d-4c2a-b348-40fdb3c3d9a6" where cdbid ="343f34be-b1cf-4cdf-8c35-344847a13837";
update db01.CONTENT set locationid="72d799e8-ff97-4388-a498-47badd6ca7d8" where cdbid ="54b0623f-b5f0-47a1-bf90-9c8cb2054676";
update db01.CONTENT set locationid="72d799e8-ff97-4388-a498-47badd6ca7d8" where cdbid ="8056e400-b809-4e08-bf0a-d5370f3e1b44";

Answer 2

awk to the rescue!

based on your input/output file format

$ awk '{file=FILENAME; sub(".*/", "", file);
        print "update db01.CONTENT set locationid=\"" file 
              "\" where cdbid=\"" $1 "\""}' output/*

Answer 3

Not awk, but here's a perl one-liner:

 perl -ne 'chomp; print "update db01.CONTENT set locationid=\"$ARGV\" where cdbid=\"$_\";\n";' * > whatever.sql

Testing

I made a test directory with two files having the following contents:

in1: (analogous to 3afb6dad-352d-4c2a-b348-40fdb3c3d9a6)

out1         (analogous to 019f08dd-5017-43a1-b65b-c77cb90068ab)
out2         (analogous to 0479c914-6988-4038-ac74-f5b4adb123d0)

in2: (analogous to 72d799e8-ff97-4388-a498-47badd6ca7d8)

out3         (analogous to 54b0623f-b5f0-47a1-bf90-9c8cb2054676)
out4         (analogous to 8056e400-b809-4e08-bf0a-d5370f3e1b44)

When I run the above with Perl 5.22, I get:

update db01.CONTENT set locationid="in1" where cdbid="out1";
update db01.CONTENT set locationid="in1" where cdbid="out2";
update db01.CONTENT set locationid="in2" where cdbid="out3";
update db01.CONTENT set locationid="in2" where cdbid="out4";

which I think is analogous to what you want.

Explanation

Edit The -n switch provides a while(<>){ ... } around the given command. Therefore, perl processes each file given on the command line, one line at a time. Within the script (the implicit loop body), $_ is the line, and $ARGV is the name of the file it came from (reference). chomp removes the trailing newline from $_, and then the print statement outputs the SQL you want. All the output is saved by the shell into whatever.sql.

Answer 4

As I wrote in comments, creating a CSV intermediate file just to consume with awk is pretty pointless. If you can create such a file then you can create the wanted SQL script directly, too. For example:

#!/bin/bash

for file in ./output/*;
do
  location=$(basename "$file")
  while read cb <"$file"; do
    echo "update db01.CONTENT set locationid=\"${location}\" where cdbid =\"${cb}\";"
  done
done

That version writes the result to standard output. You can redirect it where you want, or else put the desired redirection into the script.