2

To clarify let me give an example. I have a csv file that I have parsed the columns of but when I try to reference the field that contains date (formatted as 15-Jun-2020) my printf statement does not know how to escape it:

#Example csv content/placement (IGNORES.csv): C330001000,R3343,15-Jun-2020

while IFS=, read -r field1 field2 field3;
do
        printf "UPDATE ODS.PERF_ACCT_ERR_DTL SET REC_ACTV_IND='T' WHERE BUS_DT='$field3' and ETL_ERR_CD='$field2' AND ACCT_KEY in (SELECT ACCT_KEY FROM ODS.ACCT_PORTFOLIO WHERE ACCT_SRCH_NBR='$field1');\nCOMMIT;\n"
done < IGNORES.csv > queryfile.sql

The output ends up looking like this: 

' and ETL_ERR_CD='R3343' AND ACCT_KEY in (SELECT ACCT_KEY FROM ODS.ACCT_PORTFOLIO WHERE ACCT_SRCH_NBR='C330001000');
COMMIT;

I tried some weird escape character combos I thought would help but so far the closest I've gotten is printing the whole thing but instead of it showing 15-Jun-2020 it shows field3 as the output. Any suggestions or references for understanding how to escape something stored within a variable?

Shane Luca
  • 41
  • 5
  • 3
    The problem isn't `printf`, it's because your CSV file is in DOS/Windows format, and has a nonprinting carriage return character at the end of each line (in addition to the newline that unix programs expect), and it's getting treated as part of `field3`. See [this question](https://stackoverflow.com/questions/22481278/syntax-error-invalid-arithmetic-operator-error-token-is). In your case, you can trim it by changing the `read` statement slightly: `while IFS=$',\r' read -r ...` – Gordon Davisson Jun 16 '20 at 01:12
  • 2
    BTW, you really shouldn't include data directly in `printf`'s "format string" (the first argument). Use `%s` as placeholder in the format string, and pass the data as separate arguments: `printf "UPDATE ODS.PERF_ACCT_ERR_DTL SET REC_ACTV_IND='T' WHERE BUS_DT='%s' and ETL_ERR_CD='%s' AND ACCT_KEY in (SELECT ACCT_KEY FROM ODS.ACCT_PORTFOLIO WHERE ACCT_SRCH_NBR='%s');\nCOMMIT;\n" "$field3" "$field3" "$field1"` – Gordon Davisson Jun 16 '20 at 01:15
  • 4
    Voted to close as duplicate because I already answered your question about building the SQL request. and you did not even take time to apply sample, recommendations or even approve an answer there. – Léa Gris Jun 16 '20 at 01:49

2 Answers2

3

Replace the - with \-

If all you're dealing with is hyphens, you can use parameter substitution to do a find and replace.

var="15-Jun-2020"
${var//-/\\-}

Breakdown

The // double forward slash indicates a global find and replace in a parameter substitution.

${var//PATTERN/REPLACEMENT VALUE}
Andria
  • 4,712
  • 2
  • 22
  • 38
  • 1
    bash's *pattern substitution* `${param/pat/string}` uses pattern matching as described in `man bash` (not regular expressions). –  Jun 16 '20 at 01:48
  • Already answered here: https://stackoverflow.com/questions/62205458/building-sql-update-statements-using-a-bash-script/62205864#62205864 – Léa Gris Jun 16 '20 at 01:50
0

The input file appears to be in Windows/DOS format. You can convert it with dos2unix. For more see wooledge - How do I convert a file from DOS format to UNIX format.

Use printf format argument correctly:

while IFS=, read -r field1 field2 field3
do
  printf '%s\n' "UPDATE ODS.PERF_ACCT_ERR_DTL SET REC_ACTV_IND='T' WHERE BUS_DT='$field3' and ETL_ERR_CD='$field2' AND ACCT_KEY in (SELECT ACCT_KEY FROM ODS.ACCT_PORTFOLIO WHERE ACCT_SRCH_NBR='$field1');" "COMMIT;"
done < IGNORES.csv > queryfile.sql

%s\n is a format argument - subsequent arguments will be printed according to this.

  • Why use `printf` if you don't even use it to format variables `$field1` `$field2` and `$field3`'s values properly within the SQL statements, using `%s` format tags within the `printf`'s format string? – Léa Gris Jun 16 '20 at 01:52