Shell Programming and Scripting

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

SQL*Loader

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

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

Code:

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

Output:

Code:

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

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

Code:

TEST
TEST
TEST

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

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.

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"

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.

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

Code:

$ 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');

$