Read XLXS Files from APEX 5.0 and Higher – One (or Two) Ways

In my work as a consultant I often have the task of getting data from MS Excel spreadsheets into tables in the database.

If this is a one-time load, the process is simple – use either SQL Developer or, easier yet, use the Data Load Workshop in APEX:  SQL Workshop –> Utilities –> Data Workshop.

If the requirement is for a repeated load or many files, I need to set up some automated process for reading in the data.  I do not want to ask my users to open the XLSX files, save the data to CSV, then upload the CSV files.  That makes for unhappy users, at best.

I am sure there are several options for load XSLX files into Oracle data tables.  I am going to focus on a two I have used (and continue to use) successfully across several clients, across many projects.  And another option that used to be a very good option, that goes away with APEX 5.0 and higher.

Let’s do Bad News First.

The Option That Goes Away:

With APEX Listener, now Oracle REST Data Services, ORDS, we gained a wonderful utility for loading XLS or XLSX files into the database.

I will not elaborate on that here, for reasons which will be obvious in a bit.  I did cover that option, and several other Data Load options, in this presentation, How Do I Load Data, Let Me Count the Ways..  Most of these options still apply.  Yes, it is a PowerPoint, but gives you enough detail to get going.

This ORDS-based utility works fabulous for APEX versions less than APEX 5.0.  It loaded data into a collection. You can process your data however you wish from there.  However, as of APEX 5.0, APEX uses JSON to move data, and the ORDS Excel file upload function no longer works.  Joel Kallman explains this clearly in his blog, Let’s Wreck This Together, here.  It will still work for APEX versions < 5.0 – that is, APEX 4.2, APEX 4.1.  But beware, if you are using this Excel-Upload option of ORDS in APEX 4.1 or APEX 4.2 applications, you will need to make some changes when you upgrade those applications to APEX 5.0 and above.

The XLSX Option That Still Works – READ_XLSX

Thanks to Anton Scheffer from AMIS, we have a PL/SQL package that reads .XSLX files from a file in a file server folder that has been mapped to an Oracle directory.  That sounds like a mouthful, but it is simple.

You have files in a folder.  You or your system administrators arrange for that file system folder to be available to the database – usually a mapping of the physical folder to a virtual folder on the database file server.  Then your DBA creates an Oracle directory that points to the folder, physical or virtual, on the database file system. And grants appropriate grants so you can see and read and/or write from /to that Oracle directory.

Now Anton’s package, READ_XLSX comes in.  download the package, read Anton’s blog post.  I am only going to summarize here.

READ_XLSX consists of two main functions, one to read the contents of an XLSX file into a BLOB (named file2blob), and another to read the contents of that BLOB into records (this one is named “read“).  Cast the results of the read function as a TABLE, and voila, you have the results of your XLSX file.

This statement is the guts of it:

SELECT * FROM TABLE( as_read_xlsx.read( as_read_xlsx.file2blob('MY_ORA_DIRECTORY',
 'MyExcelWorkbook.xlsx' ) ) );

Now if you are like me, you do not want ot have to keep typing that SELECT statement.  So I turn it into a view:

CREATE VIEW MY_SPREADSHEET_V AS SELECT * FROM TABLE( as_read_xlsx.read( as_read_xlsx.file2blob('MY_ORA_DIRECTORY',
 'MyExcelWorkbook.xlsx' ) ) );

Now I can query from the view the same as I query from any other table or view I have access.  And incorporate queries from that view into any packages, procedures and functions that I build to meet the requirements of the task at hand.

When I have a series of files in a folder, I use a version of GET_DIR_LIST to read files from the Oracle directory.  (GET_DIR_LIST is a java utility, wrapped in PL/SQL, that returns the list of file names in a folder in a one-column table). You probably have your own method of doing the same.  Then, in a loop, I use dynamic SQL to point my view to the next file in the loop, call my loading procedure to process the contents of that file, then move on to the next file.  OF course I have all kinds of validations and error checking in there, as much is needed depending on the task at hand.  Once configured, the process is clean, reusable, easily customizable and – best of all – works in APEX 5.0 and above.

I did not use Anton’s READ_XLSX directly.  Why?  At the time I downloaded it (years ago now), Anton’s package handled cells of up to 4000 characters.  The spreadsheets I had to load contains some cells with > 4000 characters.  So I made a slight adjustment to handle CLOBs.  I described those customizations here.  I suspect most persons will not need such a customization.

Another (Better?) Option – EXCEL2COLLECTION Plugin:

Is READ_XLSX the only way? No. Seems like too much work? Yup, it can be.  Anton apparently thought so too, because he came up with EXCEL2COLECTION, a utility APEX Plugin that, with less coding than the READ_XLSX package, will read data from an XLSX, XSL, XML 2003 or CSV file into a PL/SQL collection.  I have also used this plugin successfully across several customers and projects.

I have used the EXCEL2COLLECTION plugin in conjunction with a customized instance of the APEX Data Load Wizard to enable simpler (from a user perspective) upload of XLSX files.  I describe more of that process here.  The APEX Data Load Wizard does not out-of-the-box allow upload of XLSX files – one needs to cut/paste.  Integrating the EXCEL2COLLECTION plugin into a customized Data Load Wizard series of pages makes use of other features of the Data Load Wizard – like the column mapping page – possible.  Beware, that customizing the Data Load Wizard pages and process flow means that your work is subject to break upon any and every APEX upgrade.  This customization of the Data Load Wizard process is unsupported territory.  Note that the EXCEL2COLLECTION plugin itself is supported (by Anton). My use of it in customizing the Data Load Wizard is not.

