SQL*Loader - Load Excel/CSV file into Oracle Database Table

In the age of various GUI tool available to load an Excel file into Oracle Database, it is always fun to use SQL*Loader command line tool. With this blog I am trying to show how I loaded an Excel file into Oracle Database.

My Current Working Directory is "C:\Users\ngarg\Desktop\load", all files are located in "C:\Users\ngarg\Desktop\load" and also all commands will be executed from "C:\Users\ngarg\Desktop\load".

I had a file AUG.xlsx, which had 14816 rows + 1 header row as following Image -

STEP1: I saved this file as CSV using Excel - Save As - "CSV UTF-8 (Comma Delimited)(*.csv)"

EmployeeID,Worker,ELCode,ReportEffectiveDate
37069,Nimish Garg (37069),USRMTMO,8/31/2018 0:00
4013,Anuj Sharma (4013),ITMILAN,8/31/2018 0:00
4041,Mamta Sharma (4041),USSTM,8/31/2018 0:00
6931,Krishan Tripathi (6931),GBRMT,8/31/2018 0:00
..
..
..

STEP2: Created a new table in my database

SQL> create table aug_csv
  2  (
  3  EmployeeID number,
  4  Worker varchar2(4000),
  5  ELCode varchar2(4000),
  6  ReportEffectiveDate date
  7  );

Table created.

STEP3: Created following control file with name "load.ctl"

options  ( 
  skip=1,
  PARALLEL=true,
  DIRECT=true
)
load data
  infile              'aug.csv'           
  append into table   aug_csv
  fields terminated by ","       
  optionally enclosed by '"' 
  ( 
     EmployeeID,
     Worker,
     ELCode,
     ReportEffectiveDate   DATE "mm/dd/YYYY HH24:MI"
  )

STEP4: Load started with SQL*Loader

C:\Users\ngarg\Desktop\load> sqlldr ngarg@meshdb control='load.ctl' log='Results.log'
Password:

SQL*Loader: Release 18.0.0.0.0 - Production on Thu Sep 12 08:56:06 2019
Version 18.4.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Path used:      Direct

Load completed - logical record count 14816.

Table AUG_CSV:
  14816 Rows successfully loaded.

Check the log file:
  Results.log
for more information about the load.

It is as simple as this, we are done :)

As we can see here all the 14816 Rows successfully loaded into the table. We can validate it as

SQL> select count(*) from aug_csv;

  COUNT(*)
----------
     14816

Here is the explanation of keywords used in Control file of SQL*Loader
1. SKIP=1 - Skip the first line of CSV, as it contains header
2. PARALLEL=true - specifies that loads can operate in multiple parallel to load data
3. DIRECT=true - specifies the load with use data path. A direct path load uses multiblock asynchronous I/O to writing the data blocks directly to the database files, and can usually load data at near disk speed.
4. DATE "mm/dd/YYYY HH24:MI" - need to provide format of dates of the date column of CSV.

SQL*Loader has also created a file "Results.log" in "C:\Users\ngarg\Desktop\load" directory during execution.

SQL*Loader: Release 18.0.0.0.0 - Production on Thu Sep 12 08:56:06 2019
Version 18.4.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Control File:   load.ctl
Data File:      aug.csv
  Bad File:     aug.bad
  Discard File:  none specified
 
 (Allow all discards)

Number to load: ALL
Number to skip: 1
Errors allowed: 50
Continuation:    none specified
Path used:      Direct - with parallel option.

Table AUG_CSV, loaded from every logical record.
Insert option in effect for this table: APPEND

   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
EMPLOYEEID                          FIRST     *   ,  O(") CHARACTER            
WORKER                               NEXT     *   ,  O(") CHARACTER            
ELCODE                               NEXT     *   ,  O(") CHARACTER            
REPORTEFFECTIVEDATE                  NEXT     *   ,  O(") DATE mm/dd/YYYY HH24:MI


Table AUG_CSV:
  14816 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.

  Date cache:
   Max Size:      1000
   Entries :         1
   Hits    :     14815
   Misses  :         0

Bind array size not used in direct path.
Column array  rows :    5000
Stream buffer bytes:  256000
Read   buffer bytes: 1048576

Total logical records skipped:          1
Total logical records read:         14816
Total logical records rejected:         0
Total logical records discarded:        0
Total stream buffers loaded by SQL*Loader main thread:        3
Total stream buffers loaded by SQL*Loader load thread:        0

Run began on Thu Sep 12 08:56:06 2019
Run ended on Thu Sep 12 08:56:52 2019

Elapsed time was:     00:00:46.22
CPU time was:         00:00:01.58

In case of failure at some records, it might create another file with ".bad" extension, listing out the records which got failed.

Related Posts:
- SQL Loader Express Mode - Loading data in Oracle database can't be more easy
- Load CSV file in Oracle using PL/SQL
- Create External Table from CSV File
- Create CSV file using PL/SQL
- SQLFormat - CSV, JSON, XML - Format output of SQL in SQLDeveloper