I just got a question again from a customer asking how to import an Excel file into a Oracle database. This has lead me to post this entry.
One way which is very easy is to install Oracle Application Express (Formerly HTML DB). Within Application Express you can actually just copy and paste your Excel rows and it will import them to a table. Other then that Applicaton Express is a very useful tool for other things too. But if you only want to load some data then you can use the solutions I provide below.
One solution is to use the sqlloader to load any external data into the Oracle database. The problem with this, in my opinion, is that you need to run the sqlloader script everytime you do an update.
The more elegant and faster method is to use external tables. Now, with an external table you can initialize your table once and never have to worry about it anymore. Plus you can use the external table just like any other table in your database and issue sql commands to join the table. This is very powerful. Actually like this you can load hundreds and thousands of records into your database (we do it for “importing” thousands of images into our digital asset management solution).
Ok, so how to do it? Let’s dive into the code.
- Export your excel sheet to a tab.delimited format, we call our file “members.txt”.
- If you don’t have a directory alias set up within Oracle then create one now. The directory allows Oracle to read files from this directory on your hard drive. You create a directory with the following commands:
create or replace directory
thenameofthedirectory as
‘C:\yourpath’; - Now we only need to create the external table. Replace the filename and the directory name to suite your names:
CREATE TABLE EXT_MEMBERS
(
ID VARCHAR2(20 CHAR),
NAME VARCHAR2(100 CHAR),
SURNAME VARCHAR2(100 CHAR)
)
ORGANIZATION EXTERNAL
( TYPE ORACLE_LOADER
DEFAULT DIRECTORY YOURDIRECTORY
ACCESS PARAMETERS
( RECORDS DELIMITED BY NEWLINE
FIELDS TERMINATED BY X’9′ (
ID char(20),
Name CHAR(100),
surname CHAR(100)
)
)
LOCATION (YOURDIRECTORY:’members.txt’)
)
REJECT LIMIT 0
PARALLEL ( DEGREE DEFAULT INSTANCES DEFAULT )
NOMONITORING;
As you can see in the code above, we are creating a table with the fielnames “id”, “name” and “surname” which represents the order from our Excel file. We also tell the external table that every record is on a newline and that the fields are separated with tabs (X’9′).
Now when we issue a select command on the external table Oracle will read in our members.txt file. Whenever there is an update of our members file we only need to replace the file on the hard drive and the table is automatically updated within the database.
You can read more on the use of external tables at the Oracle documentation found in this link.