Bottom line:

Anton’s READ_XLSX remains one of the best methods of reading XLSX data into an Oracle database, when the requirement is to read many XLSX files.  When using APEX, investigate using the EXCEL2COLLECTION plugin; that will make your XLSX load tasks easier.  For one-of data loads, I still use SQL Developer or the APEX SQL Workshop Data Workshop utility.  Need to script the load for Production?  Use SQL Developer in a DEV environment, then use SQL Developer to generate a load script, there are several options for that.

Happy Data Loading!

 

 

 

Last Day! ODTUG Innovation Award

June 2nd is the LAST DAY for ODTUG Innovation Award nominations.

Make your nomination for the latest, greatest piece of awe-inspiring work using Oracle development technologies today.

It’s simple, go to the ODTUG Innovation Award Nomination page. Tell us about your nominee – or nominate yourself!  Everyone is eligible – make your entry today.

Is is a Cloud hybrid?  An APEX plugin? An open-source project that leverages Oracle tools?  A real clever in-house application of  Oracle JET? A really neat product from Oracle?  Anything goes!

Today is the last chance  to make your 2017 nomination.

The ODTUG Innovation Award honors excellence in creative, effective, and innovative use of Oracle development tools within ODTUG’s supported communities (ADF, APEX, BI, Database, EPM and Career). The goal is to honor developers – individuals or teams – whose passion and creativity shine through in their application of Oracle technology to address real-world problems.  Note that this year we are not excluding anything or anyone, so projects and individuals from all organizations (Oracle, too!) are welcome.

Remember that ODTUG members get to vote this year.  Make your nomination, then vote for it too! Member voting will be combined with judges scoring to determine an overall Innovation Award winner.

Check out the full information on the ODTUG web site here:

ODTUG Innovation Award Nominations

Best wishes to all our nominees!

 

ODTUG Innovation Award ~ Make Your Nomination Now!

Nominations for the 2017 ODTUG Innovation Award are open now through June 2nd.  Make your nomination now, before your Memorial Day weekend.

We are looking for innovative, exceptional, ahead-of-the-curve applications of Oracle development tools that stand out as examples of “the next big thing”, or maybe the really neat next small thing,  in Oracle technology.   Can’t think of anything? Nah, I don’t believe that.  Consider:

  • Do  you have, or does your co-worker have an unusually creative application or combination of new and emerging Oracle tools?
  • Did you find a new open source project that makes your life easier?  Open source projects are eligible, as long as they integrate some Oracle technology.
  • Does your latest project enable faster, smoother, cleaner adoption of an Oracle tool?
  • Are you envying someone else’s work, saying “Wish I thought of that …”?
  • Have you seen a real neat learning tool that aids others in understanding a new Oracle tool?   All projects, large and small, are eligible.
  • Does your organization have an exception program for attracting or training persons new to Oracle technology?
  • Have you created, seen or adopted a new plugin or feature, large or small, that others may not have seen yet?

We are looking for the stuff that makes you say “Wow, great idea …”  or “Neat stuff …” or “wicked cool …” (you can tell where I am from).  We need your help to get as many innovation examples in before the June 2nd deadline.

Now is the time to make your nomination.

The ODTUG Innovation Award honors excellence in creative, effective, and innovative use of Oracle development tools within ODTUG’s supported communities (ADF, APEX, BI, Database, EPM and Career). The goal is to honor developers – individuals or teams – whose passion and creativity shine through in their application of Oracle technology to address real-world problems.  Note that this year we are not excluding anything or anyone, so projects and individuals from all organizations (Oracle, too!) are welcome.

Note that ODTUG members get to vote this year.  Make your nomination, then vote for it too! Member voting will be combined with judges scoring to determine an overall Innovation Award winner.

Check out the full information on the ODTUG web site here:

ODTUG Innovation Award Nominations

Nominations close June 2nd, so act soon!

SQL Developer Dropping Connections – Solved

I work fast, most often across several database at once, multitasking across projects. Recently with one client I noticed SQL Developer dropping connections, repeatedly, but only on certain database connections. Most often I would need to reconnect upon opening a table or view, package, trigger, almost any object from the navigator. After successfully compiling something, need to reconnect. After creating a trigger, reconnect. Forget about an After-Update trigger – that visibly disconnected me – so I got in the habit of creating things outside of SQL Dev.  Wait a minute..that’s just wrong. I knew this had to be something easy to fix.

That’s when I started looking … After a bit of research I found this link :

Brendan Tierney – Oralytics Blog: SQL Developer is dropping connections

which explained my problem – and offered a solution:

The solution (since that’s what you care about):

Add this line:

AddVMOption -Doracle.net.disableOob=true

to the sqldeveloper.conf file.

I actually added mine to the sqldeveloper_nodebug.conf file.

For SQL Developer 4.1, for my install on Windows 7, this falls in the …\sqldeveloper\bin\ directory for the SQL Developer install directory.

The problem (the short version):

The theory is that this is being caused by and Out of Bands (OOB) error in the jdbc driver or on the network.  Whatever the error, jdbc driver or network, the constant need to reconnect was rendering SQL Developer almost useless for those connections for which this occurred.   It is not doing that any more – Yeah!

I do expect that this option will block times when SQL Dev should disconnect – so I am paying attention and watching for side effects.  So far, so good.  Hope this helps someone else,

Karen