using sqlloader to insert data in database table

Oracle SQL Loader – Importing CSV files to a table

This post shows how to easily insert data from a CSV file into a database table, using the Oracle SQL Loader tool. We will only concentrate on importing CSV formatted data to the Oracle table with a 1-to-1 column mapping, otherwise I will need to write a book. Users can easily reuse my code, and just customise the columns to their needs.

Source files: import.ctl import.csv
Step 1

Using the command line/prompt, use SQL Plus to login to your Oracle database. Make sure the user has permission to create tables.

$ sqlplus user/password@DBINSTANCE;
Step 2

Under this user, create a table where you want to import the data (skip this step if the table already exists).
view plaincopy to clipboardprint?

    SQL> create table T_IMPORTED_DATA 
    2  ( 
    3    SERIAL       NUMBER(16), 
    4    TITLE        VARCHAR2(64), 
    5    PUBLISH_DATE DATE, 
    6    DESCRIPTION  VARCHAR2(64) 
    7  ) 
    8  / 
    Table created 

Step 3

We use an Oracle utility called SQL Loader. The utility is a command line based, and should work if SQL Plus works (as above). It accepts the 2 source files: the data CSV file, and the CTL control file which specifies how to process our data file. The only thing you need to do is customize the last few rows of the control file to specify how to process each column on the CSV. Each row has the following format:

<column_name> <data_type> <NULLIF column_name = BLANKS>

<column_name> specifies the table column to put the data
<data_type> specifies data type conversion when parsing the CSV data
<NULLIF column_name = BLANKS> is always added to handle empty CSV data, NULL will be inserted

Note that each successive row in CTL file represents each column to be parsed on the CSV, in order.
Step 4

To begin the import, put all the files into one directory, and run the sqlldr command, under the user which owns the table created above.

$ sqlldr userid=user/password@DB_INSTANCE_NAME control=import.ctl log=import.log data=import.csv
Verify

Log files will be created, import.log and import.bad. Verify these to make sure any rows were skipped. Otherwise the data should be added into the database table.