Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

need to create a insert query for a file

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting need to create a insert query for a file
# 1  
Old 09-04-2009
need to create a insert query for a file
Hi Guys,

I need to create a insert query for the below file

Code:
   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

Code:
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  
Old 09-05-2009
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  
Old 09-05-2009
Code:
$ 
$ 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  
Old 09-06-2009
With awk...

Code:
 
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

Last edited by malcomex999; 09-06-2009 at 07:08 AM..
# 5  
Old 09-07-2009
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  
Old 09-07-2009
I am not good at it but i will try to explain...
Code:
 
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  
Old 09-07-2009
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..
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

How to create a file from output of vertica table query in UTF-8 format?

Hello, In my shell script, I extract table data from HP Vertica DB into a csv file using vsql -c command. But the problem is the file getting created is in binary format and hence some of the data becomes unreadable which has chinese characters as part of data. file -i filename.csv - gives... (2 Replies)
Discussion started by: Dharmatheja
2 Replies

2. Shell Programming and Scripting

From sql Insert Query to XML format

Hi How do I translate Let say Cat inserts.sql gives Insert into PM9_TAXATION_ROUNDING (STATE_GECODE, TAX_TYPE, TAX_AUTHORITY, SYS_CREATION_DATE, SYS_UPDATE_DATE, APPLICATION_ID, DL_SERVICE_CODE, ROUNDING_METHOD) Values ('xx', 'xx', 'x', TO_DATE('10/26/2012 13:01:20',... (3 Replies)
Discussion started by: anuj87in
3 Replies

3. Shell Programming and Scripting

Insert query with shell variable with AWK

Hi, I'm a first timer with Unix so pardon my ignorance. I'm trying to read a comma separated file from the same folder where the script is and insert the value in a DB2 table. I'm using AWK for the same. I'm getting `)' not expected error. I'm not sure but for me it doesn't look like detailed... (8 Replies)
Discussion started by: Kabira Speaking
8 Replies

4. Shell Programming and Scripting

Create SQL DML insert statements from file using AWK or similar

Hi all. This is my first post on this forum. I've previously found great help in the huge knowledgebase that is here, but this time I have not been able to find a solution to my problem. I have a large text file that looks like this: typedef struct ABC_struct_nbr1_ { char attr1; /*... (0 Replies)
Discussion started by: Yagi Uda
0 Replies

5. Programming

insert query help

Hello i want help to load data from file into mysql DB this part i know how to do but during loading i want to combine 2 fields into 1 field and insert into db as primary key in new column thanks advice how to do so (5 Replies)
Discussion started by: mogabr
5 Replies

6. Shell Programming and Scripting

Forming an insert query using awk

Hi, I'm trying to form an insert sql query using shell programming. I have table named company with four columns 'company name', 'company id', 'company code' and 'last change id' I have to read the company name, company code and last change id from a file delimited by | which has around 10... (4 Replies)
Discussion started by: rakesh_s
4 Replies

7. Shell Programming and Scripting

How to use a variable in insert query?

My script contains as follows, VALUE=`sqlplus un/pwd <<EOF > OB.txt set pagesize 0 feedback off verify off heading off echo off select max(1) from table1; exit; EOF` insert into table2 values(1, 'The max value is $value',...); i need the value of VALUE to be inserted after 'The max... (2 Replies)
Discussion started by: savithavijay
2 Replies

8. Programming

SQL : Fine tune Insert by query

i would like to know how can i fine tune the following query since the cost of the query is too high .. insert into temp temp_1 select a,b,c,d from xxxx .. database used is IDS.. (1 Reply)
Discussion started by: expert
1 Replies

9. Shell Programming and Scripting

to create a export query from a file

Hi Guys,,, I have a file which is of this type. abdc defd afds adfse I want to create a export statement like this. Db2 "export to /clocal/rem/user/rem/used.txt of del select * from t.tel where name in ('abdc','defd','afds','adfse') with ur" Please help me.. Thanks for... (2 Replies)
Discussion started by: mac4rfree
2 Replies

10. Shell Programming and Scripting

create Insert script from a file

Hi , I have a text file text.txt which contains values as ULTRA,OTHERS,Mumbai,16912 ULTIMATE,OTHERS,Mumbai,16913 ULTIMATIUM,OTHERS,Mumbai,16914 I want to read the file line by line and create insert scripts like INSERT INTO TAB ( DESC,PLACE,NUMBER ) VALUES... (3 Replies)
Discussion started by: ultimatix
3 Replies
Login or Register to Ask a Question