AWK - Why won't this AWK work to create SQL with Today's date?

Author

Why won't this AWK work to create SQL with Today's date?

Steve

2005-04-11, 8:55 am

All, I'm trying to create a sql file from a flatfile. I can create the
file just finebut when I try to include Todaydate as a field in my sql
statements I get the following error.
I'm providing a my source, my code, the error and what I'm trying to
get to.

What am I doing wrong?

Steve

*Inputfile*
2C7CE644 04408185412 ROK6016SPR 953426461789
0001
2C7C3D37 04408142135 ROK6016SPR 485222599128
0001
2D3AAFFD 04503846141 SCP4920KIT 360570162845
0001
2D5E8DFC 04506196732 PM8200KITU 031532538954
0001
2D6496BB 04506592187 PM8200KITS 852948040328
0001
3612C785 05401230725 ROTR650HK 126050310945
0001

*MyCode*
#!/usr/bin/ksh
thefile="testcut.txt"

awk '{print "insert into db(date,f1,f2,f3,f4,f5) values ("$dt
"\""$1"\"\,",
"\""$2"\"\,",
"\""$3"\"\,",
"\""$4"\"\,",
"\""$5"\");"}' dt=`date +'%Y%m%d'` $thefile >> loadfile.sql

*Error*
awk: 0602-538 The field 20050407 must be in the range 0 to 1024.
The input line number is 1. The file is testcut.txt.
The source line number is 1.

*Desired Output*
insert into db(date,f1,f2,f3,f4,f5) values ("2005-Apr-07","2C7CE644",
"04408185412", "ROK6016SPR", "953426461789", "0001");
insert into db(date,f1,f2,f3,f4,f5) values ("2005-Apr-07","2C7C3D37",
"04408142135", "ROK6016SPR", "485222599128", "0001");
insert into db(date,f1,f2,f3,f4,f5) values ("2005-Apr-07","2D3AAFFD",
"04503846141", "SCP4920KIT", "360570162845", "0001");
insert into db(date,f1,f2,f3,f4,f5) values ("2005-Apr-07","2D5E8DFC",
"04506196732", "PM8200KITU", "031532538954", "0001");
insert into db(date,f1,f2,f3,f4,f5) values ("2005-Apr-07","2D6496BB",
"04506592187", "PM8200KITS", "852948040328", "0001");
insert into db(date,f1,f2,f3,f4,f5) values ("2005-Apr-07","3612C785",
"05401230725", "ROTR650HK", "126050310945", "0001");

Ed Morton

2005-04-11, 8:55 pm

Steve wrote:

> All, I'm trying to create a sql file from a flatfile. I can create the
> file just finebut when I try to include Todaydate as a field in my sql
> statements I get the following error.
> I'm providing a my source, my code, the error and what I'm trying to
> get to.
>
> What am I doing wrong?

Putting a $ in front of an awk variable.

<snip>
> awk '{print "insert into db(date,f1,f2,f3,f4,f5) values ("$dt

awk '{print "insert into db(date,f1,f2,f3,f4,f5) values (" dt

Regards,

Ed.

Brian Inglis

2005-04-12, 3:56 am

On 8 Apr 2005 03:00:49 GMT in comp.lang.awk, ptjm@interlog.com
(Patrick TJ McPhee) wrote:

>Printing ' can be problematic in an awk script which is passed as an
>argument.

Not too hard: just replace each embedded "'" with "'''".

--
Thanks. Take care, Brian Inglis Calgary, Alberta, Canada

Brian.Inglis@CSi.com (Brian[dot]Inglis{at}SystematicSW[dot]a
b[dot]ca)
fake address use address above to reply

Patrick TJ McPhee

2005-04-12, 8:55 pm

In article <1112899275.852699.84260@o13g2000cwo.googlegroups.com>,
Steve <dafella007@yahoo.com> wrote:

% awk '{print "insert into db(date,f1,f2,f3,f4,f5) values ("$dt

As Ed pointed out, the $ in front of dt is wrong (you're trying to
put the value of input field 20050407 there)

Given that this is your desired output

% insert into db(date,f1,f2,f3,f4,f5) values ("2005-Apr-07","2C7CE644",
% "04408185412", "ROK6016SPR", "953426461789", "0001");

% "\""$5"\");"}' dt=`date +'%Y%m%d'` $thefile >> loadfile.sql

the date format you use here is wrong, too. Try %Y-%b-%d. Also, you
don't say what database you're using, but the above is not valid
SQL. SQL uses ' to delimit strings. " can be used to delimit certain
names, but not constant strings.

Printing ' can be problematic in an awk script which is passed as an
argument. In this case, you could replace your entire shell script with
an awk script
#!/usr/bin/awk -f
BEGIN {
ARGV[1] = "testcust.txt"
ARGC = 2

datecmd = "date +%Y-%b-%d"
datecmd | getline date
close(datecmd)

outfile = "loadfile.sql"
}

{ print "insert into db(date, f1,f2,f3,f4,f5) values ('" date "',",
"'" $2 "',", "'" $3 "',", "'" $4 "',", "'" $5 "');" > outfile }

You could pipe your SQL directly into the program that will ultimately
process loadfile.sql.
--

Patrick TJ McPhee
North York Canada
ptjm@interlog.com

Sponsored Links