Oracle is the most widely used database on the planet, so chances are that at some point, you had to load data from Excel into Oracle. I know I had to do it many times and to my surprise, the steps involved were not so straightforward.

In this article I will describe a few “traditional” ways to load data from Excel into Oracle, and show you one that’s much more straightforward. As with everything in IT, there are many ways to skin this cat, so let’s look at a few options:

External tables

External tables are special tables that allow SQL queries to access data directly in a flat file.

This method has quite a lot of drawbacks mainly because the data is never fully “integrated” into Oracle (it remains in the flat file).

Oracle Forms

Oracle Forms is an Oracle programing tool that allows you to interact with an Oracle database. You can write some code in Oracle Forms to connect to the Excel file, read the data, and load it into an Oracle table. This method is more powerful than the previous one but requires you to write a piece of code, which isn’t such a flexible way to do it.

SQL Loader

SQL Loader is an Oracle data loading utility that can read a CSV file exported from Excel and load it into an Oracle table. Again it’s a step up from the previous method but still has quite a bit of overhead because SQL Loader requires a control file that’s not to straightforward to write.

Using an External Tool

Another way to load Excel data into Oracle, in my opinion much easier and flexible, is to use an external tool. There are lots of tools on the market that can perform this. I’m going to talk to you about one called Data Moving Tool.

With Data Moving Tool, you can read a CSV file and load it into any table of your choice. You can pick which fields from the CSV file go into which column of the destination tables.

The steps involved are simple and can easily be adapted if the file structure changes.

  1. Save the Excel file into a CSV file (command in Excel: File > Save As).
  2. Open Data Moving Tool and create a database connection to connect to the destination database.
  3. Create a load job and select the target database and the source CSV file
  4. Map each column from the CSV file to a column in the destination table
  5. Run the job to load your data

You can download and try Data Moving Tool for free on http://www.sersoftware.com/