Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Korn shell program to parse CSV text file and insert values into Oracle database

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Korn shell program to parse CSV text file and insert values into Oracle database
# 1  
Old 04-28-2011
Korn shell program to parse CSV text file and insert values into Oracle database
Enclosed is comma separated text file. I need to write a korn shell program that will parse the text file and insert the values into Oracle database.
I need to write the korn shell program on Red Hat Enterprise Linux server.
Oracle database is 10g.
SampleFile.txt (729 Bytes) 
# 2  
Old 04-28-2011
Try this.....
Code:
for i in `cat file.txt`
do
col1=`cat $i | awk -F ',' '{print $1}'`
col2=`cat $i | awk -F ',' '{print $2}'`
col3=`cat $i | awk -F ',' '{print $3}'`
col4=`cat $i | awk -F ',' '{print $4}'`
col5=`cat $i | awk -F ',' '{print $5}'`
col6=`cat $i | awk -F ',' '{print $6}'`
col7=`cat $i | awk -F ',' '{print $7}'`
col8=`cat $i | awk -F ',' '{print $8}'`
col9=`cat $i | awk -F ',' '{print $9}'`
col10=`cat $i | awk -F ',' '{print $10}'`
col11=`cat $i | awk -F ',' '{print $11}'`
col12=`cat $i | awk -F ',' '{print $12}'`
col13=`cat $i | awk -F ',' '{print $13}'`
col14=`cat $i | awk -F ',' '{print $14}'`



sqlplus -s scott/tiger@ORACLE10G <<eof
INSERT INTO TEST_TAB 
(COLUMN1, COLUMN2.....................)
VALUES
($col1, $col2, $col3,.................)
commit;
exit;
eof

done

# 3  
Old 04-28-2011
Please look into sqlldr.
This tool for oracle database will allow you to import CSV data much faster then generating insert statements.
SQL*Loader Command-Line Reference
It should be avalible on any machine with proper installation of Oracle client.

Running above code on for instance million records, will take alot of time.
# 4  
Old 04-28-2011
When i print the columns, some have commas and in some there are 2 columns and one column has a comma.
Code:
for i in `cat endevor.txt`
do
col1=`cat $i | awk -F ',' '{print $1}'`
col2=`cat $i | awk -F ',' '{print $2}'`
col3=`cat $i | awk -F ',' '{print $3}'`
col4=`cat $i | awk -F ',' '{print $4}'`
col5=`cat $i | awk -F ',' '{print $5}'`
col6=`cat $i | awk -F ',' '{print $6}'`
col7=`cat $i | awk -F ',' '{print $7}'`
col8=`cat $i | awk -F ',' '{print $8}'`
col9=`cat $i | awk -F ',' '{print $9}'`
col10=`cat $i | awk -F ',' '{print $10}'`
col11=`cat $i | awk -F ',' '{print $11}'`
col12=`cat $i | awk -F ',' '{print $12}'`
col13=`cat $i | awk -F ',' '{print $13}'`

done
echo "$col1";
echo "$col2";
echo "$col3";
echo "$col4";
echo "$col5";
echo "$col6";
echo "$col7";
echo "$col8";
echo "$col9";
echo "$col10";
echo "$col11";
echo "$col12";
echo "$col13";


This is what i get if i echo:
Code:
cat: TEST: No such file or directory
cat: TEST: No such file or directory
cat: TEST: No such file or directory
cat: TEST: No such file or directory
cat: TEST: No such file or directory
cat: TEST: No such file or directory
cat: TEST: No such file or directory
cat: TEST: No such file or directory
cat: TEST: No such file or directory
cat: TEST: No such file or directory
cat: TEST: No such file or directory
cat: TEST: No such file or directory
cat: TEST: No such file or directory
cat: ,CLAIMS: No such file or directory
cat: ,CLAIMS: No such file or directory
cat: ,CLAIMS: No such file or directory
cat: ,CLAIMS: No such file or directory
cat: ,CLAIMS: No such file or directory
cat: ,CLAIMS: No such file or directory
cat: ,CLAIMS: No such file or directory
cat: ,CLAIMS: No such file or directory
cat: ,CLAIMS: No such file or directory
cat: ,CLAIMS: No such file or directory
cat: ,CLAIMS: No such file or directory
cat: ,CLAIMS: No such file or directory
cat: ,CLAIMS: No such file or directory
cat: ,IT: No such file or directory
cat: ,IT: No such file or directory
cat: ,IT: No such file or directory
cat: ,IT: No such file or directory
cat: ,IT: No such file or directory
cat: ,IT: No such file or directory
cat: ,IT: No such file or directory
cat: ,IT: No such file or directory
cat: ,IT: No such file or directory
cat: ,IT: No such file or directory
cat: ,IT: No such file or directory
cat: ,IT: No such file or directory
cat: ,IT: No such file or directory
cat: ,INTERPLAN: No such file or directory
cat: ,INTERPLAN: No such file or directory
cat: ,INTERPLAN: No such file or directory
cat: ,INTERPLAN: No such file or directory
cat: ,INTERPLAN: No such file or directory
cat: ,INTERPLAN: No such file or directory
cat: ,INTERPLAN: No such file or directory
cat: ,INTERPLAN: No such file or directory
cat: ,INTERPLAN: No such file or directory
cat: ,INTERPLAN: No such file or directory
cat: ,INTERPLAN: No such file or directory
cat: ,INTERPLAN: No such file or directory
cat: ,INTERPLAN: No such file or directory
cat: TELEPROCESSING: No such file or directory
cat: TELEPROCESSING: No such file or directory
cat: TELEPROCESSING: No such file or directory
cat: TELEPROCESSING: No such file or directory
cat: TELEPROCESSING: No such file or directory
cat: TELEPROCESSING: No such file or directory
cat: TELEPROCESSING: No such file or directory
cat: TELEPROCESSING: No such file or directory
cat: TELEPROCESSING: No such file or directory
cat: TELEPROCESSING: No such file or directory
cat: TELEPROCESSING: No such file or directory
cat: TELEPROCESSING: No such file or directory
cat: TELEPROCESSING: No such file or directory
cat: (ITS): No such file or directory
cat: (ITS): No such file or directory
cat: (ITS): No such file or directory
cat: (ITS): No such file or directory
cat: (ITS): No such file or directory
cat: (ITS): No such file or directory
cat: (ITS): No such file or directory
cat: (ITS): No such file or directory
cat: (ITS): No such file or directory
cat: (ITS): No such file or directory
cat: (ITS): No such file or directory
cat: (ITS): No such file or directory
cat: (ITS): No such file or directory
cat: ,CITB269: No such file or directory
cat: ,CITB269: No such file or directory
cat: ,CITB269: No such file or directory
cat: ,CITB269: No such file or directory
cat: ,CITB269: No such file or directory
cat: ,CITB269: No such file or directory
cat: ,CITB269: No such file or directory
cat: ,CITB269: No such file or directory
cat: ,CITB269: No such file or directory
cat: ,CITB269: No such file or directory
cat: ,CITB269: No such file or directory
cat: ,CITB269: No such file or directory
cat: ,CITB269: No such file or directory
cat: ,COBMAIN: No such file or directory
cat: ,COBMAIN: No such file or directory
cat: ,COBMAIN: No such file or directory
cat: ,COBMAIN: No such file or directory
cat: ,COBMAIN: No such file or directory
cat: ,COBMAIN: No such file or directory
cat: ,COBMAIN: No such file or directory
cat: ,COBMAIN: No such file or directory
cat: ,COBMAIN: No such file or directory
cat: ,COBMAIN: No such file or directory
cat: ,COBMAIN: No such file or directory
cat: ,COBMAIN: No such file or directory
cat: ,COBMAIN: No such file or directory
cat: ,T,PRICER: No such file or directory
cat: ,T,PRICER: No such file or directory
cat: ,T,PRICER: No such file or directory
cat: ,T,PRICER: No such file or directory
cat: ,T,PRICER: No such file or directory
cat: ,T,PRICER: No such file or directory
cat: ,T,PRICER: No such file or directory
cat: ,T,PRICER: No such file or directory
cat: ,T,PRICER: No such file or directory
cat: ,T,PRICER: No such file or directory
cat: ,T,PRICER: No such file or directory
cat: ,T,PRICER: No such file or directory
cat: ,T,PRICER: No such file or directory
cat: IMPLEMENTATION: No such file or directory
cat: IMPLEMENTATION: No such file or directory
cat: IMPLEMENTATION: No such file or directory
cat: IMPLEMENTATION: No such file or directory
cat: IMPLEMENTATION: No such file or directory
cat: IMPLEMENTATION: No such file or directory
cat: IMPLEMENTATION: No such file or directory
cat: IMPLEMENTATION: No such file or directory
cat: IMPLEMENTATION: No such file or directory
cat: IMPLEMENTATION: No such file or directory
cat: IMPLEMENTATION: No such file or directory
cat: IMPLEMENTATION: No such file or directory
cat: IMPLEMENTATION: No such file or directory
cat: ,CHG41797: No such file or directory
cat: ,CHG41797: No such file or directory
cat: ,CHG41797: No such file or directory
cat: ,CHG41797: No such file or directory
cat: ,CHG41797: No such file or directory
cat: ,CHG41797: No such file or directory
cat: ,CHG41797: No such file or directory
cat: ,CHG41797: No such file or directory
cat: ,CHG41797: No such file or directory
cat: ,CHG41797: No such file or directory
cat: ,CHG41797: No such file or directory
cat: ,CHG41797: No such file or directory
cat: ,CHG41797: No such file or directory
cat: ,A0CWQ3: No such file or directory
cat: ,A0CWQ3: No such file or directory
cat: ,A0CWQ3: No such file or directory
cat: ,A0CWQ3: No such file or directory
cat: ,A0CWQ3: No such file or directory
cat: ,A0CWQ3: No such file or directory
cat: ,A0CWQ3: No such file or directory
cat: ,A0CWQ3: No such file or directory
cat: ,A0CWQ3: No such file or directory
cat: ,A0CWQ3: No such file or directory
cat: ,A0CWQ3: No such file or directory
cat: ,A0CWQ3: No such file or directory
cat: ,A0CWQ3: No such file or directory
cat: ,: No such file or directory
cat: ,: No such file or directory
cat: ,: No such file or directory
cat: ,: No such file or directory
cat: ,: No such file or directory
cat: ,: No such file or directory
cat: ,: No such file or directory
cat: ,: No such file or directory
cat: ,: No such file or directory
cat: ,: No such file or directory
cat: ,: No such file or directory
cat: ,: No such file or directory
cat: ,: No such file or directory
cat: ,UPDATE: No such file or directory
cat: ,UPDATE: No such file or directory
cat: ,UPDATE: No such file or directory
cat: ,UPDATE: No such file or directory
cat: ,UPDATE: No such file or directory
cat: ,UPDATE: No such file or directory
cat: ,UPDATE: No such file or directory
cat: ,UPDATE: No such file or directory
cat: ,UPDATE: No such file or directory
cat: ,UPDATE: No such file or directory
cat: ,UPDATE: No such file or directory
cat: ,UPDATE: No such file or directory
cat: ,UPDATE: No such file or directory
cat: ,2011/04/21,09:21: No such file or directory
cat: ,2011/04/21,09:21: No such file or directory
cat: ,2011/04/21,09:21: No such file or directory
cat: ,2011/04/21,09:21: No such file or directory
cat: ,2011/04/21,09:21: No such file or directory
cat: ,2011/04/21,09:21: No such file or directory
cat: ,2011/04/21,09:21: No such file or directory
cat: ,2011/04/21,09:21: No such file or directory
cat: ,2011/04/21,09:21: No such file or directory
cat: ,2011/04/21,09:21: No such file or directory
cat: ,2011/04/21,09:21: No such file or directory
cat: ,2011/04/21,09:21: No such file or directory
cat: ,2011/04/21,09:21: No such file or directory

