Visit Our UNIX and Linux User Community


need help with perl command.


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting need help with perl command.
# 1  
Old 09-22-2009
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.
Code:
https://www.unix.com/shell-programming-scripting/118553-need-create-insert-query-file.html

The following is the perl command having problem.

Code:
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
Code:
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
Code:
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
# 2  
Old 09-22-2009
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
# 3  
Old 09-23-2009
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
# 4  
Old 09-23-2009
You can try for awk solution given by "malcomex999" by modifying a bit.

try nawk/gawk under solaries.
# 5  
Old 09-23-2009
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..

Code:
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.
# 6  
Old 09-23-2009
remove the "-" before f

i mean

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

# 7  
Old 09-23-2009
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..

Previous Thread | Next Thread
Test Your Knowledge in Computers #670
Difficulty: Medium
Fourteen channels are designated in the 2.4 GHz range, spaced 5 MHz apart from each other except for a 12 MHz space before channel 14.
True or False?

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

perl command

Hi Alll i need something in perl that can check if a hard mount ( /tools ) if mounted and, if it is not print "not mount" i had this, but it will not work because it looks for a directory which will always exist regardless if the mount if there are not #!/usr/bin/perl my $DELAY = 300;... (1 Reply)
Discussion started by: ab52
1 Replies

2. Shell Programming and Scripting

Help with perl -pi command

Greetings to all, I am trying to write or use a perl line command in a shell script to replace the first 9 characters on each line in a file to zero, regardless of what the first 9 characters are. This is how far I have gotten: perl -pi -e 's/........../ filename Thanks in advance for the help (4 Replies)
Discussion started by: Harleyrci
4 Replies

3. Shell Programming and Scripting

perl/unix: script in command line works but not in perl

so in unix this command works works and shows me a list of directories find . -name \*.xls -exec dirname {} \; | sort -u | > list.txt but when i try running a perl script to run this command my $query = 'find . -name \*.xls -exec dirname {} \; | sort -u | > list.txt';... (2 Replies)
Discussion started by: kpddong
2 Replies

4. Shell Programming and Scripting

Send "perl -e '...' " command through SSH, from a perl script

Hey guys I am trying to send a perl -e command to a number of systems using SSH. The command should retrieve some information for me. The problem is, the remote shell tries to interpolate my variables and doesn't get it should take the command literally and just execute it. Below the code.... (2 Replies)
Discussion started by: clrg
2 Replies

5. Shell Programming and Scripting

Convert Sed command to perl command

Hello, Can any perl experts help me convert my sed string to perl. I am unsuccessful with this. I have to remove this string from html files OAS_AD('Top'); I have come up with this. However the requirement is in perl. for find in $(find . -type f -name "file1.html") ; do cat $find |... (2 Replies)
Discussion started by: abacus
2 Replies

6. Shell Programming and Scripting

combine two perl lines into a single perl command

Hi Everyone, i have a string 00:44:40 so: $tmp=~ s/://gi; $tmp=~s/({2})({2})({2})/$1*3600+$2*60+$3/e; the output is 2680. Any way to combine this two lines into a single line? Thanks (4 Replies)
Discussion started by: jimmy_y
4 Replies

7. Shell Programming and Scripting

[Perl] Accessing array elements within a sed command in Perl script

I am trying to use a script to replace the header of each file, whose filename are stored within the array $test, using the sed command within a Perl script as follows: $count = 0; while ( $count < $#test ) { `sed -e 's/BIOGRF 321/BIOGRF 332/g' ${test} > 0`; `cat 0 >... (2 Replies)
Discussion started by: userix
2 Replies

8. Shell Programming and Scripting

awk command with PERL

Hi All, My Input file looks like below: Input: 100,200,300 $fw=`head -1 test.csv | awk -F, '{print \$1}'`; $fw="'$fw" $fw="$fw'" print $fw Output:'100' I want the first field to be printed in single quotes ('') like above. I could get the ouptput but the problem is single... (6 Replies)
Discussion started by: kmkbuddy_1983
6 Replies

9. UNIX for Advanced & Expert Users

Split Command in Perl

Hi, I have to split a line of the form 1232423#asdf#124324#54534#dcfg#wert#rrftt#4567 into an array in perl. I am using @fields; @fields=split('#',$line); if($fields eq "1") But this is not working. By using the syntax, the statements in "if" are never executed. Please help.... (9 Replies)
Discussion started by: rochitsharma
9 Replies

10. UNIX for Dummies Questions & Answers

perl print command

Hi, I am running this command in perl print "this is a test message @1.00 pm\n" but the output i am getting as this is a test message .00 pm pls help, how to get the proper output (2 Replies)
Discussion started by: vasikaran
2 Replies

Featured Tech Videos