#13152: Support for Excel file queries

JasonK reported 2015-03-19T12:52:59Z · last modified 2015-08-18T19:19:46Z

Support for Excel file queries

Dev	JennyNishimura
QA	SachinPrakash

Priority	Major
Complexity	Unknown

Component	DB - Excel
Version	17.0

Are there any plans to support "where" or "order by" in Excel queries? If not, can this be added? Currently this requires converting the Excel spreadsheet to a csv file, and then importing the csv file into a database such as H2 using CVSREAD.

https://www.aquaclusters.com/app/home/project/public/aquadatastudio/wikibook/MS-Excel/page/SQL-Query-Reference/SQL-Query-Reference

1 attachment

WhereClauseTest.java

2015-05-20T21:32:48Z

2 KB

All Comments (5) Change History

SachinPrakash 2015-05-20T21:32:05Z

From Vikram:

We have identified 3 approaches.

1. We use POI to read and create our own Data structure (XML or Tree or both) in-memory and map the SQL query to this tree. This will work for both HSSF (old format) and XSSF (new format). The issue here is to be "efficient" in traversal and retrieval from this data structure.

2. We directly read and create a DOM structure of Excel in memory. Then we use XQuery to query this DOM structure. This will work ONLY for XSSF (new format) as that is (zipped) XML. We will need an XQuery processor here. Do we have one already ? We have used SAXON H.E (http://saxon.sourceforge.net/) and this approach might work. We will still need to map (and navigate) the query paths to the XSSF DOM format "efficiently". So basically with this approach we save on the "parsing" (as that is done by the XQuery processor). And we use XQJ to work with this processor.

3. Third option is the MetaModel API. Of all three approaches I am most hopeful on this. Please check this link http://metamodel.apache.org/. And here is an old example http://kasper.eobjects.org/2008/11/query-your-excel-spreadsheet-with-java.html. The API has evolved much since then. PFA a short test code. We are checking it for more complex queries. Will let you know by tomorrow.

JasonK 2015-07-23T16:44:48Z

How did the metamodel POC go? It would seem to me that implementing that for Excel queries might open up the window for improving/adding support for other files types, such as csv, XML or json?

JennyNishimura 2015-07-23T21:33:17Z

I have checked in the code changes to switch over to the MetaModel Excel JDBC Driver. MetaModel supports WHERE, ORDER BY, GROUP BY, and HAVING for Excel queries.

Existing "MS Excel" server registrations should continue to work and they now connect to the MetaModel Excel JDBC Driver. Existing Excel queries and VA workbooks should also work and the queries can now be updated to add WHERE, ORDER BY, GROUP BY, and HAVING clauses.

JasonK 2015-07-23T22:40:17Z

Good deal! I look forward to the future release!

tariqrahiman 2015-08-18T19:19:46Z

Verified in ADS v17.0.0-alpha-20. This feature will be available in ADS v17.0 release towards end of Sep'15

Search Tips

Aqua Data Studio / nhilam

Support for Excel file queries

1 attachment

Issue #13152

Completion

Search Tips

Aqua Data Studio / nhilam

Title

Support for Excel file queries

1 attachment

Issue #13152

Completion