Read an Excel xlsx with PL/SQL

55

At the OTN SQL and PLSQL forum I promised to publish some code I use for a project I’ still working on. This code allows you to select the content from an Excel document

select * from table( as_read_xlsx.read( as_read_xlsx.file2blob( 'DOC', 'Book1.xlsx' ) ) );

  SHEET_NR SHEET_NAME ROW_NR COL_NR CELL  CEL STRING_VAL NUMBER_VAL DATE_VAL
---------- ---------- ------ ------ ----- --- ---------- ---------- --------------------------
         1 Mijn naam       1      1 A1    N                      11
         1 Mijn naam       1      2 B1    N                      12
         1 Mijn naam       1      3 C1    N                      13
         1 Mijn naam       2      1 A2    N                      21
         1 Mijn naam       2      2 B2    N                      22
         1 Mijn naam       2      3 C2    N                      23
         1 Mijn naam       3      1 A3    N                      31
         1 Mijn naam       3      2 B3    N                      32
         1 Mijn naam       3      3 C3    N                      33
         1 Mijn naam       4      4 D4    S   D4
         1 Mijn naam       6      2 B6    D
         1 Mijn naam       7      2 B7    D
         1 Mijn naam       8      2 B8    D
         1 Mijn naam       9      2 B9    D
         1 Mijn naam      10      2 B10   D
         2 Sheet3          2      2 B2    S   Test
         2 Sheet3          3      3 C3    D                         19-JAN-2013 20:17:00
         2 Sheet3          4      1 A4    S   Anton

18 rows selected.

So here it is

Anton

** Changelog:
** 18-02-2013 – Ralph Bieber
Handle cell type “str” to prevent ORA-06502
if cell content is a string calculated by formula,
then cell type is “str” instead of “s” and value is inside tag
** 19-02-2013 – Ralph Bieber
Add column formula in tp_one_cell record, to show, if value is calculated by formula
** 20-02-2013 – Anton Scheffer
Handle cell types ‘inlineStr’ and ‘e’ to prevent ORA-06502
** 19-03-2013 – Anton Scheffer
Support for formatted and empty strings
Handle columns per row to prevent ORA-31186: Document contains too many nodes
** 12-06-2013 – Anton Scheffer
Handle sharedStrings.xml on older Oracle database versions
** 18-09-2013 – Anton Scheffer
** 18-09-2013 – Anton Scheffer
Fix for LPX-00200 could not convert from encoding UTF-8 to …
(Note, this is an error I can’t reproduce myself, maybe depending on database version and characterset)
Thank you Stanislav Safonov for this solution
Handle numbers with scientific notation
** 20-01-2014 – Anton Scheffer
Fix for a large number (60000+) of strings
** 16-05-2014 – Anton Scheffer
round to 15 digits

Share.

About Author