Last edited by Franklin52; 04-29-2011 at 03:58 AM.. Reason: Please use code tags
# 5  
Old 04-28-2011
Try with the updated one
Code:
for i in `cat  file.txt`
do
col1=`echo  $i | awk -F ',' '{print $1}'`
col2=`echo  $i | awk -F ',' '{print $2}'`
col3=`echo  $i | awk -F ',' '{print $3}'`
col4=`echo  $i | awk -F ',' '{print $4}'`
col5=`echo  $i | awk -F ',' '{print $5}'`
col6=`echo  $i | awk -F ',' '{print $6}'`
col7=`echo  $i | awk -F ',' '{print $7}'`
col8=`echo  $i | awk -F ',' '{print $8}'`
col9=`echo  $i | awk -F ',' '{print $9}'`
col10=`echo  $i | awk -F ',' '{print $10}'`
col11=`echo  $i | awk -F ',' '{print $11}'`
col12=`echo  $i | awk -F ',' '{print $12}'`
col13=`echo  $i | awk -F ',' '{print $13}'`
col14=`echo  $i | awk -F ',' '{print $14}'`



sqlplus -s scott/tiger@ORACLE10G <<eof
INSERT INTO TEST_TAB 
(COLUMN1, COLUMN2.....................)
VALUES
($col1, $col2, $col3,.................)
commit;
exit;
eof

done

# 6  
Old 04-28-2011
I get the last two fields as output and only for the first record.
Now this is the output:
2011/04/21
05:28

---------- Post updated at 01:09 AM ---------- Previous update was at 01:07 AM ----------

I get the last two fields of the third record.

---------- Post updated at 01:20 AM ---------- Previous update was at 01:09 AM ----------

I am getting correctly the values into the variables. Once the oracle is installed i will try to insert values into the database.

Palanisvr - Thanks a lot for your help. I will let you know if i have any questions if i have any problems with the database.
Thanks.
# 7  
Old 04-28-2011
Hi,

If your table columns and file columns are in same order then you can try like below,

Code:
while read line
do
insertval=$(echo $line | sed "s/ *//g;s/,/','/g;s/^/'/;s/$/'/")
sqlplus -s system/pravin@orcl<<SQL
insert into sample_tab values ($insertval);
commit;
exit;
SQL
done < sample.txt
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Parse log file to insert into database

I have a log file that's created daily by this command: sar -u 300 288 >> /var/log/usage/$(date "+%Y-%m-%d")_$(hostname)_cpu.log It that contains data like this: Linux 3.16.0-4-amd64 (myhostname) 08/15/2015 _x86_64_ (1 CPU) 11:34:17 PM CPU %user %nice ... (12 Replies)
Discussion started by: unplugme71
12 Replies

2. 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

3. Shell Programming and Scripting

insert values into sqlplus database using shell script

hello all, I am new to shell scripting and to unix... so the following is my assignment.. here i am trying to insert a values into sqlplus database using shell script. the following is my shell script InsertDelete.sh #! /bin/sh echo "*********The MENU******** 1.Insert The Values... (2 Replies)
Discussion started by: pradeept
2 Replies

4. Shell Programming and Scripting

how to insert data into database by reading it from a text file??

Hi....can you guys help me out in this script?? Below is a text file and it contains these: GEF001 000093625 MKL002510 000001 000000 000000 000000 000000 000000 000001 GEF001 000093625 MKL003604 000001 000000 000000 000000 000000 000000 000001 GEF001 000093625 MKL005675 000001... (4 Replies)
Discussion started by: pallavishetty
4 Replies

5. Shell Programming and Scripting

How to: Parse text string into variables using Korn shell

I am writing a script to keep check on free disk space, and I would like to find a way to parse $LINE (see code below) into a numeric value (for free disk space percentage) and a string value (for mount point). If possible, I would like to avoid sed or any additional use of awk since I am not very... (7 Replies)
Discussion started by: shew01
7 Replies

6. Shell Programming and Scripting

How to insert data into MYSql database from a text file

Hi, Need to get help from you guys about this issue. I need to insert data into MySql database from a text file which is located in other server. The text file is something look like below: Date | SubscriberNo | Call Duration 20/7/07 | 123456788 | 20 20/7/07 | 123412344 | 30 The... (4 Replies)
Discussion started by: shirleyeow
4 Replies

7. 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

8. Shell Programming and Scripting

Need Shell Script to upload data from Text file to Oracle database

Hi Does any one have any idea on uploading the data using Unix Shell script from text file to Oracle database. Requirement:- 1. Need to connect to Oracle database from Unix Shell script. 2. Need to pick Text file from some location on Unix Box. 3. Need to upload the data from text file to... (6 Replies)
Discussion started by: chandrashekharj
6 Replies

9. Shell Programming and Scripting

how to insert data in database based on text file?

Hi....can you guys help me out in this script?? Below is a text file script....called Bukom.txt and it contains these: BUKOM 20060101 2.5 2.6 2.7 2.8 2.9 2.3 2.1 BUKOM 20060102 2.4 2.5 2.6 2.7 2.7 2.6 2.4 BUKOM 20060103 2.1 ... (9 Replies)
Discussion started by: forevercalz
9 Replies

10. Shell Programming and Scripting

unix script to export data from csv file to oracle database

Hello people, Need favour. The problem I have is that, I need to develop a unix shell script that performs recurring exports of data from a csv file to an oracle database. Basically, the csv file contains just the first name and last name will be dumped to an Unix server. The data from these... (3 Replies)
Discussion started by: vinayagan
3 Replies
Login or Register to Ask a Question