Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Intelligent Script to Insert Records in Database Table

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Intelligent Script to Insert Records in Database Table
# 15  
Old 01-06-2015
Quote:
Originally Posted by chetanojha
Hi Akshay,

The first code works perfect for me. But I need these into one script. so that I can execute the test.sql from the single script only. So that once I fire the script, it will create the test.sql (or any other intermediate file), fire it against the database, and insert the records in the table.

One more questions, what do I need to change in case of ~ separated fields?

Thanks

---------- Post updated at 11:25 AM ---------- Previous update was at 07:09 AM ----------

Chaps. this must be my last question on this topic now.

How can I also include tilda (~) along with the pipe separators?

Some files have tilda and some have pipe.

Ok use this

Code:
function clean(x){ gsub(/[”“\047\042]/,x); gsub(FS,",") }
FNR==1{ clean(); h = $0 ; next}
{
	clean("\047");
	print "insert into",table,"("h")","values","("$0");"
}


Code:
akshay@Aix:/tmp$ awk -vFS="~" -vtable="Employee" -f import.awk infile >test.sql

This User Gave Thanks to Akshay Hegde For This Post:
chetanojha
# 16  
Old 01-06-2015
This doesn't work. As while executing, it create INSERT statement with PIPE in it instead of Comma as shown below:

Code:
INSERT INTO EMPLOYEE (NAME|DEPT|EMPNO|SALARY|DOB|DOJ).......


Quote:
Originally Posted by Akshay Hegde
Ok use this

Code:
function clean(x){ gsub(/[”“\047\042]/,x); gsub(FS,",") }
FNR==1{ clean(); h = $0 ; next}
{
    clean("\047");
    print "insert into",table,"("h")","values","("$0");"
}

Code:
akshay@Aix:/tmp$ awk -vFS="~" -vtable="Employee" -f import.awk infile >test.sql

# 17  
Old 01-06-2015
or try this

if sep is ~
Code:
awk -vFS="\\\~" -vtable="Employee" -f import.awk infile

else if sep is |
Code:
awk -vFS="\\\|" -vtable="Employee" -f import.awk infile

This User Gave Thanks to Akshay Hegde For This Post:
chetanojha
# 18  
Old 01-06-2015
Try
Code:
awk     '       {gsub (/[|~]/,",")
                 gsub (/“|”/, "\047")
                }
         NR==1  {gsub (/\047/,"")
                 TMP = "insert into EMPLOYEE (" $0 ") values ("
                 next
                }
                {print TMP, $0, ");"
                }
        '  file

This User Gave Thanks to RudiC For This Post:
chetanojha
# 19  
Old 01-07-2015
Guys,

Thanks a lot for your prompt and detailed help. I am not able to put together script which can help me to dynamically create the insert script with different separators. And this will also insert data (using the insert statements created with your help) into the database table and cleanup the intermediate files.

Once again thanks a ton for your help especially RudyC, Akshay and Pravin. You guys really rock!! Keep the good work going.

Thanks.
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

1. This will insert the records into db table by reading from ta csv file

I have this code with me but the condition is If any of the mandatory columns are null then entire file will be rejected. LOAD DATA infile ' ' #specifies the name of a datafile containing data that you want to load BADFILE ' ' #specifies the name of... (1 Reply)
Discussion started by: raka123
1 Replies

2. Shell Programming and Scripting

How to read a text file line by line and insert into a database table?

I have a test file that I want to read and insert only certain lines into the the table based on a filter. 1. Rread the log file 12 Hours back Getdate() -12 Hours 2. Extract the following information on for lines that say "DUMP is complete" A. Date B. Database Name C.... (2 Replies)
Discussion started by: JolietJake
2 Replies

3. Shell Programming and Scripting

UNIX Script required for count the records in table

Hi Friends, I looking for the script for the count of the records in table. and then it's containg the zero records then should get abort. and should notify us through mail. Can you please help me out in this area i am lacking. (5 Replies)
Discussion started by: victory
5 Replies

4. Shell Programming and Scripting

Shell script for insert multiple records into a Database

I have a table in an Informix DB into which I want to insert multiple records at a time. Data for one of the column should be unique & other column data may be the same for all the records I insert Typical Insert Statement I use to insert one row : insert into employee(empid, country, state)... (5 Replies)
Discussion started by: nisav
5 Replies

5. Shell Programming and Scripting

Insert script result into Oracle Table

Hi All, I want to insert STAT and ENDTIME values for each job in joblist into TBL_DAILY_STATUS table. Eg: insert into tbl_daily_status values(STAT,ENDTIME); Please help me on this. #!/bin/ksh joblist="com_abc_job com_abc_dot_job com_abc_seq com_abc_det" for i in $joblist do... (8 Replies)
Discussion started by: vichuelaa
8 Replies

6. Shell Programming and Scripting

Script to count the dataflow in a table in oracle database

I need to write a unix shell script to connect to oracle database and retrieve data from a table, the script should count the total number of rows till date in that table number of rows which are getting filled per hour on current day and the peak hour of the current day number of rows which are... (5 Replies)
Discussion started by: PhAnT0M
5 Replies

7. UNIX for Dummies Questions & Answers

Bash script to insert data into an html table

hi, I need to create a bash shell script which picks up data from a text file and in the output file puts it into an html made table. I have to use sed and awk utilties to do this the input text file will contain data in the format: job name para1 para2 para3 para4 para4 1 ... (1 Reply)
Discussion started by: intern123
1 Replies

8. Web Development

INSERT data to a Database Table from a text file

If you have a text file and if you want to Insert data to your Database Table, You can do it with these queries LOAD DATA LOCAL INFILE '/path/yourTextFile.txt' INTO TABLE yourTableName FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\n' (0 Replies)
Discussion started by: sitex
0 Replies

9. UNIX for Advanced & Expert Users

unix script for update or insert records from a file to a oracle table

Hi, I have delimited file(|). Sample data: 1|name|50009|DS24|0|12 2|name|30009|DS24|0|13 3|name|20409|DS24|0|14 4|name|20009|DS24|0|15 5|name|10009|DS24|0|16 I want to load this data into a oracle table (update and insert) Please help me the commands and also... (1 Reply)
Discussion started by: unihp1
1 Replies

10. Shell Programming and Scripting

Shell Script: want to insert values in database when update script runs

Hi , I am new to linux and also also to shell scripting. I have one shell script which unpacks .tgz file and install software on machine. When this script runs I want to insert id,filename,description(which will be in readme file),log(which will be in log file) and name of unpacked folder... (1 Reply)
Discussion started by: ring
1 Replies
Login or Register to Ask a Question