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
# 8  
Old 01-05-2015
Try gsub (/“|”/, "\'")in the first line of the awk part.
# 9  
Old 01-05-2015
You mean as shown below:

Code:
tr "|" "," < ${_fileName} | awk -F"," -v tableNM=$_tableName -v sqlfn=${_SQLFile} 'BEGIN{sqlInsert="INSERT INTO table (columns) values (cValues)"}                                   { if( NR==1)                                     { 
                                      gsub (/"|"/,"\'")
                                      gsub("\"","",$0)                                       clName=$0                                       next                                     }                                     gsub("table",tableNM,sqlInsert)                                     gsub("columns",clName,sqlInsert)                                     gsub("cValues",$0,sqlInsert)                                     print  sqlInsert";\n" > sqlfn;                                   }'


OR

Code:
awk     '      {gsub (/"|"/,"\'") 
               }
               {gsub (/\|/,",")                 }          NR==1  {gsub (/"/,"")                  TMP = "insert into EMPLOYEE (" $0 ") values ("                  next                 }                 {print TMP, $0, ");"                 }         ' FS="\|" file

Last edited by Don Cragun; 03-05-2017 at 02:35 PM.. Reason: Add missing CODE tags.
# 10  
Old 01-05-2015
Sorry, can't read your post. Did you try the Go advanced and there Preview Post?

However, put it into the general action before the NR==1 pattern, like
Code:
awk     '       {gsub (/\|/,",")
                 gsub (/“|”/, "\"")
                }
         NR==1  {gsub (/\'/,"")
                 TMP = "insert into EMPLOYEE (" $0 ") values ("
                 next
                }
                {print TMP, $0, ");"
                }
        ' FS="\|" file

, but then take care to remove the "\'" in the NR==1 action! And, make sure you know what you're working on: these “ ” three byte chars, or the ASCII 0x22 double quotes
Last edited by RudiC; 01-05-2015 at 01:59 PM..
This User Gave Thanks to RudiC For This Post:
chetanojha
# 11  
Old 01-05-2015
This might help you

Code:
akshay@Aix:/tmp$ cat infile
“Name”|”Dept”|”Empno”|”Salary”|”DOB”|”DOJ”
“Alexander”|”10”|”200”|”$1000”|”25/05/1977”|”01/01/2015”
“Graham”|”11”|”300”|”$1500”|”05/09/1970”|”19/02/2011”
“Bell” |”12”|”400”|”$2000”|”31/01/1965”|”01/10/1999”

Code:
akshay@Aix:/tmp$ cat import.awk
function clean(x){ gsub(/[”“\047\042]/,x); gsub(/\|/,",") }
FNR==1{ clean(); h = $0 ; next}
{
	clean("\047");
	print "insert into",table,"("h")","values","("$0");"
}

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

Code:
akshay@Aix:/tmp$ cat test.sql 
insert into Employee (Name,Dept,Empno,Salary,DOB,DOJ) values ('Alexander','10','200','$1000','25/05/1977','01/01/2015');
insert into Employee (Name,Dept,Empno,Salary,DOB,DOJ) values ('Graham','11','300','$1500','05/09/1970','19/02/2011');
insert into Employee (Name,Dept,Empno,Salary,DOB,DOJ) values ('Bell' ,'12','400','$2000','31/01/1965','01/10/1999');

---------- Post updated at 12:49 AM ---------- Previous update was at 12:38 AM ----------

OR try this

Code:
akshay@Aix:/tmp$ cat import_2.awk
function clean(x){ gsub(/[”“\047\042]/,x); gsub(/\|/,",") }
FNR==1{ clean("`"); print "INSERT INTO","`"table"`","("$0")","VALUES"; next}
{
	if(s)print s",";
	clean("\047");
	s = "("$0")"
}
END{
	print s";"
}

Code:
akshay@Aix:/tmp$ awk -vtable="Employee" -f import_2.awk infile
INSERT INTO `Employee` (`Name`,`Dept`,`Empno`,`Salary`,`DOB`,`DOJ`) VALUES
('Alexander','10','200','$1000','25/05/1977','01/01/2015'),
('Graham','11','300','$1500','05/09/1970','19/02/2011'),
('Bell' ,'12','400','$2000','31/01/1965','01/10/1999');

This User Gave Thanks to Akshay Hegde For This Post:
chetanojha
# 12  
Old 01-05-2015
This is what I get from above:
Code:
insert into Employee (Name,Dept,Empno,Salary,DOB,DOJ) values ('''Bell''' ,'''12''','''400''','''$2000''','''31/01/1965''','''01/10/1999''');

Don't forget, those ”“ are three byte chars (at least in my locale)!
This User Gave Thanks to RudiC For This Post:
chetanojha
# 13  
Old 01-06-2015
Modified code
Code:
#!/bin/sh

_fileName=$1
_tableName=$(basename ${_fileName})
_SQLFile=${_tableName}".sql"

tr "|" "," < ${_fileName} | awk -F"," -v tableNM=$_tableName -v sqlfn=${_SQLFile} 'BEGIN{sqlInsert="INSERT INTO table (columns) values (cValues)"}
                                  { if( NR==1)
                                    {
                                      gsub("\"","",$0)
                                      clName=$0
                                      next
                                    }
                                    gsub("table",tableNM,sqlInsert)
                                    gsub("columns",clName,sqlInsert)
                                    gsub("\"","'\''",$0)
                                    gsub("cValues",$0,sqlInsert)
                                    print  sqlInsert";\n" > sqlfn;
                                  }'



sqlplus -s $user/$passwrd <<ENDOFSQL
whenever sqlerror exit sql.sqlcode;
@${_SQLFile}
select sysdate from dual;
exit;
ENDOFSQL

RetCode=$?

Check the return code from SQL Plus
if [ $RetCode != 0 ]
then
    echo "********************"
    echo "ERROR: The SQL Plus Command Failed. RetCode: $RetCode"
else
    echo "********************"
    echo "SQL Plus Successfully Ran. RetCode: $RetCode"
fi

This User Gave Thanks to pravin27 For This Post:
chetanojha
# 14  
Old 01-06-2015
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.
Last edited by chetanojha; 01-06-2015 at 08:09 AM.. Reason: Wrong reply.
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