Search notes:

Oracle: Reading Excel sheets with external tables

This example tries to demonstrate how Excel sheets can be read with external tables

First, in the filesystem where Oracle is running, we create a directory into which the Excel workbooks will be stored from which an Excel sheet will be loaded.

# su - oracle
$ mkdir /opt/oracle/ext

Then, we make sure that only oracle can see its contents.

$ chmod 700 /opt/oracle/ext

We also need a directory where the Python script xlsx2csv.py will be stored:

$ mkdir  /opt/oracle/xlsx2csv
$ git clone https://github.com/dilshod/xlsx2csv /opt/oracle/xlsx2csv

Fix the script's shebang because Oracle sets the PATH to Oracle related directories like $ORACLE_HOME/bin hen calling the script (so-called clean environment):

$ sed -i '1s|.*|#!/usr/bin/python3|' /opt/oracle/xlsx2csv/xlsx2csv.py

We also need a directory object for the directory where the script is located:

create directory        XLSX2CSV as '/opt/oracle/xlsx2csv';
grant read on directory XLSX2CSV to rene;

We're now able to create the external table

create table ext_table_xlsx (
   i   number,
   n   varchar2(20),
   m   varchar2(20)
)
organization external (
   type              oracle_loader
   default directory ext_dir
   access parameters (
      records delimited  by newline
      preprocessor xlsx2csv:'xlsx2csv.py'
      nologfile
      nobadfile      
      fields  terminated by ','
      missing field values are null
   )
   location ('a.xlsx')
)
-- reject limit 0 /* 0 is the default anyway */
;

Finally:

select * from ext_table_xlsx;

Oracle: Reading Excel sheets with external tables

See also