Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Perl/Sed script help

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Perl/Sed script help
# 1  
Old 06-04-2008
Java Perl/Sed script help
Hi All,

I would need to generate Oracle Inster scripts from an excel formatted spreadsheet as follows:

Quote:
SRVC_ROLE_SPECIFICATION
REC 1
SRVC_ROLE_SPEC_ID : 1
SRVC_ROLE_TYPE : heAdmin
MIN_NUMBER_ALLOWED : 1
MAX_NUMBER_ALLOWED : 1
IDENTIFIER_NAME_SPACE : NULL
CREDENTIAL_TYPE : ENCRYPTEDTEXT
SRVC_SPECIFICATION_ID : 1
REC 2
SRVC_ROLE_SPEC_ID : 2
SRVC_ROLE_TYPE : emailLite
MIN_NUMBER_ALLOWED : 1
MAX_NUMBER_ALLOWED : 10
IDENTIFIER_NAME_SPACE :
CREDENTIAL_TYPE : ENCRYPTEDTEXT
SRVC_SPECIFICATION_ID : 1
REC 3
SRVC_ROLE_SPEC_ID : 3
SRVC_ROLE_TYPE : hostopiaAdmin
MIN_NUMBER_ALLOWED : 1
MAX_NUMBER_ALLOWED : 1
IDENTIFIER_NAME_SPACE :
CREDENTIAL_TYPE : ENCRYPTEDTEXT
SRVC_SPECIFICATION_ID : 2
REC 4
SRVC_ROLE_SPEC_ID : 4
SRVC_ROLE_TYPE : voicemailAdmin
MIN_NUMBER_ALLOWED : 1
MAX_NUMBER_ALLOWED : 1
IDENTIFIER_NAME_SPACE : ?
CREDENTIAL_TYPE : ENCRYPTEDTEXT
SRVC_SPECIFICATION_ID : 2
REC 5
SRVC_ROLE_SPEC_ID : 5
SRVC_ROLE_TYPE : voicemailUser
MIN_NUMBER_ALLOWED : 1
MAX_NUMBER_ALLOWED : NULL (this indicates no upper limit)
IDENTIFIER_NAME_SPACE :
CREDENTIAL_TYPE : ENCRYPTEDTEXT
SRVC_SPECIFICATION_ID : 2
This needs to be stripped as follows:

Quote:
REC 1 : INSERT INTO SRVC_ROLE_SPECIFICATION (SRVC_ROLE_SPEC_ID, SRVC_ROLE_TYPE, MIN_NUMBER_ALLOWED, MIN_NUMBER_ALLOWED , MAX_NUMBER_ALLOWED, IDENTIFIER_NAME_SPACE , CREDENTIAL_TYPE, SRVC_SPECIFICATION_ID)
VALUES
(1,'heAdmin',1,1,'NULL','ENCRYPTEDTEXT',1);

REC 2 : ..

REC 1, REC 2 etc are the separators of the records ...

I beleive a pearl script can be written for this ... anything useful will be of great help.

Thanks in Advance
Sabya
# 2  
Old 06-04-2008
Something like this:
(use nawk or /usr/xpg4/bin/awk on Solaris)


Code:
awk 'END { 
  printf fmt, ++c, tab, cols, values 
  }
NR == 1 { 
  tab = $1 
  fmt = "REC %d: INSERT INTO %s(%s)\nVALUES\n(%s);\n" 
  next 
  }
/^REC/ && cols { 
  printf fmt, ++c, tab, cols, values 
  cols = "" 
  values = "" 
  next 
  } 
{ 
  $2 = $2 ~ /^[0-9]/ ? $2 : q $2 q 
  cols = cols ? cols "," $1 : $1 
  values = values ? values "," $2 : $2 
  }' FS=" *: *" q="'" input

# 3  
Old 06-04-2008
Data
Hello Radoulov,

Thanks for your reply and sorry for my lack of knowledge in awk.

I am using solaris 10 - I have tried as follows:

I have created a script : chk.awk

Quote:
#! /usr/xpg4/bin/awk
awk 'END {
printf fmt, ++c, tab, cols, values
}
NR == 1 {
tab = $1
fmt = "REC %d: INSERT INTO %s(%s)\nVALUES\n(%s);\n"
next
}
/^REC/ && cols {
printf fmt, ++c, tab, cols, values
cols = ""
values = ""
next
}
{
$2 = $2 ~ /^[0-9]/ ? $2 : q $2 q
cols = cols ? cols "," $1 : $1
values = values ? values "," $2 : $2
}' FS=" *: *" q="'"
input text is stored in a file : qqq

While I am calling the script - I am getting the following error:

Quote:
{mukher2}/export/home/mukher2:awk -f chk.awk qqq
awk: syntax error near line 2
awk: bailing out near line 2
{mukher2}/export/home/mukher2:
Please can you advise ?
# 4  
Old 06-04-2008
Radoulov has given you the complete awk command!! (you should be more observent)

In your form of execution, take out the awk and the single quote from the begin & end of the command in your script-file.
# 5  
Old 06-04-2008
Change the script like this:

Code:
#! /usr/xpg4/bin/awk -f

END {
  printf fmt, ++c, tab, cols, values
  }
NR == 1 {
  tab = $1
  fmt = "REC %d: INSERT INTO %s(%s)\nVALUES\n(%s);\n"
  FS = " *: *"
  q = "'"
  next
  }
/^REC/ && cols {
  printf fmt, ++c, tab, cols, values
  cols = ""
  values = ""
  next
  }
{
  $2 = $2 ~ /^[0-9]/ ? $2 : q $2 q
  cols = cols ? cols "," $1 : $1
  values = values ? values "," $2 : $2
  }

