Perl/Sed script help

sabyasm · #1 (**permalink**) 06-04-2008

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

radoulov · #2 (**permalink**) 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

sabyasm · #3 (**permalink**) 06-04-2008

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 ?

unilover · #4 (**permalink**) 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.

radoulov · #5 (**permalink**) 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

unilover · #6 (**permalink**) 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

sabyasm · #7 (**permalink**) 06-04-2008

Thanks Radoulov and Unilover

Works like a charm.

Just a small glitch ...

- 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

More UNIX and Linux Forum Topics You Might Find Helpful
Thread	Thread Starter	Forum	Replies	Last Post
Calling a perl script from a perl script	new2ss	Shell Programming and Scripting	6	05-24-2009 05:03 PM
Include PERL script with in the unix shell script	ganapati	UNIX for Dummies Questions & Answers	1	04-29-2008 12:18 PM
here document to automate perl script that call script	hogger84	Shell Programming and Scripting	3	10-22-2007 10:15 AM
Modify Perl script to work with txt - Permissions script	joangopan	Shell Programming and Scripting	1	09-12-2007 11:38 PM
Perl: Run perl script in the current process	vino	Shell Programming and Scripting	10	12-09-2005 10:45 AM

Thread Tools	Search this Thread
Show Printable Version Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread: