Data Loader

 

Here's How To Convert Excel To Oracle Like A Professional

What is Excel?

excelMicrosoft Excel is the most widely used spreadsheet software in the world. An Excel spreadsheet consist of rows and columns. These combination of rows and columns makes it helpful in Data Analysis and Management. A file in Excel is called Workbook and each workbook contains number of spreadsheets. A single Excel spreadsheet can contain up to maximum 1 million rows and maximum 16, 384 columns.

The first version MS Excel was released in 1987 and since then Microsoft constantly upgraded the software and released new versions. Now MS Excel comes as a part of Microsoft Office Suite. An Excel workbook is saved with an .XLXS or .XLS extensions.

Oracle

oracleOracle is the most popular Relational Database Management System (RDBMS). It is widely used by many big corporations around the world. Many of worlds biggest databases are managed with Oracle software. Another advantage of Oracle is that it runs on almost every operating system say UNIX, Solaris, Windows etc.

In this article I am going to show you how to convert an Excel to Oracle. In this article we will learn step by step how to convert a sample Excel sheet to Oracle 12c table.

To convert Excel to Oracle using Data Loader, here is a step by step guide

Step 1

Suppose we have an Excel Sheet like this,

Sample Excel sheet

We want to load this data into an Oracle table. To do that, let's start Data Loader and choose Excel as source and Oracle as Target and click next

Step 2.

You will get the select Excel Sheet screen. In this screen click "Browse" button to navigate to the folder containing the Excel sheet and select the Sheet.
Once you select the sheet, Data Loader will prompt you to install "Microsoft ACE OLE DB 32 Bit Provider", if the 32 provider is not installed in your system, as shown below


Select Excel Source

 
Click "Yes" button to download the required Provider. Please go to your browser downloads section and install the Microsoft ACE OLE DB 32 bit Provider.
After installation is successful, come back to Data Loader and click load button.

install Microsoft ACE OLEDB Provider

Once you click load button then Data Loader will show the sheet names which are there in select Excel Worksheet. Select the desired sheets from the list box and select next.
Here we will select FoodSales$ sheet

Step 3

Now Data Loader will prompt you to connect to the target Oracle Database. Data Loader directly connects to Oracle Database irrespective of Oracle Client software installed or not.

To connect to Oracle Database enter the Host Name or IP address where Oracle Database is running and you have to mention the SID of the Oracle database also.


connect to oracle from data loader

Once you enter all the required information click Connect button and you will get Successfully connected message if it succeeded. Click Next button

Step 4

Now Data Loader will show Source and Destination Tables screen. Here you can click "Columns" button if you want to do column mapping and want to choose loading option etc.

 
To make this simple, in this tutorial we will skip this optional step and just click Next

change columns and other options in data loader

Click Next button

Step 5.

Now Data Loader will prompt you whether to save this session for future execution, and schedule interval time for execution. You can select your desired scheduling options and click Next

Save and Schedule Session in Data Loader

 

Step 6

Now you will be shown the Final screen to Execute the migration. Click the Start button to start loading from Excel to Oracle. Data Loader will show the actions in the log window as shown below

Start converting Excel to Oracle using data loader

Step 7

Now you can login to the target Oracle Database and verify that the table is migrated correctly or not by  clicking Browse DB button or starting SQL PLus

View oracle table


That's it! This is how you can convert Excel sheets to Oracle tables. In these steps you must have noticed that we don't have to enter any commands, you can convert from Excel to Oracle by just few clicks using the Data Loader tool. There is a Trial Version available which is completely functional except there is a limit of 50 rows per table.  You can download it by clicking the following link.

Download Data Loader