Saturday, July 13, 2013

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!   :)

17 comments:

  1. Hey Steve,

    Great example. I haven't used SQL Loader for a few years and my current project requires it so I am relearning it. Here are two questions for you.

    1. Is it possible to load multiple data files (each for a separate table, with table structures that are not identical) using a single control file?
    2. As an alternative to #1, is it possible to invoke SQL Loader multiple times (for multiple control files) in a single batch file?

    I am going to try out #2 so I may post a reply to my own question in a little while.

    Thanks

    ReplyDelete
    Replies
    1. 1) I think so. I haven't experimented with most of the advanced features.
      2) Yes, a batch file is just a serial list of commands.
      This is just intended as an introduction, See the Oracle documentation -- SQL-Loader is very powerful and can do quite a few interesting things.

      Delete
  2. Very well explained. I was tired of searching for an example on the net .Finally I found it ....Thanks ..

    ReplyDelete
  3. Very helpful. Yours is about the 5th example I've tried. Finally got there. Nice and simple.

    Much appreciated.

    ReplyDelete
  4. Very useful and self explainatory. Thanks for this post.

    ReplyDelete
  5. simple and straight forward. Thanks

    ReplyDelete
  6. Excellent document! Thanks for posting this .

    ReplyDelete
  7. Very well explained..!!

    ReplyDelete
  8. Thanks! Is there a way to insert a specific data into one of the fields? For example, I want to insert the number "5" into accountt_balance_amt.

    ReplyDelete
    Replies
    1. Yes, in one of my other posts on sqlldr, ( http://steve-lyon.blogspot.com/2011/08/using-sql-loader-to-load-csv-data-into.html ), towards the end, it show how to use constants. You can insert numeric, text, or date constants into a column as SQL-Loader runs.

      Delete
  9. This comment has been removed by the author.

    ReplyDelete
  10. Is there any way to load the data using a sequence?
    I need to load about 440.000 row, but I need to use a sequence for one of the columns.

    ReplyDelete
    Replies
    1. See my other post, it includes some of the other cool features you can use in the control file -- including generating a sequence number for records being inserted:

      "Using SQL-Loader to Load CSV Data Into a Table."
      "http://steve-lyon.blogspot.com/2011/08/using-sql-loader-to-load-csv-data-into.html"

      Delete
  11. oraodm12.dll missing

    ReplyDelete
    Replies
    1. I'm supposing you mean something like "When I try to use SQL-loader I get an error that says that oraodm12.dll is missing." Try Googling something like "sqlldr - error File missing oraodm12.dll" it seems to be a new Oracle bug that you can fix by renaming a file in the Oracle installation. Or you can get the official Oracle patch.

      Delete