Discussions
Categories
- 385K All Categories
- 2.5K Data
- 580 Big Data Appliance
- 2K Data Science
- 452K Databases
- 222.3K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 32 Multilingual Engine
- 575 MySQL Community Space
- 479 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3.1K ORDS, SODA & JSON in the Database
- 575 SQLcl
- 4K SQL Developer Data Modeler
- 187.7K SQL & PL/SQL
- 21.5K SQL Developer
- 11 Data Integration
- 11 GoldenGate
- 297.6K Development
- 3 Application Development
- 18 Developer Projects
- 140 Programming Languages
- 294.3K Development Tools
- 118 DevOps
- 3.1K QA/Testing
- 646.4K Java
- 30 Java Learning Subscription
- 37K Database Connectivity
- 185 Java Community Process
- 107 Java 25
- 22.1K Java APIs
- 138.3K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 21 Java Essentials
- 172 Java 8 Questions
- 86K Java Programming
- 82 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 206 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 579 LiveLabs
- 41 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 191 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 240 Portuguese
Excel Upload in APEX 5.1
I just found out that the upload from excel (xls) to a collection doesn't work with 5.1 any more. We upgraded an existing 5.0 to 5.1 and this is now bugging us. The setting is normally done in the ords by adding the four lines to support uploading excel:
<entry key="apex.excel2collection">true</entry>
<entry key="apex.excel2collection.onecollection">true</entry>
<entry key="apex.excel2collection.name">EXCEL_COLLECTION</entry>
<entry key="apex.excel2collection.useSheetName">true</entry>
However in APEX 5.1 the simple page with a button to start the upload and the file browse item produces an error.
I tested this on multiple environments where there was an upgrade from 5.0 to 5.1 and it is the same thing.
Anyone with an idea ?
Thanks,
Denes Kubicek
Best Answer
-
Hi Denes,
The Excel to Collection functionality no longer works when using ORDS and APEX 5.1. In APEX 5.1 we've changed the page submission to use JSON, which does not work with the current Excel to Collection implementation in ORDS. Unfortunately we don't have an alternative or work around available at this time.
Regards,
Marc
Answers
-
I was under the impression that the file parsing was done via ORDS code (using Apache POI), not APEX (which has to work on XE - which can't use Java)
I'm wondering: Do you need to upgrade ORDS?
I've always used SQL*Developer to adjust the EXCEL2COLLECTION settings.
Although, I prefer to use the XLS2Collection plugin.
I don't have access to a 5.1 that I can play with. sorry
MK
-
Hi Denes,
The Excel to Collection functionality no longer works when using ORDS and APEX 5.1. In APEX 5.1 we've changed the page submission to use JSON, which does not work with the current Excel to Collection implementation in ORDS. Unfortunately we don't have an alternative or work around available at this time.
Regards,
Marc
-
Marc,
Thanks for answering. Well, this is bad for us since a couple of applications depend on this feature. I will have to think about the workarrounds here. This is not easy now.
Regards,
Denes Kubicek
-
Hi Denes,
as a workaround, you might want to have a look at https://chrisonoracle.wordpress.com/2013/11/13/read-excel-file-in-plsql-using-java-in-the-db/
Regards
Patrick
-
This - Process Type Plugin - EXCEL2COLLECTIONS plugin is a great workaroud
-
Anton,
Many thanks. I could get it working and can use it as an alternative solution until it works again with ORDS.
Regards,
Denes
-
-
Thanks for nice words. It's a nice plugin indeed. Not the best though.
I've never used it myself. I prefer less generic plugins which insert the data into a table, not into an APEX collection.That results into a much better performance, seconds instead of minutes for loading a file with than a few 1000 records.
Anton
-
Some time ago (i believe ords 2.x and apex 4.2) xls2collection was broken. After that was fixed to finally work with xls and xlsx (apex 5.0.2 and listener 2.0.9) and worked (not as efficient as in listener 1.x but it was complete out of the box solution)
Now in 2017 (apex 5.1 and ords 3.x) we go back to 2011 and need to use workaround to keep function live.
I'm always impressed how apex/ords new versions broke old functions without a word.
Without Anton we would have been beaten.
-
After reading this Let's Wreck This Together...with Oracle Application Express!: excel2collection functionality of ORDS will not work with …
Will Anton plugin work without ords support for excel import ?
