Loading Data into Oracle Database from text CSV files

In this post we are going to load data from text files (formatted as CSV “comma separated values”) into oracle database (12.1) using 3 different techniques:

1. Oracle SQL Loader
2. Oracle SQL Developer
3. Oracle sqlcl command line utility.

We will just touch the above techniques; you still need to drill down for more options and more details.

Environment:

This is the data to be loaded:

We saved this excel sheet as CSV, now it looks like:

Note that there is both English & Arabic Data.

Preparation:

I created a database table called emp:

Using SQL Loader:

To use sql loader, you should first create the sqlloader control file, as the following one:

Then

Run the sqlloader as follows:

set NLS_LANG=AMERICAN_AMERICA.AR8MSWIN1256

• This is very important as we have Arabic data.

sqlldr userid=ahmed/ahmed control=employees.ctl log=employees.log

Review the log file:

If you query the uploaded data:

Now let us move to the second option, but let us first truncate our table:

Using SQL Developer:

Select the emp table, then “Import Data”:

Truncate the emp table again and let us move to the third option:

Using SQLCL Command-Line Tool:

Note that although data was loaded successfully, but the Arabic data is corrupted in this case, but this may be a subject for one of my coming posts!.

Thanks

Ahmed