Read an Excel xlsx with PL/SQL P1010827 S

Read an Excel xlsx with PL/SQL

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
** 22-01-2016 – Anton Scheffer
free some used memory
** 02-02-2016 – Anton Scheffer
fixed bug regarding only returning first sheet, thanks Martin Goblet
** 19-07-2017 – Anton Scheffer
added fix for some invalid xlsx wrongly having /xl/ before file location

Tags:,

131 Comments

  1. Jose Luis A.R. July 25, 2016
    • Anton Scheffer July 25, 2016
      • Jose Luis A.R. August 1, 2016
  2. Simon June 30, 2016
  3. Simon June 29, 2016
    • Anton Scheffer June 29, 2016
  4. Lucas June 25, 2016
  5. COUAS June 13, 2016
    • Anton Scheffer June 13, 2016
      • Martin Goblet June 13, 2016
        • COUAS June 14, 2016
          • Anton Scheffer June 14, 2016
  6. Nagaraj June 12, 2016
    • Anton Scheffer June 13, 2016
      • Nagaraj June 17, 2016
      • Nagaraj June 17, 2016
  7. Nagaraj June 10, 2016
  8. GZB4ZP March 16, 2016
    • Anton Scheffer March 17, 2016
      • GZB4ZP March 17, 2016
      • Stanislav March 17, 2016
        • Sampathkumar March 21, 2016
          • Anton Scheffer March 21, 2016
          • martin.goblet@dornach.eu March 21, 2016
          • Sampathkumar March 21, 2016
          • Sampathkumar March 21, 2016
          • snaseer March 21, 2016
      • Manoj May 27, 2016
        • Anton Scheffer May 27, 2016
        • Andrew May 27, 2016
  9. Martin Goblet February 2, 2016
    • Anton Scheffer February 2, 2016
      • naveen reddy Somu April 14, 2016
        • Anton Scheffer April 14, 2016
          • naveen reddy Somu April 14, 2016
          • Anton Scheffer April 14, 2016
  10. mahmoud kandeel January 24, 2016
  11. mahmoud kandeel January 24, 2016
  12. Andrew January 21, 2016
  13. mahmoud kandeel January 21, 2016
  14. mahmoud kandeel January 21, 2016
  15. Martin Goblet January 21, 2016
  16. mahmoud kandeel January 21, 2016
  17. mahmoud kandeel January 21, 2016
  18. Martin Goblet January 21, 2016
  19. mahmoud kandeel January 21, 2016
    • Anton Scheffer January 21, 2016
  20. Martin Goblet January 21, 2016
  21. mahmoud kandeel January 21, 2016
  22. mahmoud kandeel January 14, 2016
    • Anton Scheffer January 19, 2016
  23. Lilianna November 18, 2015
    • MARTIN GOBLET November 18, 2015
      • Lilianna November 19, 2015
  24. TH Tech September 13, 2015
  25. Jack R August 1, 2015
  26. Sagay June 3, 2015
    • Anton Scheffer June 3, 2015
  27. CUGAR April 22, 2015
    • Anton Scheffer April 23, 2015
    • Nicolas Denis April 23, 2015
  28. Martin Goblet March 12, 2015
    • Andrew March 12, 2015
      • Martin Goblet March 12, 2015
        • Andrew March 12, 2015
  29. Steven Holley August 6, 2014
  30. Sokratis Aristovoulidis May 17, 2014
  31. Martin Goblet May 14, 2014
    • Anton Scheffer May 16, 2014
      • Martin Goblet May 19, 2014
  32. critian April 28, 2014
  33. Cristian April 9, 2014
  34. Nicolas DENIS April 2, 2014
  35. Jackson March 25, 2014
    • Anton Scheffer March 25, 2014
  36. Krishna Santosh February 10, 2014
    • Anton Scheffer February 10, 2014
  37. Guido Leenders December 14, 2013
  38. N.S. December 12, 2013
  39. N.S. December 10, 2013
    • Anton Scheffer December 10, 2013
      • Martin Goblet March 12, 2015
        • mago28 March 12, 2015
  40. Frank November 10, 2013
    • Anton Scheffer November 11, 2013
      • Herbert Broich March 10, 2015
        • Anton Scheffer March 10, 2015
          • Herbert Broich March 12, 2015
  41. Ralph Bieber November 8, 2013
    • Anton Scheffer November 8, 2013
  42. Alban October 31, 2013
  43. M.R. October 23, 2013
  44. M. October 22, 2013
    • Anton Scheffer October 22, 2013
    • Jackson March 25, 2014
  45. Andrew Markiewicz October 11, 2013
  46. Robertson September 25, 2013
  47. Stan September 17, 2013
    • Anton Scheffer September 17, 2013
  48. TJ Hall July 18, 2013
    • Anton Scheffer July 18, 2013
      • Selva January 22, 2015
      • Selva January 22, 2015
      • Alberto March 31, 2015
  49. Jon Renaut July 15, 2013
    • Anton Scheffer July 15, 2013
      • michael July 15, 2013
        • Anton Scheffer July 16, 2013
          • Jon Renaut July 16, 2013
          • michael July 16, 2013
  50. michael July 9, 2013
    • Anton Scheffer July 9, 2013
  51. Naseer June 11, 2013
  52. Naseer June 11, 2013
  53. Naseer June 10, 2013
  54. Anton Scheffer June 10, 2013
  55. Naseer June 10, 2013
  56. Naseer June 10, 2013
    • Anton Scheffer June 10, 2013
  57. tuataneo February 19, 2013
    • Anton Scheffer February 19, 2013
    • Jackson March 25, 2014
  58. ynixon January 20, 2013
    • Anton Scheffer January 20, 2013
      • regrbde February 19, 2013
        • Anton Scheffer February 19, 2013
          • critian April 28, 2014
          • regrbde May 9, 2014
        • critian April 28, 2014