And invoke it like this:

Code:
./script datafile

# 6  
Old 06-04-2008
Also, remove the following:

FS=" *: *" q="'"

from chk.awk and run your command as:
Code:
awk -f chk.awk FS=" *: *" q="'" qqq

# 7  
Old 06-04-2008
Thanks Radoulov and Unilover Smilie

Works like a charm.

Just a small glitch ...Smilie - not so much proficient to debug and fix the problem ...

Everything (all the other records) is coming out except the first record.

The output is coming like:

Quote:
REC 1: INSERT INTO SRVC_ROLE_SPECIFICATION(REC 1,SRVC_ROLE_SPEC_ID,SRVC_ROLE_TYPE,MIN_NUMBER_ALLOWED,MAX_NUMBER_ALLOWED,IDENTIFIER_NAME_SPACE,CREDENT IAL_TYPE,SRVC_SPECIFICATION_ID)
VALUES
('',1,'heAdmin',1,1,'','ENCRYPTEDTEXT',1);
Whereas it should be: (just the elements marked in red needs to be removed)

Quote:
REC 1: INSERT INTO SRVC_ROLE_SPECIFICATION(SRVC_ROLE_SPEC_ID,SRVC_ROLE_TYPE,MIN_NUMBER_ALLOWED,MAX_NUMBER_ALLOWED,IDENT IFIER_NAME_SPACE,CREDENTIAL_TYPE,SRVC_SPECIFICATION_ID)
VALUES
(1,'heAdmin',1,1,'','ENCRYPTEDTEXT',1);


Other than the first record - all other records are coming absolutely fine.

Thanks again,
Sabya
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Programming

PERL: In a perl-scripttTrying to execute another perl-script that SETS SOME VARIABLES !

I have reviewed many examples on-line about running another process (either PERL or shell command or a program), but do not find any usefull for my needs way. (Reviewed and not useful the system(), 'back ticks', exec() and open()) I would like to run another PERL-script from first one, not... (1 Reply)
Discussion started by: alex_5161
1 Replies

2. Shell Programming and Scripting

Rewrite sed to perl or run sed in perl

I am having trouble re-writing this sed code sed -nr 's/.*del(+)ins(+).*NC_0{4}(+).*g\.(+)_(+).*/\3\t\4\t\5\t\1\t\2/p' C:/Users/cmccabe/Desktop/Python27/out_position.txt > C:/Users/cmccabe/Desktop/Python27/out_parse.txt in perl Basically, what the code does is parse text from two fields... (12 Replies)
Discussion started by: cmccabe
12 Replies

3. Shell Programming and Scripting

Rsync script to rewrite suffix - BASH, awk, sed, perl?

trying to write up a script to put the suffix back. heres what I have but can't get it to do anything :( would like it to be name.date.suffix rsync -zrlpoDtub --suffix=".`date +%Y%m%d%k%M%S`.~" --bwlimit=1024 /mymounts/test1/ /mymounts/test2/ while IFS=. read -r -u 9 -d '' name... (1 Reply)
Discussion started by: jmituzas
1 Replies

4. Shell Programming and Scripting

Perl : embedding java script with cgi perl script

Hi All, I am aware that html tags can be embedded in cgi script as below.. In the same way is it possible to embed the below javascript in perl cgi script ?? print("<form action="action.htm" method="post" onSubmit="return submitForm(this.Submitbutton)">"); print("<input type = "text"... (1 Reply)
Discussion started by: scriptscript
1 Replies

5. Shell Programming and Scripting

calling a perl script with arguments from a parent perl script

I am trying to run a perl script which needs input arguments from a parent perl script, but doesn't seem to work. Appreciate your help in this regard. From parent.pl $input1=123; $input2=abc; I tried calling it with system("/usr/bin/perl child.pl $input1 $input2"); and `perl... (1 Reply)
Discussion started by: grajp002
1 Replies

6. Shell Programming and Scripting

PERL script -- calling 'sed' by passing 'variable value'.

Hi Friends, I'm calling 'sed' command inside one perl script, which is to list directory names which are having some date value as their names (in the form YYYYMMDD) with in the range (start and end date). #!/usr/bin/perl -w use strict; use warnings; my $DATA = "/export/home/ganapa"; my... (5 Replies)
Discussion started by: ganapati
5 Replies

7. Shell Programming and Scripting

How to get value from xml node using sed/perl/script?

hello, new to this forum. but i have a requirement to extract the value from multiple xml node and print out the values to new file with comma seperated. would like to know how this would be done using either sed/perl or some unix script. an example would be tremendous... sample input file:... (2 Replies)
Discussion started by: davidsouk
2 Replies

8. Shell Programming and Scripting

sed not working in perl script

Hi, I have a comfig file as follows: Sun 0000-2359 Mon 0000-0859;1830-2359 Tue 0000-2359;1830-2359 Wed 0000-2359;1830-2359 Thu 0000-2359;1830-2359 Fri 0000-2359;1830-2359 Sat 0000-2359 Sun 0000-2359 Mon 0000-0859;1830-2359 Tue ... (1 Reply)
Discussion started by: som.nitk
1 Replies

9. Shell Programming and Scripting

sed command in perl script

What is wrong with this line in a perl script? $amc_data = `sed -n '/\/,/\/p' "$config_file"` I ran the above from command line and it works fine from unix command prompt. The code should produce output between the and tags. The config_file is as follows: Sun ... (2 Replies)
Discussion started by: som.nitk
2 Replies

10. 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
Login or Register to Ask a Question