Import of an Excel or any other file (External data) into Oracle

For this purpose we can use the following three methods

1. Oracle Application Express

One way which is very easy is to install Oracle Application Express (Formerly HTML DB). Within Application Express we can actually just copy and paste our Excel rows and it will import them to a table. Other then that Application Express is a very useful tool for other things too.

2. SQL Loader SQLLDR

One solution is to use the sqlloader to load any external data into the Oracle database. The problem with this is that we need to run the sqlloader (sqlldr) script every time we need an update.

3. External Table

The more elegant and faster method is to use external tables.

Now, with an external table we can initialize our table once and never have to worry about it anymore. Plus we can use the external table just like any other table in database and issue SQL commands to join the table (may be processing is bit slowly).

Steps required

a. Export your excel sheet to a tab. Delimited format, we call our file “data_2_import.txt”.

b. 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 importdir as ‘C:\data_to_import’;

c. Now we only need to create the external table.

CREATE TABLE EXT_MEMBERS

(

ID VARCHAR2(20 CHAR),

NAME VARCHAR2(100 CHAR)

)

ORGANIZATION EXTERNAL

( TYPE ORACLE_LOADER

DEFAULT DIRECTORY importdir

ACCESS PARAMETERS

(RECORDS DELIMITED BY NEWLINE

FIELDS TERMINATED BY X’9′ (

ID char(20),

Name CHAR(100)

)

)

LOCATION (importdir:’data_2_import.txt’)

)

REJECT LIMIT 0

PARALLEL (DEGREE DEFAULT INSTANCES DEFAULT)

NOMONITORING;

As we can see in the code above, we are creating a table with the filenames “id” and “name” 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′)

In case we are using Comma delimited we use fields terminated by,’, in case we are using fixed field length then we use fields(field 1 position (1:4) char(4), field 2 position (5:30) char(30))

 

Now when we issue a select command on the external table Oracle will read in our data_2_import.txt file. Whenever there is an update of our data_2_import files we only need to replace the file with same formatting on the hard drive and the table is automatically updated within the database.

Note: If external tables are created with NOLOG then granting READ on the DIRECTORY object is sufficient. If an external table is created without the NOLOG syntax then both READ and WRITE must be granted to SELECT from it.

External tables are READ ONLY. Insert, update, and delete can not be performed

About these ads

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