Shell Programming and Scripting

INPUT---> USER | User1 | DisplayName1 | HomeTown1 | 987654321 | 27-07-1991 | M

i want to separate out all the fields of the file which has the format specified above , with the use of awk. I was writing that to the mysql by batch-file. But now there is problem , in databse i have defined the the datatype date for storing the date (2nd last field from the raw file) ,and format of the date is exactly opposite of that of dd-mm-yyyy ie,. yyyy-mm-dd.

So please help me to extract the field with correct date format (yyyy-mm-dd).or in short out put will this for the above given input.

output-> insert into XYZ values("user1","DisplayName1","HomeTown","987654321","1991-07-27","m");

Try this...

Code:

awk 'BEGIN{FS=" \\| "}
 { printf "insert into XYZ values(\"%s\",\"%s\",\"%s\",\"%s\",\"%s\",\"%s\");\n",\
           $2, $3, $4, $5, substr($6,7) "-" substr($6, 4,2) "-" substr($6,1,2), $7
 }' file1 > file2

I did test it...

Code:

$ head file[12]
==> file1 <==
USER | User1 | DisplayName1 | HomeTown1 | 987654321 | 27-07-1991 | M

==> file2 <==
insert into XYZ values("User1","DisplayName1","HomeTown1","987654321","1991-07-27","M");

great ! ygor