How to import data from excel's xlsx file into oracle database

Think, if we can consider a row of an excel file as a record or row of table and a column as a table column. Is it possible at all? Yeap, Now it's possible. Anton Scheffer's as_read_xlsx package helps me to make it possible.

You will be able to direct query from excel's xlsx file in oracle database by using as_read_xlsx package. And then, manipulate your data as you want. Download this package from the following link.

Download

After downloading, there are two ways to use this xlsx file.

1. Read xlsx from a directory.
2. Read xlsx from a blob column of database.

Follow these steps to read xlsx from a directory:
1. Execute the script to create package in oracle database.
2. To contain the xlsx file, create a directory object. Here are scripts to help you.

CREATE OR REPLACE DIRECTORY XLSX_DIR AS 'c:\oracle\xlsx_files';
GRANT READ, WRITE ON DIRECTORY XLSX_DIR TO HR;

3. Place xlsx file to this directory.
4. You can write a query like this,

SELECT * 
FROM TABLE(as_read_xlsx.read(as_read_xlsx.file2blob('XLSX_DIR', 'file.xlsx')));

The result would be viewed as like as a query from a table. Now you can use this query in PL/SQL procedure as a cursor and insert data into database table.

Follow these steps to read xlsx from a blob column of database:
1. Execute the script to create package in oracle database.
2. Upload xlsx file into a blob column in table.
3. You can write query like this,

      WITH xlsx
           AS (SELECT ROW_NR,
                      COL_NR,
                      CASE CELL_TYPE
                         WHEN 'S' THEN STRING_VAL
                         WHEN 'N' THEN TO_CHAR (NUMBER_VAL)
                         WHEN 'D' THEN TO_CHAR (DATE_VAL, 'MM/DD/YYYY')
                         ELSE FORMULA
                      END
                         CELL_VAL
                 FROM (SELECT *
                         FROM TABLE (as_read_xlsx.read (
                                        (SELECT blob_column
                                           FROM xlsx_files
                                          WHERE xlsx_id = 1),
                                        'Sheet1'))))
      SELECT xl.ID,
             xl.FIRST_NAME,
             xl.LAST_NAME,
             xl.FATHER_NAME
        FROM xlsx PIVOT (MAX (CELL_VAL)
                  FOR COL_NR
                  IN  (1 AS ID,
                      2 AS FIRST_NAME,
                      3 AS LAST_NAME,
                      4 AS FATHER_NAME)) xl
       WHERE row_nr > 1;

NB: Here, "xlsx_files" is database table, "blob_column" is blob column of table, "xlsx_id" is unique key of table, "Sheet1" is the sheet name of excel's xlsx file and  "row_nr > 1" is used to cut the row from header of excel's xlsx file.

Again, the result would be viewed as like as a query from a table. Now you can use this query in PL/SQL procedure as a cursor and insert data into database table.

Remember,

• If you have lot of data at your .xlsx file then it will take some time. But if you can upload and import file from .csv then it would more faster and easier.

Think it helps you. Good luck.

You may also like:

• How to import data from excel's csv file into oracle database