I must write updates in a database from a flat file (CSV). I want to do that in the shell, with tools such as AWK.
#!/bin/bash
cat in.csv | sed -e '1d' | awk -F';' -v q=\' '{ # For each line.
print "DECLARE @v_trmID varchar(16) = " q $1 q
print "DECLARE @v_trmNom varchar(6) = " q $3 q
print "DECLARE @v_trmNbrTrav smallint = " $4
print "IF EXISTS (SELECT 1 FROM trimestre WHERE trmID = @v_trmID AND trmNom = @v_trmNom)"
print " BEGIN"
print " UPDATE trimestre"
print " SET trmNbrTrav = @v_trmNbrTrav"
print " WHERE trmID = @v_trmID AND trmNom = @v_trmNom"
print " END"
print "ELSE"
print " BEGIN"
print " PRINT " q "The script execution FAILED for record " NR " (pfiID " q " + @v_trmID + " q ", trimestre " q " + @v_trmNom + " q ")." q
print " END"
print "go"
print ""
}'
Though, there a 2 things I don't like:
The way quotes are inserted; it becomes really difficult to follow, even if I choose for the simpler way I found to write quotes inside an AWK string (instead of multiple escape sequences). Still, not that readable.
The fact that every SQL line is not readable as is. Code is not highlighted as SQL. I'd like to find a "here doc" solution, where I wouldn't have to prefix lines with
printf
.
Do you have any pieces of advice or better ideas on the way to write robust (because more readable / easily modifiable) code?