How do we load files (csv, txt) into Oracle database

How do we load files (csv, txt) into Oracle database
How do we load files (csv, txt) into Oracle database

It is possible to import files like csv and txt into an oracle database table. There are two similar ways. With use SQL*Loader or by use External Table. Their mode of operation is similar, but depending on the case it is more appropriate to use one method.

SQL*Loader

With SQL*Loader we should have created the table before importing the file. Through a control file we define the parameters such as the path of the file and the delimiter it uses.

When is SQL*Loader intended to be used?

Its use is optimal when we want to load data remotely, either in an intermediate table containing indexing and finally when we do not need to modify the data during import.

The example using SQL*Loader

We should create the table.

create table import(id integer, name varchar2(100));
Table created.

We make the control file with the parameters for loading named dokimi.ctl.

load data
 infile '/home/oracle/test.csv'
 append
 into table sys.import
 fields terminated by ";" -- delimiter
 ( id, name )

To carry out the execution, we call sqlldr as a user who has rights, mentioning the path where the control file we made is located.

sqlldr "'sys/Passw0rd1 as SYSDBA'" control=/home/oracle/dokimi.ctl

External Table

By using External Table we should create a logical directory with the location where the file will be located and we should create the table as organization external.

When is the use of the External Table foreseen?

Using External Table is optimal when we want to import the data in parallel and when we want to format the data as we import it.

The example using the External Table

We create a logical directory.

SQL> create  directory imp3 as '/home/oracle';
Directory created.

We execute the create table statement with the organization external option and the file parameters.

CREATE TABLE import2(
  id integer,
  name VARCHAR2(100))
  ORGANIZATION EXTERNAL (TYPE ORACLE_LOADER DEFAULT DIRECTORY imp3
                         ACCESS PARAMETERS (FIELDS TERMINATED BY ';')
                         LOCATION ('test.csv'))parallel 5;

Sources:

Share it

Leave a reply