Microsoft
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
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
Suppose we have an Excel Sheet like this,
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
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
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.
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
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.
Once you enter all the required information click Connect button and you
will get Successfully connected message if it succeeded. Click Next button
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
Click Next button
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
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
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
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.