Oracle reading data from spreadsheet file using external tables

Submitted by عمرو نصر on Thu, 10/25/2018 - 12:31

Oracle reading data from spreadsheet file using external tables

This method can be useful to read data from fingerprint device or read data from another data sources.

What is Oracle external table?

External table is database table that can read data from external files that are located in defined location

How to create oracle external table?

It’s an Oracle database object that can be used to view data from an external file with specified delimiters that are defined in the table creation code (SQL DDL Statement)

 

Steps followed by this tutorial are

  1. Prepare the spreadsheet file and convert it to CSV format.
  2. Create a directory object to enable Oracle loader to read and write in the external files.
  3. Create external table and define logs.
  4. Load the data from the file in the external table.

1- Prepare the spreadsheet file and convert it to CSV format.

Suppose that we have a spreadsheet file contains employees attendance with the following structure

id

name

enter_time

leave_time

1

Amr

9:00:23

17:00:23

2

Sameh

9:00:23

18:00:23

We can save this file as csv file that contains the data as values separated by specified delimiters .

In this example I’m using LibreOffice Calc you can use Microsoft Excel or any other spreadsheet application you prefer and you will find options corresponding to those used here in LibreOffice Calc (if you are using OpenOffice you will find same options).

Open file in the LibreOffice Calc and then goto File → Save As …

Liber Office Calc (Spreadsheet) Save AS CSV

From the Save File dialog add write the file name then choose the Text CSV from format as the following image.

Convert Liber Office Calc to CSV

A confirmation message pops up choose use “Text CSV Format” .

An options dialog appears in this dialog select “,” without quotes as Field delimiter and choose “ as a String delimiter and hit OK button.

Now we have the CSV file which will be used by Oracle loader.

 

2- Create a directory object to enable Oracle loader to read and write in the external files.

Now we will create a directory object points to the file system path “D:\READFILE” .

You should connect to Oracle with a user that has CREATE ANY DIRECTORY system privilege to create directories.

Also note that the user who created the directory object is automatically granted the READ and WRITE object privileges on the directory and he or the DBA can grant these privileges to other users and roles.

CREATE OR REPLACE DIRECTORY
external_dir AS ‘D:\READFILE’;

3- Create external table and define logs.

External tables are created using the SQL CREATE TABLE ORGANIZATION EXTERNAL statement.

CREATE TABLE EX_EMPLOYEES

(

                id NUMBER,
                name VARCHAR2(100),
                enter_time VARCHAR2(20),
                leave_time VARCHAR2(20)

)

ORGANIZATION EXTERNAL
  (
      TYPE ORACLE_LOADER
     DEFAULT DIRECTORY external_dir
     ACCESS PARAMETERS
  (

RECORDS DELIMITED BY NEWLINE    
    BADFILE     external_dir:'bad.log'
    LOGFILE     external_dir:'emploees.log'
    SKIP 1
    FIELDS TERMINATED BY ',' 
    OPTIONALLY ENCLOSED BY '"' LRTRIM
    MISSING FIELD VALUES ARE NULL

(
   id,
  name,
  enter_time,
  leave_time
  )

)

     LOCATION (external_dir:employees.csv')

);


 

In the first section of the code we have created a table with the required fields structure as creating any normal database table

CREATE TABLE EX_EMPLOYEES

(
                id NUMBER,
                name VARCHAR2(100),
                enter_time VARCHAR2(20),
                leave_time VARCHAR2(20)
)

The second section we defined the external table by using the ORGANIZATION EXTERNAL clause

ORGANIZATION EXTERNAL
  (
     TYPE ORACLE_LOADER
     DEFAULT DIRECTORY external_dir
     ACCESS PARAMETERS
  (

RECORDS DELIMITED BY NEWLINE    
    BADFILE     external_dir:'bad.log'
    LOGFILE     external_dir:'emploees.log'
    SKIP 1
    FIELDS TERMINATED BY ',' 
    OPTIONALLY ENCLOSED BY '"' LRTRIM
    MISSING FIELD VALUES ARE NULL
(
   id,
  name,
  enter_time,
  leave_time
  )

)
LOCATION (external_dir:’employees.csv')
);

The “ORGANIZATION EXTERNAL” clause needs many parameters to be defined and they are the following

TYPE : 
1- ORACLE_LOADER : To read data from text files 
2-  ORACLE_DATAPUMP :  To read data from dump files.

DEFAULT DIRECTORY : To define the directory.

ACCESS PARAMETERS : Those parameters are describing the structure of the external file which we will load data from.

RECORDS DELIMITED BY NEWLINE : Every line of the file represents a table record (i.e. records are separated by newline) 


LOGFILE     external_dir:'emploees.log': Log errors if found in the employees.log in the external_dir directory.

BADFILE     external_dir:'bad.log' : Log bad data that contains errors in the file called bad.log in the external_dir directory.


 SKIP 1 : Skip first row since the first row in the file as headers of the table.

 
FIELDS TERMINATED BY ',' : Fields are separated by ‘,’.
OPTIONALLY ENCLOSED BY '"' LRTRIM : Fields may be surrounded by “”.
MISSING FIELD VALUES ARE NULL: Consider empty values as NULL values.

You may also interested in Importing data from spreadsheet (Excel, LibreOffice Calc, CSV) file into PostgreSQL database table.

Add new comment

Restricted HTML

  • Allowed HTML tags: <a href hreflang> <em> <strong> <cite> <blockquote cite> <code> <ul type> <ol start type> <li> <dl> <dt> <dd> <h2 id> <h3 id> <h4 id> <h5 id> <h6 id>
  • Lines and paragraphs break automatically.
  • Web page addresses and email addresses turn into links automatically.