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