Process and read xlsx from PL/SQL

To read a XLSX-file from PL/SQL can be a bit challenging. One option is to rename the file to .ZIP and then process it from from there. An XLSX-file is actually ZIP archives internally. Microsoft Office formats after 2007 (the “Open XML” formats) are structured as a collection of XML files and resources (like images, styles, and document parts) bundled inside a ZIP archive.

When you rename file.xlsx → file.zip, you can then:

• Open it with a ZIP program (like WinRAR, 7-Zip, or even Windows Explorer).

• Browse its contents — you’ll see folders like xl/, docProps/, and _rels/, and files like [Content_Types].xml.

• Extract parts — e.g., images, embedded objects, raw XML data.

But, if you are going to process a single tab document it easier to save the document as CSV and then process it as an external table.

How do you then save the XLSX-file as CSV with PL/SQL and read it? Below is instructions on how to do it on Linux.

1. Create a java stored procedure that can execute OS-commands
   go to https://oracle-base.com/articles/8i/shell-commands-from-plsql and follow the instructions

2. Install LibreOffice on your server.

3. Create a shell-script that save the XLSX-file as CSV

   /usr/bin/libreoffice --headless -convert-to csv:"Text - txt - csv (StarCalc)":124,34,0,1,1 $1 --outdir $2

   $1 = full path to the file you should save, including the file name
   $2 = path to folder where the CSV shoul be created  

4. Execute the shell script from PL/SQL
   host_command('/home/oracle/XLSX2CSV.sh /documents/prospekt.xlsx /documents');

5. Create the external table

   CREATE TABLE your_exttbl (
     col_1 VARCHAR2(100),
     col_2 VARCHAR2(100),
     col_3 VARCHAR2(100),
     col_4 VARCHAR2(100),
     etc...
   )
   ORGANIZATION EXTERNAL
   (
     TYPE ORACLE_LOADER
     DEFAULT DIRECTORY your_db_directory_where the_csv_is_storded
     ACCESS PARAMETERS
     (
       RECORDS DELIMITED BY NEWLINE
               FIELDS TERMINATED BY '|'
               MISSING FIELD VALUES ARE NULL (
                 col_1 VARCHAR2(100),
                 col_2 VARCHAR2(100),
                 col_3 VARCHAR2(100),
                 col_4 VARCHAR2(100),
                 etc...
               )
     )
     LOCATION your_db_directory_where the_csv_is_storded: 'file_name_of_the_created_csv.csv')
   )
   REJECT LIMIT UNLIMITED;

6. Read the CSV using an external table
   select * from your_exttbl;