I'm using bash. I have a CSV file with entries that look like this


I want to extract the second and third columns and put them into an SQL statement. I thought this was the way to go ...

localhost:myproject davea$ awk '{printf "update my_table_user set thirdparty_user_id='%s' where thirdparty_user_id='%s';", $(NF-2),$(NF-1)}' /tmp/Region1\ users.csv
awk: trying to access out of range field -1
 input record number 1, file /tmp/Region1 users.csv
 source line number 1

but I'm getting this "trying to access out of range field -1" error. What's the proper syntax for extracting the second and third columns from my CSV file?

Edit: This is what's happneing in response to the answer given ...

localhost:myproject davea$ awk -F\, '{printf "update my_table_user set thirdparty_user_id=\'%s\' where thirdparty_user_id=\'%s\'\;", $(NF-2),$(NF-1)}'

Edit 2 In response to the updated answer, here's my output. Notice the word "update" is getting cut off ...

localhost:myproject davea$ awk -F, '{printf "update my_table_user set thirdparty_user_id='\''%s'\'' where thirdparty_user_id='\''%s'\'';\n", $1,$3}' /tmp/myfile.csv
';date my_table_user set thirdparty_user_id='102110089' where thirdparty_user_id='57cc73366e58b7cc330083a7
';date my_table_user set thirdparty_user_id='102110091' where thirdparty_user_id='57cc73366e58b7cc330083a8
';date my_table_user set thirdparty_user_id='102110093' where thirdparty_user_id='57cc73366e58b7cc330083a9
';date my_table_user set thirdparty_user_id='102110107' where thirdparty_user_id='57cc73366e58b7cc330083b3
  • 1
    Set the delimiter to be ,. By default is space so you have only one field visible from awk Jun 30 '17 at 13:36
  • Your data file has \r\n line endings, so do tr -d '\r' < yourFile | awk ... Jun 30 '17 at 17:48

The awk need to know the delimiter is ,. So you should exec command on this way:

awk -F\, '{printf "update my_table_user set thirdparty_user_id=\'%s\' where thirdparty_user_id=\'%s\'\;", $(NF-1),$(NF)}' /tmp/Region1\ users.csv

Also if the format of input file is consistent (three fields, you get 1st and 2nd) you can use $1 and $2

  • Its still not printing things out exactly right. Do I need to be escaping the apostraphes, like for instance, in this clause -- "set thirdparty_user_id='%s'"?
    – Dave
    Jun 30 '17 at 13:52
  • Yes, you should escape single quotas like this: ' Will update my answer Jun 30 '17 at 13:54
  • there should be no need to escape comma. -F, should be enough.
    – Archemar
    Jun 30 '17 at 13:58
  • @Archemar, I will prefer to be on the safe site. One symbol more will not harm the script :) Jun 30 '17 at 13:59
  • 2
    Never mind -- that was just bad line endings causing all the madness.
    – Dave
    Jun 30 '17 at 15:45

You need to tread carefully in this case since you have two layers of interleaving quotes:

        |-------------------------- 1 ------------------------|--2 --|------------- 3 ----------|--4 --|----- 5 ----|
awk -F, '{printf "update my_table_user set thirdparty_user_id='\'%s\'' where thirdparty_user_id='\'%s\'';\n", $2,$3}' yourcsvfile

Notice that regions 2 and 4 are white space (unquoted) and in there we insert our single quotes and %s strings. The regions 1,3,5 are balanced single quoted pairs. The regions 1..5 are contiguous. We are able to place %s in white space as is since they are not shell metacharacters like * ? $ [ or we'd have to escape them or place them in nonwhitespace regions like 3.

Another way is the via making available a quote via an awk variable:

awk -F, -v q=\' '{v2=q $2 q;v3=q $3 q;printf "update my_table_user set thirdparty_user_id=%s where thirdparty_user_id=%s;\n", v2,v3}' yourcsvfile

In this we first construct single-quote enclosed variables and them use them in our printf. I believe this is more user-friendly.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.