55 Comments

  1. Hi Anton

    Many thanks for this code, it saved me a lot of time.

    Just 2 points where I’ve some trouble with.
    - Numbers appears like this 0.82280000000000009 instead of 0.8228. There are some false decimals at the end. Workaround I use is a round(), because I know the expected decimals in this field.
    - I’ve a worksheet (link placed in the website) with a formula in a column (for each row). I only get the formula from the first row, the others are null.

    Regards
    Martin

    • Anton Scheffer on

      @Martin Goblet
      I’ve added rounding to the package.
      The formula’s are empty because it are “shared” formula’s. Something I didn’t know about before, and that’s not handled (yet)

  2. Nicolas DENIS on

    Hello,

    I did do a quick improvement to the code.
    When you put by mistake a large file (did test with a 500K text file) that is NOT a excel file, the get_file take pretty long time to return nothing.

    I did add this at the beginning of the get_file function:
    BEGIN
    IF dbms_lob.substr(p_zipped_blob, 4, 1) = hextoraw(’504B0304′) THEN
    ….
    END IF;
    RETURN NULL;
    END;

    To test the ZIP Header signature. If not a ZIP file, I simply ignore the request. (I could have add an exception but was not required).

    I did also add a function to test if the file look like a valid Excel file:

    FUNCTION testFile(p_xlsX BLOB) RETURN VARCHAR2 IS
    cTest BLOB;
    BEGIN
    cTest := get_file(p_xlsx, ‘xl/workbook.xml’);
    IF cTest IS NULL THEN
    RETURN ‘N’;
    ELSE
    RETURN ‘Y’;
    END IF;
    END;

    Otherwise, Thanks for this useful piece of code!

    • Anton Scheffer on

      @Krishna Santosh
      Make sure your directory object points to an existing directory on the file system, you have enough right to use it, you have enough rights on the file system and you have an excel file at that place.

  3. I’ve been using the XML storage facility of Excel for some time (going back to 2003). It works, too bad Oracle is not delivering a more extensive library set for PL/SQL when compared to Java or .NET. For integration of Excel and Oracle we’ve created an add-in to Excel named Invantive Control. Works the other way around: the user uploads (and downloads) all Excel data to/from a database or data vault. In that way, he can throw away the Excel sheet when done :-)

  4. when I try to open my xlsx file that has 27,916 lines is presenting the error

    ORA-04030: out of process memory When trying to allocate 16,408 bytes

    How could I fix this?

    thank you

  5. It would be possible to show the select in the same way that I see in excel, ie columns and data?

    for example…..

    select * from table( as_read_xlsx.read( as_read_xlsx.file2blob( ‘DOC’, ‘test.xlsx’ ), ‘Sheet1′ ) )

    …. and show as below….

    table_cod table_name product unit description price
    1 name 1 166 CX test 46,56
    2 name 2 203 CX second test name 63,06

    thank you!

  6. Wondering if you can assist? I’ve found this package extremely useful, however some XLSX files are producing the following error:

    ORA-31011: XML parsing failed
    ORA-19202: Error occurred in XML processing
    LPX-00200: could not convert from encoding UTF-8 to ISO-8859-1
    Error at line 257
    ORA-06512: at “SYS.XMLTYPE”, line 265
    ORA-06512: at “CSDPT.AS_READ_XLSX”, line 38
    ORA-06512: at “CSDPT.AS_READ_XLSX”, line 191
    ORA-06512: at line 1
    31011. 00000 – “XML parsing failed”
    *Cause: XML parser returned an error while trying to parse the document.
    *Action: Check if the document to be parsed is valid.

    As best as I can tell, it must be encountering invalid characters and killing the parser, however I do not have any control over the source XLSX file in order to see it remedied. Is there a method whereby the package can incorporate an error trap for this?

    • Try the latest version of this package. It might fix your problem. Note, this is an error I can’t reproduce myself, so I’m not sure if works for you.

  7. I ran into error ORA-06502, when parsing a number with exponential value.
    Cell content was 3.3013052861106202E+17 (number 330130528611062020)

    Following format string in the TO_NUMBER conversion raised the error:
    t_nr := to_number( t_val, translate( t_val, ‘.0123456789,-+’, ‘D9999999999′ ), ‘NLS_NUMERIC_CHARACTERS=.,’ );

    I replaced by:
    t_nr := to_number(t_val, RegExp_Replace(translate(t_val, ‘.0123456789,+-’, ‘D9999999999′ ),’(E).*$’,’EEEE’,1,1,’i’), ‘NLS_NUMERIC_CHARACTERS=.,’ );

    • Anton Scheffer on

      Or you could use
      t_nr := to_number( t_val
      , case when instr( t_val, ‘E’ ) = 0
      then translate( t_val, ‘.012345678,-+’, ‘D999999999′ )
      else translate( substr( t_val, 1, instr( t_val, ‘E’ ) – 1 ), ‘.012345678,-+’, ‘D999999999′ ) || ‘EEEE’
      end
      , ‘NLS_NUMERIC_CHARACTERS=.,’
      );

  8. Thanks a lot, It’s really fast !
    I had a trouble with some empty cells where your package found some values !! (it’s empty in Excel not in Oracle Result) After copy and paste entire sheet in a new one the problem gone. I think the values came from a previous action like ‘cut’ to this cell and the xlsx format keep it.

  9. Amazing solution, works great! Thank you very much for sharing it.

    I have a question though: it does not work with xls files (older Excel format, 2003), it only works with xlsx files. Would be possible to get it to work with xls as well?

    Regards,
    M. Rusu, Canada

  10. Andrew Markiewicz on

    Thanks for this package. It saved me a lot of work.
    However, there currently seems to be a memory leak using dbms_xmldom. I am on Oracle 11.2.0.3 so I thought this would have been fixed from 10g.

    I was able to find a fix for this with a minor alteration to your package in the read function. I created a local domdocument variable instead of the implicit document parameter to dbms_xmldom.getdocumentelement()

    ….
    t_ndoc dbms_xmldom.domdocument;

    function blob2node( p_blob blob )
    return dbms_xmldom.domnode
    is
    begin
    if p_blob is null or dbms_lob.getlength( p_blob ) = 0
    then
    return null;
    end if;
    t_ndoc := dbms_xmldom.newdomdocument( xmltype( p_blob, nls_charset_id( ‘AL32UTF8′ ) ) );
    –return dbms_xmldom.makenode( dbms_xmldom.getdocumentelement( dbms_xmldom.newdomdocument( xmltype( p_blob, nls_charset_id( ‘AL32UTF8′ ) ) ) ) );
    return dbms_xmldom.makenode( dbms_xmldom.getdocumentelement( t_ndoc ) );
    end;

    This allows us to explicitly free up the memory at the end of the pipelined function with dbms_xmldom.freedocument(). I also call dbms_session.free_unused_user_memory() for good measure but that may not be necessary.

    ……
    dbms_xmldom.freedocument(t_ndoc);
    dbms_session.free_unused_user_memory;
    return;
    end;

    Thanks again
    Andrew

  11. Hi Anto,
    Today i copied the package , it is selecting the infinity empty rows after the data has finished in the Excel sheet. Please help on that

  12. Hello, Anton!
    I’ve too stuck with same error, but your cure didn’t help. Is any way to get rid of those unrecognized simbols, or substitute something in place?
    Thank you for your package!

    • @Stan
      It’s a problem I can’t reproduce on my database. So I’m only guessing what the cause could be, and what a solution might be. So, if the “cure” doesn’t help I have no idea how to solve it.
      Anton

  13. Hi. I need somw help to execute. Where is the directory located when I execute the following?

    select *
    from table( as_read_xlsx.read( as_read_xlsx.file2blob( ‘DOC’, ‘Book1.xlsx’ ) ) )

    I created the package in TOAD under my ID and I am trying to execute the select statement in TOAD. I get an error that it can’t find the directory (ORA-22285). Where is it looking for the directory? I tried using c:\dir name but that didn’t work either. Thanks Much.

    • Anton Scheffer on

      That example is looking at directory object DOC. And that directory object could be pointing at a directory some where at your Oracle DB server. Something that you have to set up your self.

  14. Thanks very much for sharing this – you’ve saved me a ton of time. I am, however, having some trouble with the code. It seems to be both doubling the sheets and doubling the cells – if I parse an xlsx with one sheet and one populated cell, I get four entries. Any ideas?

  15. I am trying to read an XLSX file:

    select *
    from table(as_read_xlsx.read(as_read_xlsx.file2blob(‘CALM_RESULTS’,’Closing_Final3.xlsx’),’Main Sheet’, ‘AF7′));

    and getting the following error:

    ERROR at line 2:
    ORA-31011: XML parsing failed
    ORA-19202: Error occurred in XML processing
    LPX-00200: could not convert from encoding UTF-8 to ISO-8859-1
    Error at line 2
    ORA-06512: at “SYS.XMLTYPE”, line 265
    ORA-06512: at “DATASTORE.AS_READ_XLSX”, line 38
    ORA-06512: at “DATASTORE.AS_READ_XLSX”, line 191
    ORA-06512: at line 1

    any ideas?

    Thanks,

    • Anton Scheffer on

      @michael
      Change the character set of your database? The error message says it all, the Excel contains some UTF-8 characters which can’t be handled by your database.
      You could try to handle it by changing this line
      t_nd := blob2node( get_file( p_xlsx, ‘xl/sharedStrings.xml’ ) );
      to
      declare
      t_blob blob;
      t_clob clob;
      t_dest_offset integer;
      t_src_offset integer;
      t_lang_context number := dbms_lob.default_lang_ctx;
      t_warning integer;
      XML_parsing_failed exception;
      PRAGMA EXCEPTION_INIT( XML_parsing_failed, -31011 );
      begin
      t_nd := blob2node( get_file( p_xlsx, ‘xl/sharedStrings.xml’ ) );
      exception
      when XML_parsing_failed
      then
      dbms_lob.createtemporary( t_clob, true);
      t_dest_offset := 1;
      t_src_offset := 1;
      dbms_lob.converttoclob( t_clob
      , get_file( p_xlsx, ‘xl/sharedStrings.xml’ )
      , dbms_lob.lobmaxsize
      , t_dest_offset
      , t_src_offset
      , nls_charset_id( ‘AL32UTF8′ )
      , t_lang_context
      , t_warning
      );
      dbms_lob.createtemporary( t_blob, true);
      t_dest_offset := 1;
      t_src_offset := 1;
      dbms_lob.converttoblob( t_blob
      , t_clob
      , dbms_lob.lobmaxsize
      , t_dest_offset
      , t_src_offset
      , nls_charset_id( ‘AL32UTF8′ )
      , t_lang_context
      , t_warning
      );
      t_nd := blob2node( t_blob );
      dbms_lob.freetemporary( t_blob );
      dbms_lob.freetemporary( t_clob );
      end;

  16. Anton thanks a lot for your help. With your help I am able to see the data in the string_val column by replacing pattern from ‘.’ to ‘*/text()’

    Thanks again.

  17. I think in my case this line is causing string_val to be empty.
    t_strings( i ) := dbms_xslprocessor.valueof( dbms_xmldom.item( t_nl, i ), ‘.’ );

    It looks like patron ‘.’ is incorrect because dbms_xslprocessor.valueof( dbms_xmldom.item( t_nl, i ), ‘.’ ); is returning NULL.

    Do you think there is some kind of version issue with XML related packages?

  18. Query is:
    select *
    from table( as_read_xlsx.read( as_read_xlsx.file2blob( ‘UPLOAD_DIR’, ‘Spring2013.xlsx’ ) ) )

    Query can read file and correctly list all columns except STRING_VAL (I am suing formula)

    I will send you .xlsx file in few minutes.

    Regards,

  19. Anton Scheffer on

    You could show me the query you are using or send me the excel you use
    scheffer @ “the company I work” .nl

  20. Thanks for quick reply. My bad I though I gave the necessary info :(.
    In my case whenever the output for column CELL_TYPE is S in these cases STRING_VAL column is blank.
    I tried to play with line below but no success.
    t_one_cell.string_val := t_strings( to_number( t_val ) );

    What info you are looking to suggest any possible reason.

    Thanks a lot.

  21. Hello Anton,

    This is great utility. In my case String valuses are not comming up (blank). Number are comming correctly. We are at office 2010 and db is 11.1.0.7.0 – 64bit.

    Any Idea?

    Thanks,

    Naseer – Houston

    • Anton Scheffer on

      @Naseer.
      No, I have no ideas. But it could be that you gave just to little information to work on :).

  22. Excellent,
    Since I am using oracle 10g, I cannot use the PIVOT function
    Are you going to implement a formatted output the same as it looks in the excel ?

    • Anton Scheffer on

      @ynixon
      No, I have no plans to change anything in this code. But if you know that your Excel has always the same number of columns its very easy to change the code yourself to return all Excel columns in one row.

      Anton

Leave a Reply