Sometimes we may need to create an application, where we create a database table and large amount of data need to be loaded on it. This can be achieved with the help of SQL Loader, where we load bulk data directly from a file into our database table. The source file can be of format .xls, .txt or .csv, etc.

In this article, we will see how to load bulk data into a table from a .txt file, from windows command prompt.

Following are the steps to load data using SQL loader.

  • Create a table and keep the source file ready with required data.
  • Create a control file and specify required database table details and source file details.
  • Run the SQL loader from command prompt.

Create a table and keep the source file ready with required data :

This step can be skipped in case you already have a database table created.

Create a table called EMP_DTLS, with columns holding details about employee ID and employee’s first and last name details as shown below. This is the table where we are going to load our data from text file.

CREATE TABLE EMP_DTLS(
    EMP_ID INT,
    EMP_FIRST_NAME VARCHAR(15),
    EMP_LAST_NAME VARCHAR(15)
);

Create a text file called Data.txt with employee data. This file contains the data, which needs to be stored on our newly created table EMP_DTLS.  Our file contains column header in first row and column details separated with “,” as shown below.

EmpID,FirstName,LastName
1,ASB,Notebook
2,Bat,Man
3,Spider,Man

Create a control file and specify required database table details and source file details :

Now the next step is to create a control file. This file specifies different control  statements, like where our source file is located, should we need to truncate the table before inserting new records, which character is used to separate the column data, etc.

In our example control file shown below, we have specified that our:

  • First row needs to be skipped as it contains column header names with options keyword.
  • Data will be loaded from file located in C:\Users\Desktop\Data.txt
  • Truncate the table TEST.EMP_DTLS before inserting the records using truncate keyword.
  • Column fields are terminated with character “,”. (We can use “,”, “|”, etc).
  • Column data might be enclosed with an optional ‘ ” ‘ character.
  • Which order the given data should be inserted to table column fields EMP_ID, EMP_FIRST_NAME and EMP_LAST_NAME.
options ( skip=1 )
load data infile 'C:\Users\Desktop\Data.txt'           
truncate into table TEST.EMP_DTLS
fields terminated by ","       
optionally enclosed by '"' ( 
  EMP_ID,
  EMP_FIRST_NAME,
  EMP_LAST_NAME
)

Run the SQL loader from command prompt :

Finally run the sqlldr command along with required details as shown below. Here, we are specifying the database ‘username/password@Database’ to connect to the database.

control keyword is used to specify control file path.

An optional log keyword is used to specify log file path, where log should be created. This will be helpful in case of any error occurs during data loading process.

sqlldr 'db_username/db_password@Database' 
control='C:\Users\Desktop\loader.ctl' 
log='C:\Users\Desktop\Results.log'

We can able to see that our data is loaded into table after executing the command.

sqlldr

Finally, our table EMP_DTLS is loaded with data from source .txt file.

sql_loader2

In this article, we learned how to use SQL loader to load bulk data into our database table.

Versions Used:

  • OS: Windows 7.
  • Database: Oracle 11g.
  • SQL Loader : Release 9.2.0.1.0

Also read: