Load MS Excel file to Oracle Database (automated solutions if possible of course)


I’ve seen this over and over. Is it possible to load Excel to Oracle database some easy to automate way? Of course there is, there are actually many ways. My favorite is (7).

1. client side executed solution – VBA macro or COM to directly write to Oracle Database (some manual file manipulation required each time you want to load the file )

This is certainly possible and is not complex to code, but I don’t like it at all for two reasons – first you need to install (if its COM) or at least run (in case of VBA) script on client machine and either hardcode uid/psw or allow the user access directly to Oracle Database. Certainly a lot of places where things can go wrong. One place where I used this was ESSBASE – it has decent excel plug-in and allows to write back to essbase cube  (yeah I know essbase is not oracle database).

2. client side executed solution – web app (some manual file manipulation required each time you want to upload the file)

This is a bit nicer solution simply because there is nothing to install and even authentication can be very seamless. If you use for example Oracle Apex, you can use transparent AD authentication. The app can work the way Oracle Apex imports excel data (copy-paste from excel to a text field in the page) which basically pastes the data as tab delimited structure (and saves probably as CLOB if you use Apex).

3. ask for a file in CSV format (some manual file manipulation required each time you want to load the file)

OK, this is obvious easy to deal with format, I’ve used this method many many times and burned my fingers over and over. You would be surprised how many users have problems with SAVE-AS CSV

4.  ask for XML file (no manual work required if excel is kept in XML format)

This is very interesting solution since XML is somewhat natural excel format. If the user keeps his/her file in XML then there is no special file manipulation required on his/her side. Oracle can easily load XML. If you start digging into this, you might see how complex the XML is and how “not so easy” this can be.

5. server side VBScript (can be very easily automated)

If you are lucky and have Windows server with Excel installed then you can easily use Worksheet.SaveAs Method to convert excel to CSV. One strong advantage I see here is the fact that you use Excel object model to manipulate Excel file and thus it should be easy to move to new Excel format (in case of any future  company wide conversion).

6. JAVA in Oracle JVM (can be very easily automated)

I’m really not JAVA guru, but … there are few easy to use conversion classes. Since  Java can run in Oracle JVM and you can use PL/SQL to execute it, it can be painless very easy to use solution. Please note JVM is isolated from Oracle database and thus most of the out-of-the-box classes out there are going to create CSV file in the file system. I found very nice sample here http://www.dynamicpsp.com/!go?ln=nfaq&faq=FAQ9

7. unix SCRIPT executed either stand alone or using 11.2 external table preprocessor (can be very easily automated)

You can either use JAVA (similar to above) or PERL or …  this is really easy to use solution and if you are lucky and have oracle 11.2 then you can utilize preprocessor and simple SELECT * FROM MY_EXTERNAL_TABLE is going to take care of conversion as well as loading to oracle – this is the way I like !! No scripts to execute, no special manual manipulations, simple SELECT * FROM … is taking care of everything.

here is small how-to for (7)  in 11.2

1. take the sample below and create Book1.xls

2. create four oracle directories pointing to four unix directories (you can of course create one for all files, but let’s try to keep the security tight)

 CREATE DIRECTORY dir_sample_data as '/home/jiri/Desktop/sample/data';
 CREATE DIRECTORY dir_sample_log as  '/home/jiri/Desktop/sample/log';
 CREATE DIRECTORY dir_sample_bad as  '/home/jiri/Desktop/sample/bad';
 CREATE DIRECTORY dir_sample_exec as '/home/jiri/Desktop/sample/execute';

 GRANT READ        on DIRECTORY dir_sample_data to JIRI;
 GRANT READ, WRITE on DIRECTORY dir_sample_log  to JIRI;
 GRANT READ, WRITE on DIRECTORY dir_sample_bad  to JIRI;
 GRANT EXECUTE     on DIRECTORY dir_sample_exec to JIRI;

3. place Book1.xls to /home/jiri/Desktop/sample/data

4. download and install xls2csv perl script – http://search.cpan.org/~ken/xls2csv-1.06/script/xls2csv

