SQL-Loader: The Step by Step Basics - Example 1 (CSV file)

SQL*Loader is Oracle's powerful command line (sorry, no GUI here) tool for loading data from almost any organized data file into an Oracle table (or tables). The following are a few examples that could get you started. We'll start with a simple CSV file.

The following examples are run from a Windows PC -- SQL*Plus is also available on Linux or UNIX servers.

First of all, you'll need an Oracle Client installed on your machine. The "sqlplus.exe" executable is part of the Oracle Client software. If you're on a machine that hosts an Oracle database, then the client, by default, is part of that database installation. Normally, a PC has an Oracle Client that is used to talk to an Oracle database that is hosted on a large server that you connect to over a network.

The CSV data file

Next, somewhere on my PC, I've created a folder where I have placed my data file -- a CSV (Comma Separated Values) file that contains data that I would like to load into an Oracle table. Notice that the first line names the columns of data. In the data the names are enclosed in double-quotes, but the numbers are not -- the names must be enclosed in double-quotes because they contain commas. The commas separate the three values on each line. In the first box below there are some extra spaces to make the data a little more readable; they are not necessary.

The file containing the CSV data (file name = "Data.csv")

NAME, BALANCE, START_DT
"Jones, Joe" ,     14 , "Jan-12-2012 09:25:37 AM"
"Loyd, Lizy" , 187.26 , "Aug-03-2004 03:13:00 PM"
"Smith, Sam" ,  298.5 , "Mar-27-1997 11:58:04 AM"
"Doyle, Deb" ,   5.95 , "Nov-30-2010 08:42:21 PM"

Or without the extra spaces around the commas:

NAME,BALANCE,START_DT
"Jones, Joe",14,"Jan-12-2012 09:25:37 AM"
"Loyd, Lizy",187.26,"Aug-03-2004 03:13:00 PM"
"Smith, Sam",298.5,"Mar-27-1997 11:58:04 AM"
"Doyle, Deb",5.95,"Nov-30-2010 08:42:21 PM"

Create the Oracle Table

Next we need to create the Oracle table that will contain the data. Here's the create command to run on your database (assuming that you have a schema named "SCOTT"):

Log into schema SCOTT on your database and execute:

create table scott.sql_loader_demo_simple
  ( customer_full_name   varchar2(50)
  , account_balance_amt  number
  , account_start_date   date
  ) ;

One of the things that will make this example easy is that the columns in the CSV file are in the same order that they appear in the table (the name, the number, then the date). The header names on line 1 of the CSV file do not need to be the same as the column names in the table -- in fact, the header names in the CSV file will be ignored completely; the "SQL*Load" process doesn't use them at all.

The Control file

Now for the critical and most important part -- the control file. Create this text file in the same folder with your CSV data file and name it "Control.txt"

Create the control file. (file name = "Control.txt")

------------------------------------------------------------
-- SQL-Loader Basic Control File
------------------------------------------------------------
options  ( skip=1 )
load data
  infile               'Data.csv'           
  truncate into table   scott.sql_loader_demo_simple
fields terminated by ","       
optionally enclosed by '"' 
  ( customer_full_name
  , account_balance_amt
  , account_start_date   DATE "Mon-DD-YYYY HH:MI:SS am"
  ) 

Here is a quick explanation of the control file contents:

  
 A)  options  ( skip=1 )
 B)  load data
 C)    infile               'Data.csv'           
 D)    truncate into table   scott.sql_loader_demo_simple
 E)  fields terminated by ","       
 F)  optionally enclosed by '"' 
 G)    ( customer_full_name
 H)    , account_balance_amt
 I)    , account_start_date   DATE "Mon-DD-YYYY HH:MI:SS am"
 J)    ) 


   Line A   = Skip the header row of the CSV file.
              If there is no header row it would be: (skip=0).   
            
   Line B   = This is the command to start loading data.

   Line C   = This is the name of your CSV data file.

   Line D   = This is the schema and name of your Oracle table.  

              The "truncate" specifies that the existing data in the

              table will be truncated or erased prior to the load.

   Line E   = This is the symbol used to separate values in your CSV file. 

   Line F   = This allows CSV values to be enclosed in double-quotes.

   Line G-J = This is the list of columns to be loaded.
              The order of this list comes from the CSV file
              and the column names come from the table. 
           
   Line I   = Because this is a date column, the format of the
              dates in the CSV data needs to be specified as shown.

The Batch file

Now let's create one more simple file in the folder that will make it easy to run the load process -- This is a text file that has the extension "bat". This batch file, when double-clicked, will run basic command-line commands on your PC. This is how we access the "sqlldr.exe" executable that runs the SQL*Loader process.

Create the Batch file. (file name = "StartLoad.bat")

@echo off

sqlldr 'scott/tiger@my_database' control='Control.txt' log='Results.log'

pause

In the "bat" file the "sqlldr" finds the executable and starts the process. The logon credentials are given next as the username being SCOTT and the password being TIGER. The "@" symbol is followed by the name of your database. Next the control file is specified -- the name here must match the name of your actual file. Last, the name of a log file is given where the results of the load process will be written. Actually, this line could be typed directly into a command window to start the load process. The "pause" command at the end keeps the window open till you press a key -- it's nice to see if any errors show up.

If you wish, you can leave out the password and it will prompt you to enter it when you run the load:

@echo off
sqlldr 'scott@my_database' control='Control.txt' log='Results.log'
pause

Executing the Load

There should now be 3 files in your folder:

  StartLoad.bat

  Control.txt

  Data.csv

Double click on the batch file -- enter your password if you left it out -- and the SQL load will proceed. With this small file it should be almost instantaneous. Your command window should look something like this:

Not much to see, but the load is complete.

If we open up the file "Results.log" we can see the details of SQL*Load process. Looking down towards the bottom of the log we can see the following:

...
Table SCOTT.SQL_LOADER_DEMO_SIMPLE:
  4 Rows successfully loaded.
  0 Rows not loaded due to data errors.
  0 Rows not loaded because all WHEN clauses were failed.
  0 Rows not loaded because all fields were null.
...
Total logical records skipped:          1
Total logical records read:             4
Total logical records rejected:         0
Total logical records discarded:        0
...

This tells us that the 4 rows of data successfully loaded. If there were rows of data that did not load, then their counts would show up where there are zeros in the log file example above.

The Data Has Been Loaded!

Now we can run a simple query to see the data in the table:

That's SQL*Loader in a nut-shell ...
or at least in a nut-case's blog page.

Enjoy your data!   :)