Easily Load Data from a Text File to Oracle

So, this is our sample data in text format which we want to load into Oracle. We’ll use SQL Loader utility of Oracle which is a super fast way of loading data into Oracle database.

Just remember that Oracle does not automatically create a table for you from a text file. Therefore, we need to create our table manually in the Oracle database into which we will load our data.

CREATE TABLE accounting_table (
ID varchar2(20),
ACCRUED number,
COLLECTED number,
OUTSTANDING_DEBTS number
)

Secondly, we create a control file, which is simply a text file with .ctl extension. The name of the file is accounting_table.ctl

options (skip=1)
load data
CHARACTERSET UTF8  

     infile accounting_table.csv
     replace into table accounting_table
     fields terminated by "," optionally enclosed by '"'
     trailing nullcols

     (     

ID ,
ACCRUED "TO_NUMBER(:ACCRUED,'9999999999999D99','nls_numeric_characters=.,')",
COLLECTED "TO_NUMBER(:COLLECTED,'9999999999999D99','nls_numeric_characters=.,')",
OUTSTANDING_DEBTS "TO_NUMBER(:OUTSTANDING_DEBTS,'9999999999999D99','nls_numeric_characters=.,')"

     )

Thirdly, we create a batch file with the following contents:

sqlldr userid=your_username/your_password@your_database_name control=accounting_table.ctl rows=100000 bindsize=1000000 direct=true

When you double-click the batch file you’ve just created, the loading should begin. That’s it for loading data from a text file to a table in Oracle!

NOTE: Don’t forget to place all files, including the data file, in the same folder. After, completion a log file will be created by the system in the same folder.