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;

Fatal error: Uncaught PDOException: SQLSTATE[HY000]: General error: 8 attempt to write a readonly database in /home/httpd/vhosts/renenyffenegger.ch/php/web-request-database.php:51 Stack trace: #0 /home/httpd/vhosts/renenyffenegger.ch/php/web-request-database.php(51): PDOStatement->execute(Array) #1 /home/httpd/vhosts/renenyffenegger.ch/php/web-request-database.php(66): id_of(Object(PDO), 'uri', '/notes/developm...') #2 /home/httpd/vhosts/renenyffenegger.ch/php/web-request-database.php(30): insert_webrequest_('/notes/developm...', 1759612265, '216.73.216.149', 'Mozilla/5.0 App...', NULL) #3 /home/httpd/vhosts/renenyffenegger.ch/httpsdocs/notes/development/databases/Oracle/objects/tables/external/Excel(86): insert_webrequest() #4 {main} thrown in /home/httpd/vhosts/renenyffenegger.ch/php/web-request-database.php on line 51

Oracle: Reading Excel sheets with external tables

See also