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.