how to pass a command line argument in the awk script

Question

This type of question was asked before but not exactly this one. I am a rookie in bash and awk scripting. So I don't know how to full fill my goal.

My target is to generate sql insert from the csv file.

Here is my csv file contents:-

ID;NAME;AGE;TITLE;DATE;SALARY
1;Test1;36;Engineer;date '2022-10-12';50.40
2;Test2;45;Manager;date '2020-01-15';100.50

My awk script is sql.awk:-

    #!/bin/awk -f

BEGIN {
  FS=";"
  OFS=","
  quotation="'"
}
FNR==1 {
    $1=$1; head=$0
    next
}
{
  dat=""
  for(i=1;i<=NF;i++) {
      val=($i~/[[:alpha:]]$/)?quotation $i quotation:$i
      dat=(dat)?dat OFS val:val
  }
  printf("INSERT INTO  DATA ( %s) VALUES ( %s );\n", head, dat) > "data.sql"
}
END {
    print ENVIRON["strftime"] 
    printf("\n-- Generated by %s at %s\n", ENVIRON["USERNAME"], strftime("%Y-%m-%d %T")) >> "data.sql"
}

This script generates file data.sql with correct contents as:

  INSERT INTO  DATA ( ID,NAME,AGE,TITLE,DATE,SALARY) VALUES ( 
  1,'Test1',36,'Engineer',date '2022-10-12',50.40 );
  INSERT INTO  DATA ( ID,NAME,AGE,TITLE,DATE,SALARY) VALUES ( 
  2,'Test2',45,'Manager',date '2020-01-15',100.50 );

  -- Generated by usr_name at 2020-10-18 17:31:13

My target is to get the sql table name as a command-line argument. Instead of hardcode DATA, I want it from the command-line argument.

I tried to run this as where above sript is modified as:-

./sql.awk -f data.csv gievn_table_name

    #!/bin/awk -f

BEGIN {
  FS=";"
  OFS=","
  quotation="'"
  tableName=ARGV[2]
}
FNR==1 {
    $1=$1; head=$0
    next
}
{
  dat=""
  for(i=1;i<=NF;i++) {
      val=($i~/[[:alpha:]]$/)?quotation $i quotation:$i
      dat=(dat)?dat OFS val:val
  }
  printf("INSERT INTO  %s VALUES ( %s );\n",tableName, head, dat) > tableName.sql
}
END {
    print ENVIRON["strftime"] 
    printf("\n-- Generated by %s at %s\n", ENVIRON["USERNAME"], strftime("%Y-%m-%d %T")) >> tableName.sql
}

I got an error:

./sql.awk -f data.csv gievn_table_name awk: ./sql.awk:19: printf("INSERT INTO %s VALUES ( %s );\n",tableName, head, dat) > tableName.sql awk: ./sql.awk:19: ^ syntax error awk: ./sql.awk:23: printf("\n-- Generated by %s at %s\n", ENVIRON["USERNAME"], strftime("%Y-%m-%d %T")) >> tableName.sql awk: ./sql.awk:23: ^ syntax error awk: data.csv:2: 1;Test1;36;Engineer;date '2022-10-12';50.40 awk: data.csv:2: ^ invalid char ''' in expression awk: data.csv:2: 1;Test2;36;Engineer;date '2022-10-12';50.40 awk: data.csv:2: ^ syntax error

How to get a command-line argument for the table name and output file name?

Answer 1

You need to do ARGC-- after reading a value from ARGV array if you don't want awk to process that as a file later. You may use:

cat sql.awk

BEGIN {
  FS=";"
  OFS=","
  quotation="'"
  tableName=ARGV[2]
  ARGC--
}
FNR==1 {
    $1 = $1
    head = $0
    next
}
{
  dat = ""
  for(i=1; i<=NF; i++) {
      val = ($i~/[[:alpha:]]$/) ? quotation $i quotation : $i
      dat = (dat != "") ? dat OFS val : val
  }
  printf("INSERT INTO %s ( %s ) VALUES ( %s );\n", tableName, head, dat)
}
END {
    printf("\n-- Generated by %s at %s\n", ENVIRON["USER"], strftime("%Y-%m-%d %T"))
}

To run this, use command:

awk -f sql.awk data.csv DATA

Alternatively, you can just use more common -v tableName=DATA to your awk command.