In this article, we will explain different methods to load data from excel sheet into Oracle database.

Below are the methods that can be used.

  • SQL DEVELOPER
  •  SQLCL utility
  •  TOAD
  • SQLLOADER

Here we will explain how to do this with SQL developer and SQLCL utility.

USING SQLDEVELOPER TOOL:

SQL developer is a free tool provided by SQL developer is a free tool provided by Oracle for managing database with GUI interface.

 

 

You can download and install it on your desktop. Loading the excel sheet into a table using SQL developer is the easiest way.

Let’s say, we have an excel sheet names employee.xlsx as below.

 

So first create a table with the columns same as excel sheet.

SQL> show user
USER is "APP_USER"
SQL>
SQL>
SQL> CREATE TABLE EMPLOYEE
( EMPNAME VARCHAR2(100),
  EMPNO   NUMBER ,
  GRADE   VARCHAR2(10),
  SAL     NUMBER
);  2    3    4    5    6

Table created.

Once the table is created, connect to the database using SQL developer.

 

Select the table, which we created,(where data will be loaded).

 

 

Right click on the table and select Import Data.

 

Browse the xlsx file and set format to xls.

Import method -> Insert

 

We have successfully imported the excel sheet into the table. Let’s check in the database.

SQL> select count(*) from app_user.employee;

  COUNT(*)
----------
         4

USING SQLCL  UTILITY:

SQLCL is a free utility provided by Oracle that provides an option to load CSV file to a database table easily.

For installation and use please refer – SQLCL Utility in Oracle:

But here we have an excel sheet which has the extension. .xlsx So 1st step is to convert this to.xlsx csv .

Steps to convert excel sheet to the csv file.

Open a notepad and paste the below connect and name it as XlsToCsv.vbs

Script reference – https://stackoverflow.com/questions/1858195/convert-xls-to-csv-on-command-line

if WScript.Arguments.Count < 2 Then
    WScript.Echo "Error! Please specify the source path and the destination. Usage: XlsToCsv SourcePath.xls Destination.csv"
    Wscript.Quit
End If
Dim oExcel
Set oExcel = CreateObject("Excel.Application")
Dim oBook
Set oBook = oExcel.Workbooks.Open(Wscript.Arguments.Item(0))
oBook.SaveAs WScript.Arguments.Item(1), 6
oBook.Close False
oExcel.Quit
WScript.Echo "Done"

Now in command prompt, go to the location where this executable file exists.

If it shows Done, then CSV file conversion is successful. Then copy this csv file to your database server.

Login with SQLCL utility and use LOAD command. Before that table structure needs to be created in the database.

SQL> CREATE TABLE EMPLOYEE
( EMPNAME VARCHAR2(100),
  EMPNO   NUMBER ,
  GRADE   VARCHAR2(10),
  SAL     NUMBER
);  2    3    4    5    6

Table created.

SYNTAX – LOAD  [ table_name ] [ csv file with path ]

  
SQL> select count(*) from app_user.employee;

  COUNT(*)
----------
         4

We loaded the CSV file successfully.