Loading multiple files .CSV or .txt using SQL Loader (Linux)

Few days back I was working on loading multiple files into my oracle data base. My oracle database was installed on Linux environment, and the files that I need to load was like for one table it was .csv and other file was in .txt files .

The files are huge like having more than 3 millions of data and also I need to pick the date from each file name and update on the same table which has a date column.

It is easy to load the flat files using SQL Loader or using external tables which are more faster way to load. But picking the date ??? from the file name then I tried but was really not able to get it done from Linux bash scripting then by the help of few experts like:

I was able to achieve it.

My task was something like below:

I have multiple text files which need to be loaded into my oracle table.
Below are the process that need to be followed:
1) using a shell script first take the flat files one by one and load into the staging table.
2) one loaded into the staging table then get the date from the file name (test-20121017.txt) and then update the staging table with date format ‘dd-mon-yyyy’.
If the file name=test-20121017.txt
then the date will be = 17-OCT-2012, need to load with the same like ‘20121017’
3) Now call a procedure which contains few business logic to append to the main table.
4) Now change the file name from  test-20121017.txt to test-20121017_loaded.txt
5) then take the next file and follow the same steps .. but it should not take any file that is renamed or contains “_loaded”.

Try the below process to achieve the above:

Here is my table and sample files :

# test-20121018.txt
DATE IN FILE :
COL1,COL2
1,"TEST1"
2,"TEST1"
3,TEST1

# test-20121017.txt
DATE IN FILE :
COL1,COL2
1,"TEST2"
2,"TEST2"
3,TEST12

# test-20121016.txt

DATE IN FILE :
COL1,COL2
1,"TEST3"
2,"TEST3"
3,TEST13

Table structure:

SQL> CREATE TABLE TEST_STG
(
COL1 NUMBER,
COL2 VARCHAR2,
COL3 DATE
);

OPTIONS(skip=1)
LOAD DATA
TRUNCATE
INTO TABLE TEST_STG
fields terminated by “,”
optionally enclosed by ‘”‘
TRAILING NULLCOLS
(
COL1,
COL2
)

My control CONTROL file:

OPTIONS(skip=1)
LOAD DATA
TRUNCATE
INTO TABLE TEST_STG
fields terminated by ","
optionally enclosed by '"'
TRAILING NULLCOLS
(
	COL1,
	COL2
)

To get this done below is the updated script which I have written to accomplish the task:


#!/bin/sh
# -------------------------------------------------------------------------
# Author: Swadhin Ray {Sloba} (slobaexpert@gmail.com)
# Date: 30 Oct 2012
# -------------------------------------------------------------------------

###########################################################################################
# Export Oracle paths and take a variable and pass the username and password
# of Oracle database
###########################################################################################

export ORACLE_BASE=/opt/app/oracle
export ORACLE_SID=*****.db.*****.com
export ORACLE_HOME=$ORACLE_BASE/product/11.1.0
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export ORACLE_PATH=$ORACLE_HOME/bin
export PATH=$ORACLE_HOME/bin:$PATH
ORACLE_ACCESS=sloba@$ORACLE_SID/*********

###########################################################################################
# Get all the files which are not like '%_loaded'
# For example : if the file name is test223221_loaded.txt then this will not be taken
# as this file is already loaded.
###########################################################################################
FILES=`ls /opt/app/test1-*.txt|grep -v "_loaded.txt"`;

###########################################################################################
# Get all the files which are not like '%_loaded' and start the Oracle SQL Loader to
# to load the data
#
###########################################################################################

for file in ${FILES[@]}
do
 filename=`expr substr "$file" 47 31`
 echo $filename
###########################################################################################
# Get the date from the file name , so that we can update it on the table
###########################################################################################
 filedate=`expr substr "$file" 66 8`
 echo $filedate
###########################################################################################
# SQL LOADER command to load the file from
###########################################################################################

 sqlldr $ORACLE_ACCESS control=/opt/app/test_c.ctl log=/opt/app/test_loaded.log data=$filename skip=1

export ORACLE_BASE=/opt/app/oracle
export ORACLE_SID=*****.db.*****.com
export ORACLE_HOME=$ORACLE_BASE/product/11.1.0
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export ORACLE_PATH=$ORACLE_HOME/bin
export PATH=$ORACLE_HOME/bin:$PATH
ORACLE_ACCESS=sloba@$ORACLE_SID/*********
LOG_FILE_PATH=/opt/app
LOG_FILE=/update.log
###########################################################################################
# update the date column for test table
###########################################################################################
# Run SQL
SQL=`sqlplus -S <<RUN_SQL 2>>${LOG_FILE_PATH}${LOG_FILE}
$ORACLE_ACCESS
set serveroutput on size 999999 linesize 200 feedback off
whenever sqlerror exit 1
UPDATE test
SET col3 =$filedate
/
###########################################################################################
# Prcedure to insert from stage table to main with
# some validation which I have used it but you can ignore this too
###########################################################################################

BEGIN
 INSERT_MAIN();
END;
/
exit 0
RUN_SQL`
echo $SQL
if [[ $? = 1 ]]; then
 print `date "+%Y-%m-%d-%H.%M.%S"` "**Error running update sql. review above messages." >> ${LOG_FILE_PATH}${LOG_FILE}
fi

########################################################################################################
# Now rename the file with sufix "_loaded.txt"
# So that when we schedule this programe then only the files will be taken which are not loaded earlier
#########################################################################################################
 NAME=`expr substr "$file" 47 27`
 EXT=${filename#*.}
 LOAD="loaded"
 NEWFILE=${NAME}_${LOAD}.${EXT}
mv $file $NEWFILE
echo $NEWFILE

done

If you want to send an email notification then you can also add the below piece of code to send email to the required persons for intimating the file are been loaded:


########################################################################################################
# Email notification
#########################################################################################################
SUBJECT="TEST file loaded"
FROM="Your Team"
DESCRIPTION="THIS IS AN AUTO-GENERATED MESSAGE"
EMAIL="you@xyz.com,other1@xyz.com,other2@xyz.com,.....othern@xyz.com"
EMAILMESSAGE=/tmp/emailmessage

echo "From: $FROM" > $EMAILMESSAGE
 echo "To: $EMAIL" >> $EMAILMESSAGE
 echo "Reply-To: you@xyz.com" >> $EMAILMESSAGE
 echo "Subject: $SUBJECT " >> $EMAILMESSAGE
 echo "************************************************************************" >> $EMAILMESSAGE
 echo "$DESCRIPTION : DO NOT REPLY TO THIS EMAIL MESSAGE. " >> $EMAILMESSAGE
 echo "************************************************************************" >> $EMAILMESSAGE
 echo " " >> $EMAILMESSAGE
 echo " " >> $EMAILMESSAGE
 echo "Test file is loaded sucessfully " >> $EMAILMESSAGE
 echo " " >> $EMAILMESSAGE
 echo "Some message you want to track " >> $EMAILMESSAGE
 echo " " >> $EMAILMESSAGE
 echo " " >> $EMAILMESSAGE
 echo "Thanks, " >> $EMAILMESSAGE
 echo "Swadhin Ray(Sloba)" >> $EMAILMESSAGE
 cat $EMAILMESSAGE | /usr/sbin/sendmail -t

You can modify the codes and use it for your work and for learning purpose too.

Thanks,
Swadhin Ray (Sloba)

 

Advertisements

One thought on “Loading multiple files .CSV or .txt using SQL Loader (Linux)

  1. HI Swadhin.
    great post.I have a similar requirement and i am pretty new at using unix and stuff.So can yo help me out in my task on the doubts i have

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s