You can read and import data from the Excel's xlsx files in Oracle, using PL SQL package as_read_xlsx written by Anton Scheffer, with the help of this package you can directly query the Xlsx file in Oracle. It is very simple to use and gives the output which can be easily manipulate. In this post steps are given below to import excel file into Oracle table using PL/SQL, first you need to install package as_read_xlsx, which you can download the from the following link:
Below are the steps to use this package and to import xlsx file in Oracle:
1. After downloading the package, execute the script in database to create the package.
2. Then create a directory object in Oracle database in which your excel (xlsx) files are exists:
Create or Replace Directory DOC as 'D:\YourDir'
Or for Unix / Linux
Create or Replace Directory DOC as '/YourDir/'
2. After that just execute the query as shown below to view the contents of the xlsx file:
FROM TABLE(as_read_xlsx.read (
as_read_xlsx.file2blob ('DOC', 'xlsx_test.xlsx')))
The result will come like shown below from the excel file shown in above image:
You can use above query as cursor in PL/SQL procedure then can import the excel file into Oracle table.