5. place script below to /home/jiri/Desktop/sample/execute/run_convert.sh – the script executes perl conversion script and spools the output file to stdout (source for oracle external table)

#!/bin/bash
currentDir="/home/oracle/Desktop/sample/data"
/usr/bin/xls2csv -q -x $currentDir/Book1.xls -w Sheet1 -c $currentDir/Book1.dat
/bin/cat $currentDir/Book1.dat

6. create external table with preprocessor and load the data. Please note the script above is going to be executed every time you run SELECT * FROM jiri.sample_01_ext

CREATE TABLE JIRI.SAMPLE_01_EXT
(
 CUSTOMER_ID     NUMBER(10),
 CUSTOMER_NM     VARCHAR2(60),
 CUSTOMER_SSN    VARCHAR2(12)
)
ORGANIZATION EXTERNAL
( TYPE ORACLE_LOADER
 DEFAULT DIRECTORY dir_sample_data
 ACCESS PARAMETERS
 ( RECORDS DELIMITED BY NEWLINE
 PREPROCESSOR dir_sample_exec:'run_convert.sh'
 SKIP 1
 LOGFILE dir_sample_log:'Book1.log'
 BADFILE dir_sample_bad:'Book1.bad'
 NODISCARDFILE
 FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
 MISSING FIELD VALUES ARE NULL
 )
 LOCATION (dir_sample_data:'Book1.xls')
)
REJECT LIMIT 0
NOPARALLEL
NOMONITORING;

It cannot be easier than that. There is one small issue with the script. It is really not that critical, but as you can see run_convert.sh creates CSV file first and thus if two users run the select at the same time, you can might have a conflict. You can of course create unique file name each time or even better change the perl script a bit (this is actually very very easy change) and run the output to stdout instead of a file.

8. odbc driver

BluShadow pointed this out on OTN Oracle discussion (thank you for the comment). Oracle allows to use OCDB driver to connect to well any source which supports ODBC. Excel ODBC driver is free on Windows and thus this probably would be preferred solution if you run Oracle on Windows machine. It is possible to run it on unix if you have the driver (DataDirect are most common unix drivers – not cheap).

Here is how to create the link (this is copy-paste solution from OTN BlueShadow posted – thank you again for the info), you can find more details on AskTom http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:4406709207206

1- Go to Control Panel>Administrative Tools>Data Sources (ODBC)>System DSN and create a data source with appropriate driver. Name it EXCL.
2- In %ORACLE_HOME%\Network\Admin\Tnsnames.ora fie add entry:

EXCL =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.12.0.24)(PORT = 1521))
)
(CONNECT_DATA =
(SID = EXCL)
)
(HS = OK)
)

Here SID is the name of data source that you have just created.

3- In %ORACLE_HOME%\Network\Admin\Listener.ora file add:

(SID_DESC =
(PROGRAM = hsodbc)
(SID_NAME = <hs_sid>)
(ORACLE_HOME = <oracle home>)
)

under SID_LIST_LISTENER like:

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = d:\ORA9DB)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = ORA9DB)
(ORACLE_HOME = d:\ORA9DB)
(SID_NAME = ORA9DB)
)
(SID_DESC =
(PROGRAM = hsodbc)
(SID_NAME = EXCL)
(ORACLE_HOME = D:\ora9db)
) )

Dont forget to reload the listener

C:\> lsnrctl reload

4- In %ORACLE_HOME%\hs\admin create init<HS_SID>.ora. For our sid EXCL we create file initexcl.ora.

In this file set following two parameters:

HS_FDS_CONNECT_INFO = excl
HS_FDS_TRACE_LEVEL = 0

5- Now connect to Oracle database and create database link with following command:

SQL> CREATE DATABASE LINK excl
2 USING 'excl'
3 / 

Database link created.

Now you can perform query against this database like you would for any remote database.

SQL> SELECT table_name FROM all_tables@excl;

TABLE_NAME
------------------------------
DEPT
EMP
Advertisements
Tagged , , , ,

2 thoughts on “Load MS Excel file to Oracle Database (automated solutions if possible of course)

  1. How it can be defined?

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

%d bloggers like this: