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

Shell Programming and Scripting
1

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.

2 
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

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

When i print the columns, some have commas and in some there are 2 columns and one column has a comma.

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:

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

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

Hi,

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

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
8

As @Peasant mentioned sqlloader is THE tool for this job:

SQL*Loader

9

@pravin : You might face an issue while dealing with date fields to manipulate the same if you use table type insertion..

10

Table columns & file columns are not in the same order.

---------- Post updated at 10:44 PM ---------- Previous update was at 11:55 AM ----------

How do we tell in awk to ignore spaces in the fields.

If there are spaces in the fields , it is not giving correct output:
-------------------------

for i in `cat  file.txt`
do
col1=`echo  $i | awk -F ',' '{print $1}'`
done

Output:

TEST
 
TELEPROCESSING
(ITS)
 
IMPLEMENTATION
 
 
TEST
 
-
ALL
OTHERS
 
 
 
TEST
 
-
ALL
OTHERS

--------------------------------------------------
Used underscore for fields where there is space, i get correct output:

TEST
TEST
TEST

-------------------------------------------------

11

Hi Folks,

Im going through this forum down here looking for some clues as how to insert a CSV file into a table in Oracle. Im in the same situation as the one who posted their question in here.

My input file is like this

395 , LIES CANT BE FOUND IN TRUTH, 20110517 , PRD1
231 , HOW GREEN WAS MY VALLEY, 20110517 , ADJCC

and I need to insert the above lines into a table that has 4 columns in the exact same order as above.

But I cant seem to do it no matter how I did. Here is what I have ... ofcourse a copy of what you have in here anyway....

#!/bin/sh

for i in `cat FILE`
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}'`

sqlplus -s user/pwd@oracledb <<OSQL
INSERT INTO RDD_Book_Unit (COUNT, MESSAGE, PRINTDATE, IBNCODE)
values ($col1, $col2, $col3, $col4)
commit;
exit;
OSQL

done
----

Please advise as I feel like Im cursed now !

thanks,
PGonzalez.

12

Just as already suggested by Franklin & Peasant, the right way of loading data into your DB is to use the corresponding tools that have been designed for that (In your case - i suppose - it is SQL Loader : sqlldr).

Since you refer to sqlplus, i suppose you have something like an Oracle DB behind, so i suggest you to have a look at Ask Tom "SQL and CSV fields"

13

Hey Thanks for the quick response. Im as new as they come to Oracle itself. I read couple of posts here pointing me in this direction as it looks pretty simple & easy to follow & ofcourse it doesnt look like so.

At any rate, if you can help, that would great, otherwise, I will sit & learn sqlloader and see how that can be of help for me in this instance.

thanks,
PGonzalez.

14

SQL Loader has better performance for large loads, but you can also just use a quick awk script to process the CSV file, e.g...

$ cat file1.csv
395 , LIES CANT BE FOUND IN TRUTH, 20110517 , PRD1
231 , HOW GREEN WAS MY VALLEY, 20110517 , ADJCC

$ cat eg.awk
BEGIN {
  FS = " *, *"
  ins = "INSERT INTO RDD_Book_Unit (COUNT, MESSAGE, PRINTDATE, IBNCODE) VALUES"
}
{
  printf "%s (%s, '%s', '%s', '%s');\n", ins, $1, $2, $3, $4
}

$ awk -f eg.awk file1.csv > eg.sql

$ cat eg.sql
INSERT INTO RDD_Book_Unit (COUNT, MESSAGE, PRINTDATE, IBNCODE) VALUES (395, 'LIES CANT BE FOUND IN TRUTH', '20110517', 'PRD1');
INSERT INTO RDD_Book_Unit (COUNT, MESSAGE, PRINTDATE, IBNCODE) VALUES (231, 'HOW GREEN WAS MY VALLEY', '20110517', 'ADJCC');

$
1 Like
15

Ctsgnb & Ygor,

Yes, the size of the file that we are talking is pretty small only 60 lines or so and hence I thought this could be the *easy* way out.

Ygor, I shall post any further questions or thoughts once I go to work & run this.

Thanks a bunch Ygor & I assure you that all of your efforts are sincerely appreciated.

regards,
PGonzalez.

16

Ctsgnb & Ygor,

This script was working fine for me till day before yesterday. I had to make some changes by adding few columns which have null values or spaces. So, when I run the same script, it is giving me the following error -

awk: illegal primary in regular expression *, * at , *
input record number 1, file file1
source line number 3

I did happen to look at the correct number of arguments passed to awk and they are made to match to the number of columns. However Im seeing this error. I have spent atleast 4 hours on this and I did not make any dent into the error. I even did which awk and it should /usr/bin/awk.

By the way, I using bash.

Please help me & stop this hair loss.

regards,
PG.