1

I'm trying to build a package with a procedure which takes .XLSX file, parse and insert to a table in database.

What I have achieved so far is the code below:

create or replace package body pkg_name
is
    procedure fetch_and_insert_data
    is
        l_display   varchar2(32767);
        l_id        varchar2(32767);
        l_filename  varchar2(100)    := 'my_file.xlsx';

        v_file      UTL_FILE.FILE_TYPE; 
    begin        
      execute immediate 'create or replace directory XOBJ as ''' || c_file_dir || '''';
      v_file := UTL_FILE.FOPEN(location     => 'XOBJ', 
                               filename     => l_filename, 
                               open_mode    => 'r', 
                               max_linesize => 32767);

      loop
         begin
            UTL_FILE.GET_LINE(v_file,l_id);
            dbms_output.put_line(l_id);
            EXCEPTION
              WHEN NO_DATA_FOUND THEN
            EXIT;
         end;
      end loop;

      UTL_FILE.FCLOSE(v_file); 

      EXCEPTION 
      WHEN OTHERS THEN 
        UTL_FILE.FCLOSE(v_file); 
      RAISE; 

    end fetch_and_insert_data;
end;

But when I run it it's encoded .

PK!bîha[Content_Types].xml ¢( ¬”MOÃ0†ïHü‡WÔfã€Z·G˜Äø¡q×hiÅÞØþ=nö!„ʦ‰]µ±ß÷‰kg4Y·6[ADã])†Å@d௛—âcö’ß‹I9­¬wPŠ

How can I decode and maintain my line as it is in XLSX file? Tried with CSV file and works perfectly but i need to use XLSX files.

Also tried other external packages found online and didn't helped.

Adi
  • 311
  • 4
  • 19
  • 1
    An XLSX file is a zipped set of XML files, it isn't plain text. What external packages did you look at, and what was wrong with them? [This might be useful](https://stackoverflow.com/a/33212086/266304) if you haven't already looked it. – Alex Poole Sep 04 '18 at 12:52
  • Indeed, i know this but it there any way to parse it correctly with UTL_FILE ? – Adi Sep 04 '18 at 12:53
  • UTL_FILE is not a parser, it is a file reader. An XLSX is an XML file compressed with ZIP. To parse XLSX in PL/SQL I've used the code from this blog with good results: https://blogs.oracle.com/apex/easy-xlsx-parser:-just-with-sql-and-plsql – Jeffrey Kemp Sep 07 '18 at 08:07

0 Answers0