awk overwriting beginning of line! - awk

Oh forget it. I just now realized that the input file 'santanu.orig'
was
created in Windows. So, I converted it to unix format using dos2unix.
After that, the script worked as expected.

santanu wrote:
> [Sorry if I posted an incomplete message before. I erroneously
> pressed the mouse key on the post button.]
>
> Hello Everybody,
>
> I was trying to teach myself some awk. I wrote this small awk script:
> ==========================================
> #!/usr/bin/env gawk
>
> #Original text
> #2 7/26/1997 0:00 10 Active 1 7/26/1997
> 0:00 [NULL]
> #4 7/30/2000 0:00 10 Active 1 6/16/2001
> 0:00 [NULL]
>
> #Modified text
> #INSERT INTO TABLENAME VALUES(2,TO_DATE('7/26/1997 0:00','MM/DD/YYYY
> HH:MI'),10,'Active',1,TO_DATE('7/26/1997 0:00','MM/DD/YYYY
> HH:MI'),NULL);
> #INSERT INTO TABLENAME VALUES(4,TO_DATE('7/30/2000 0:00','MM/DD/YYYY
> HH:MI'),10,'Active',1,TO_DATE('6/16/2001 0:00','MM/DD/YYYY
> HH:MI'),NULL);
>
> {
> print "" > "santanu.out";
> a=$9; gsub(/\[|\]/, "", a);
> printf("INSERT INTO TABLENAME VALUES(");
> printf($1",TO_DATE('"$2" "$3"','MM/DD/YYYY HH:MI'),");
> printf($4",'"$5"',"$6",TO_DATE('"$7" "$8"','MM/DD/YYYY HH:MI'),");
> printf(a);
> printf(");");
> printf("\n");
> }
> ====================================================
>
> But when I run this script, on a file containing the "Original text",
> instead
> of getting the "Modified text", I get this:
> ==================================
> $awk -f santanu.awk santanu.orig
> );SERT INTO TABLENAME VALUES(2,TO_DATE('7/26/1997 0:00','MM/DD/YYYY
> HH:MI'),10,'Active',1,TO_DATE('7/26/1997 0:00','MM/DD/YYYY
> HH:MI'),NULL
> );SERT INTO TABLENAME VALUES(4,TO_DATE('7/30/2000 0:00','MM/DD/YYYY
> HH:MI'),10,'Active',1,TO_DATE('6/6/2001 0:00','MM/DD/YYYY HH:MI'),NULL
> ==================================
>
> Please note that the lines start with ");SERT" instead of "INSERT",
> whereas
> the ");" part should be at the end!
>
> What is the reason for this? I am baffled!
>
> Regards,
> Santanu
>

Your input file probably was created on Windows or some such and has
spurious control characters at the end of the line so that when you do this:

printf(a);

you're printing a control character that's jumping back back to the
beginning of your output line. To test that, comment out that line and
your lines will start with INSERT. Run dos2unix or some such conversion
program to fix the input file.

Now, for the script - among other things, you misunderstand how to use
printf. The first argument for printf is a format string, the second and
subsequent are the data to be printed. By trying to print the data
without a succeeding formatting string, you're openign yourself up to
all sorts of weird behavior if your input data contains formatting
strings (e.g. "%s").

The above should be rewitten as:

{
gsub(/[][]/, "", $9)
printf("INSERT INTO TABLENAME VALUES(%s,TO_DATE('%s %s','MM/DD/YYYY
HH:MI'),%s,'%s',%s,TO_DATE('%s %s','MM/DD/YYYY HH:MI'),%s);\n",
$1,$2,$3,$4,$5,$7,$8,$9)
}

Regards,

Ed.

On Jun 5, 2:23 am, Ed Morton <mor...@lsupcaemnt.com> wrote:
> Your input file probably was created on Windows or some such and has
> spurious control characters at the end of the line so that when you do this:
>
> printf(a);
>
> you're printing a control character that's jumping back back to the
> beginning of your output line. To test that, comment out that line and
> your lines will start with INSERT. Run dos2unix or some such conversion
> program to fix the input file.

You are right. I just figured that one out sometime after my original
posting.

> Now, for the script - among other things, you misunderstand how to use
> printf. The first argument for printf is a format string, the second and
> subsequent are the data to be printed. By trying to print the data
> without a succeeding formatting string, you're openign yourself up to
> all sorts of weird behavior if your input data contains formatting
> strings (e.g. "%s").
>
> The above should be rewitten as:
>
> {
> gsub(/[][]/, "", $9)
> printf("INSERT INTO TABLENAME VALUES(%s,TO_DATE('%s %s','MM/DD/YYYY
> HH:MI'),%s,'%s',%s,TO_DATE('%s %s','MM/DD/YYYY HH:MI'),%s);\n",
> $1,$2,$3,$4,$5,$7,$8,$9)

Thanks for the tip. I did not realize that the $n variables would
replace %s in the
format strings. (First I tried things like "INSERT $1" expecting awk
to
work like perl, but that did not happen.)

Regards,
Santanu