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

Data loaded successfully.

Review the log file:


If you query the uploaded data:

It is fine.

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”:

Select the csv file, format as csv and the encoding as “cp1256”, click Next
Select the Import Method as “Insert” and click Next.
Select the table columns corresponding to the data in the csv file, click Next.
Review the column mapping and click Next.
Review the Summary screen and click “Finish”.
Data imported successfully.

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

Using SQLCL Command-Line Tool:

Use the “load” command to load data from the csv file.

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!.

Donate to support our blogging work

$1.00

Thanks

Ahmed

2 thoughts on “Loading Data into Oracle Database from text CSV files

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s