need to create a insert query for a file

Shell Programming and Scripting
1

Hi Guys,

I need to create a insert query for the below file

   Fri Sep  4 06:25:51 2009
ACTION : 'CREATE INDEX S100S_DC.PLInsuranceReportRules_testI1     ON S100S_DC.PLInsuranceReportRules_test1(ENTITY_KEY)'
DATABASE USER: '/'
PRIVILEGE : SYSDBA
CLIENT USER: oracle
CLIENT TERMINAL: pts/3
STATUS: 0

Fri Sep  4 06:28:41 2009
ACTION : 'GRANT DELETE ON S100S_DC.PLInsuranceReportRules_test1 TO   DC_DATACHG_ROLE'
DATABASE USER: '/'
PRIVILEGE : SYSDBA
CLIENT USER: oracle
CLIENT TERMINAL: pts/3
STATUS: 0

Fri Sep  4 06:39:22 2009
ACTION : 'OCIDescribeAny'
DATABASE USER: 'S1'
PRIVILEGE : SYSDBA
CLIENT USER: oracle
CLIENT TERMINAL: pts/3
STATUS: 0

The file contains lot of blocks as shown. The Each of the values should go into the table.

The insert statement should be as follows

Insert into table values ('Fri Sep  4 06:25:51 2009','CREATE INDEX S100S_DC.PLInsuranceReportRules_testI1     ON S100S_DC.PLInsuranceReportRules_test1(ENTITY_KEY)','/','SYSDBA','oracle','pts/3','0');

Thanks For your help in advance,

Regards,
Magesh.

2

Guys, come on guys.. i was counting on you people.. atleast tell me how to read the inputs and concatenate into a single column...

3 
$ 
$ cat f1
Fri Sep  4 06:25:51 2009
ACTION : 'CREATE INDEX S100S_DC.PLInsuranceReportRules_testI1     ON S100S_DC.PLInsuranceReportRules_test1(ENTITY_KEY)'
DATABASE USER: '/'
PRIVILEGE : SYSDBA
CLIENT USER: oracle
CLIENT TERMINAL: pts/3
STATUS: 0

Fri Sep  4 06:28:41 2009
ACTION : 'GRANT DELETE ON S100S_DC.PLInsuranceReportRules_test1 TO   DC_DATACHG_ROLE'
DATABASE USER: '/'
PRIVILEGE : SYSDBA
CLIENT USER: oracle
CLIENT TERMINAL: pts/3
STATUS: 0

Fri Sep  4 06:39:22 2009
ACTION : 'OCIDescribeAny'
DATABASE USER: 'S1'
PRIVILEGE : SYSDBA
CLIENT USER: oracle
CLIENT TERMINAL: pts/3
STATUS: 0
$ 
$ 
$ ##
$ 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"}' f1
Insert into table values ('Fri Sep  4 06:25:51 2009','CREATE INDEX S100S_DC.PLInsuranceReportRules_testI1     ON S100S_DC.PLInsuranceReportRules_test1(ENTITY_KEY)','/','SYSDBA','oracle','pts/3','0');
Insert into table values ('Fri Sep  4 06:28:41 2009','GRANT DELETE ON S100S_DC.PLInsuranceReportRules_test1 TO   DC_DATACHG_ROLE','/','SYSDBA','oracle','pts/3','0');
Insert into table values ('Fri Sep  4 06:39:22 2009','OCIDescribeAny','S1','SYSDBA','oracle','pts/3','0');
$ 
$

tyler_durden

4

With awk...

 
awk -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);}
}END{printf(");\n")}' infile
5

Thanks Guys for your help...
Both your solution worked fine..
I have used awk earlier hence going with it..

Malcomex, can you please explain me your awk command.. am a newbie to awk..
Thanks for your help again..

6

I am not good at it but i will try to explain...

 
awk -v f="'" '                  ### it creates variable f and assign a single quote 
{if(NF<1){printf(");\n");next}  ### if NF(number of field) is less than 1, which means if the line is blank.It closes it with                               
}                               ### bracket and semi colon and prints new line.
{if($0 ~ /^[A-Z].. /){          ### if the line starts with Capital letter followed by two characters
printf("insert into table values (%c%s%c",f,$0,f)} ### it prints the insert statement with that line
else{split($0,arr,":");                            ### or else split the line with colon
gsub(f,"",arr[2]);                                 ### substitute the comma with nothing
printf(",%c%s%c",f,arr[2],f);}                     ### and print the line
}END{printf(");\n")}                               ### At the END, print closing bracket and semi colon for the last line
' infile
7

malcomex,
i have few doubts..
1.you said "line starts with Capital letter followed by two characters", whether it will account for only upper case characters?
2. i assume the first NF<1 is the one which splits the each set, so that a single insert statement is created?
3. how you are concatenating all the single single records into one insert statement?
For an example, 1st line -- Insert into table ('Fri Sep 4 06:25:51 2009'
2nd line will be splitted by : and created 'CREATE INDEX S100S_DC.PLInsuranceReportRules_testI1 ON S100S_DC.PLInsuranceReportRules_test1(ENTITY_KEY)'
How you are concantenating these two and the others?

Thanks for your help and time..