need help with perl command.

Hi Guys,

I wanted to create a insert statement from a file. The solution was given Durden_tyler and malcomex999. Malcomex gave the solution in awk which was not working as my OS is SunOS. The perl command was working but it is giving wrong insert statements for some of the records.

This is the thread i am talking about.

http://www.unix.com/shell-programming-scripting/118553-need-create-insert-query-file.html

The following is the perl command having problem.

perl -ne 'BEGIN {$q=chr(39)} chomp;
> if (/^$/){print $s,");\n"; $s=""}
> elsif (/\d{4}$/){$s="Insert into table values (".$q.$_.$q}
> else {@x=split/: /;
> if (/^(ACTION|DATABASE)/){$s .= ",".$x[1]}
> else {$s .= ",".$q.$x[1].$q}}
> END {print $s,");\n"}' pra.txt

This how it comes

d02 $ perl -ne 'BEGIN {$q=chr(39)} chomp;
> if (/^$/){print $s,");\n"; $s=""}
> elsif (/\d{4}$/){$s="Insert into table values (".$q.$_.$q}
> else {@x=split/: /;
> if (/^(ACTION|DATABASE)/){$s .= ",".$x[1]}
> else {$s .= ",".$q.$x[1].$q}}
> END {print $s,");\n"}' pra.txt
Insert into table values ('Thu Mar 16 16:14:41 2006','CONNECT','/','SYSDBA','oracle','pts/6','0');
Insert into table values ('Wed Mar 22 11:17:12 2006','CONNECT','/','SYSDBA','oracle','pts/6','0');
Insert into table values ('STATUS: 1031');
Insert into table values ('STATUS: 1017');
Insert into table values ('STATUS: 1031');
Insert into table values ('Tue Mar 28 12:39:44 2006','CONNECT','/','SYSDBA','oracle','pts/6','0');
Insert into table values ('Thu Mar 16 16:14:41 2006','CONNECT','/','SYSDBA','oracle','pts/6','0');
Insert into table values ('Wed Mar 22 11:17:12 2006','CONNECT','/','SYSDBA','oracle','pts/6','0');
Insert into table values ('STATUS: 1031');
Insert into table values ('STATUS: 1017');
Insert into table values ('STATUS: 1031');
Insert into table values ('STATUS: 1031');
Insert into table values ('STATUS: 1017');
Insert into table values ('STATUS: 1031');

The file is as shown below

d02 $ cat pra.txt
Thu Mar 16 16:14:41 2006
ACTION : 'CONNECT'
DATABASE USER: '/'
PRIVILEGE : SYSDBA
CLIENT USER: oracle
CLIENT TERMINAL: pts/6
STATUS: 0

Wed Mar 22 11:17:12 2006
ACTION : 'CONNECT'
DATABASE USER: '/'
PRIVILEGE : SYSDBA
CLIENT USER: oracle
CLIENT TERMINAL: pts/6
STATUS: 0

Wed Mar 22 11:18:44 2006
ACTION : 'CONNECT'
DATABASE USER: '/'
PRIVILEGE : NONE
CLIENT USER: T5282TS
CLIENT TERMINAL: CITMD0204244304
STATUS: 1031

Wed Mar 22 11:18:44 2006
ACTION : 'CONNECT'
DATABASE USER: 'SYS'
PRIVILEGE : NONE
CLIENT USER: T5282TS
CLIENT TERMINAL: CITMD0204244304
STATUS: 1017

Wed Mar 22 11:18:44 2006
ACTION : 'CONNECT'
DATABASE USER: '/'
PRIVILEGE : NONE
CLIENT USER: T5282TS
CLIENT TERMINAL: CITMD0204244304
STATUS: 1031

Tue Mar 28 12:39:44 2006
ACTION : 'CONNECT'
DATABASE USER: '/'
PRIVILEGE : SYSDBA
CLIENT USER: oracle
CLIENT TERMINAL: pts/6
STATUS: 0

Thu Mar 16 16:14:41 2006
ACTION : 'CONNECT'
DATABASE USER: '/'
PRIVILEGE : SYSDBA
CLIENT USER: oracle
CLIENT TERMINAL: pts/6
STATUS: 0

Wed Mar 22 11:17:12 2006
ACTION : 'CONNECT'
DATABASE USER: '/'
PRIVILEGE : SYSDBA
CLIENT USER: oracle
CLIENT TERMINAL: pts/6
STATUS: 0

Wed Mar 22 11:18:44 2006
ACTION : 'CONNECT'
DATABASE USER: '/'
PRIVILEGE : NONE
CLIENT USER: T5282TS
CLIENT TERMINAL: CITMD0204244304
STATUS: 1031

Wed Mar 22 11:18:44 2006
ACTION : 'CONNECT'
DATABASE USER: 'SYS'
PRIVILEGE : NONE
CLIENT USER: T5282TS
CLIENT TERMINAL: CITMD0204244304
STATUS: 1017

Wed Mar 22 11:18:44 2006
ACTION : 'CONNECT'
DATABASE USER: '/'
PRIVILEGE : NONE
CLIENT USER: T5282TS
CLIENT TERMINAL: CITMD0204244304
STATUS: 1031

Wed Mar 22 11:18:44 2006
ACTION : 'CONNECT'
DATABASE USER: '/'
PRIVILEGE : NONE
CLIENT USER: T5282TS
CLIENT TERMINAL: CITMD0204244304
STATUS: 1031

Wed Mar 22 11:18:44 2006
ACTION : 'CONNECT'
DATABASE USER: 'SYS'
PRIVILEGE : NONE
CLIENT USER: T5282TS
CLIENT TERMINAL: CITMD0204244304
STATUS: 1017

Wed Mar 22 11:18:44 2006
ACTION : 'CONNECT'
DATABASE USER: '/'
PRIVILEGE : NONE
CLIENT USER: T5282TS
CLIENT TERMINAL: CITMD0204244304
STATUS: 1031

Thanks for your help in advance

Regards,
Magesh

The script worked for the input file you provided in that post. Your input file has changed now.

If you understand how the script works, and the change in your data, then you should be able to figure out what's wrong quite easily.

Seems like all you are doing is a cut-paste of the scripts from here to your project, without even trying to figure out how it's working.

If you are looking for ongoing tech support, then sorry to say, but we are volunteers here who do not get paid to solve your problems for you.

tyler_durden

I strongly agree to your point tyler.. The problem is this is my first perl script i am looking at.. So, i am really having a trouble in understanding it...

If you can take the trouble of explaining your script, i will itslef try to change the script by myself...

Regards,
Magesh

You can try for awk solution given by "malcomex999" by modifying a bit.

try nawk/gawk under solaries.

actually panyam, i am working on that only,, but i am getting an error if i used nawk instead of awk.. i figured out one of the error.. but there are still some errors coming..

d003:/cust/home/dsdev>nawk -v -f="'" '{if(NF<1){printf(");\n");next}}
> {if($0 ~ /^[A-Z].. /){printf("insert into table values (%c%s%c",f,$0,f)}
> else{split($0,arr,":");
> gsub(f,"",arr[2]);printf(",%c%s%c",f,arr[2],f);}}' a.txt
insert into table values (nawk: empty regular expression
 input record number 2, file a.txt
 source line number 4

Can you help out with this..
i have added "-" before "f" which was not present in the awk command.

remove the "-" before f

i mean

nawk -v f=" ' " ............{}

if i am not wrong that f stands for the delimiter right?

it worked like the charm..

But it will be really helpful if anyone can explain the perl command..

f is not a delimiter.

awk/nawk -v variable_name = " ' " .. here in place of variable_name , your using "f".

Sorry w.r.t to "perl" , still i am a beginner.

Thanks For the reply panyam..
I hope someone will explain me the perl script..