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

• Author
• Recent Posts

Stratos Matzouranis

The journey began in 2008 where I officially started working in the field of IT (IT). Starting the first semester of school I realized a special attraction towards databases and automation. I have been involved with databases such as Microsoft SQL Server / Oracle Database, data analysis and automations using the command line (CLI), Visual Basic for Applications and Python. Through years of experience I have developed these capabilities so that I can make my life easier. For me, the purpose of every IT guy and every office worker is to have the knowledge so that through tools he can work a little but produce a lot. Through this his website DataPlatform.gr I try to offer knowledge and propose solutions to everyday problems.

Certifications:

Latest posts by Stratos Matzouranis (see all)

• How can we connect Oracle Database to SQL Server using Oracle Gateway on Linux - 1 July 2024
• How do we automate the process of checking the integrity of databases in SQL Server without using a maintenance plan - 10 May 2024
• How can we connect Oracle Database to SQL Server using Oracle Gateway on Windows - 5 April 2024

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:

• Choosing External Tables Versus SQL*Loader