You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@poi.apache.org by Anjana Fernando <la...@gmail.com> on 2010/04/16 17:26:51 UTC
SQL based data access for POI-HSSF and POI-XSSF
Hi,
I'm hoping this is the correct list to ask this question. I was
wondering, if it will be useful if there is an SQL based method (JDBC
Driver) for querying data in Excel files using Apache POI. It can be a
subset of standard SQL, where mostly used operations such as SELECT,
INSERT, UPDATE etc .. can be run, and maybe also transaction support,
i.e. serializable isolation level. I'm willing to put in the work for
adding this functionality, if this is something worth doing .. hoping
for comments :) ..
Thanks,
Anjana.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
Re: SQL based data access for POI-HSSF and POI-XSSF
Posted by Anjana Fernando <la...@gmail.com>.
On Sat, Apr 17, 2010 at 8:58 PM, MSB <ma...@tiscali.co.uk> wrote:
>
> Ah, I understand a bit better now and applaud the endeavour.
:) ..
> It just struck me that using an existing JDBC bridge would allow you to 'forget' syntax
> checking and the like and allow you to concentrate on what, I guess, will be
> a GUI allowing the user to view field names and select them so as to
> assemble the SQL command - assuming you are aiming at less experienced
> users. In my mind's eye, I had an image of Access's Query Tool.
Yeah, basically the goal is to give the user the control of performing
complex operations easily with the spreadsheet data, so for that, SQL
is an ideal language, since the data already fits the model that is
needed. Other stuff, GUI tools etc.., can be built on top of that as
needed.
Cheers,
Anjana.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
Re: SQL based data access for POI-HSSF and POI-XSSF
Posted by MSB <ma...@tiscali.co.uk>.
Ah, I understand a bit better now and applaud the endeavour. It just struck
me that using an existing JDBC bridge would allow you to 'forget' syntax
checking and the like and allow you to concentrate on what, I guess, will be
a GUI allowing the user to view field names and select them so as to
assemble the SQL command - assuming you are aiming at less experienced
users. In my mind's eye, I had an image of Access's Query Tool.
Yours
Mark B
Anjana Fernando wrote:
>
> Hi Mark,
>
> Thank you for the reply .. I actually came across xlSQL earlier, and I
> was specifically looking for any Apache licensed libraries, and xlSQL
> is GPLv2, which is incompatible with Apache's license as I know. Also
> another reason for using Apache POI is, I guess it is the most mature
> and actively in-development API for Excel support, e.g. having OOXML
> functionality.
>
> Cheers,
> Anjana.
>
> On Sat, Apr 17, 2010 at 12:10 PM, MSB <ma...@tiscali.co.uk> wrote:
>>
>> Have never used it but before committing too much effort to this
>> endeavour -
>> https://xlsql.dev.java.net/
>>
>> Touts itself as "xlSQL is a JDBC Driver for Excel ( CSV, XML and other )
>> document data sources. Documents can be read and written with SQL as if
>> they
>> were tables in a database. In the case of Excel, xlSQL maps a directory
>> with
>> Excel files to a database, workbooks to schemas, and sheets to tables.
>> These
>> tables can be queried with the full SELECT syntax of either the HSQLDB or
>> MySQL (more to follow) SQL dialect. Native xlSQL is a rich subset of SQL
>> for
>> creating, altering and populating documents ( spreadsheets ). The xlSQL
>> Yn
>> codebase, although alpha, has been taken into production at numerous
>> sites
>> worldwide. xlSQL has proven to be a very stable read-write SQL Excel JDBC
>> Driver. Continuous development efforts are aimed to explore the limits of
>> the XLS fileformat for storing relational data and to support other
>> document
>> formats as CSV and XML. ".
>>
>> Yours
>>
>> Mark B
>>
>>
>> Anjana Fernando wrote:
>>>
>>> Hi,
>>>
>>> On Fri, Apr 16, 2010 at 10:28 PM, Anjana Fernando <la...@gmail.com>
>>> wrote:
>>>> On Fri, Apr 16, 2010 at 9:25 PM, Anjana Fernando <la...@gmail.com>
>>>> wrote:
>>>>> On Fri, Apr 16, 2010 at 9:17 PM, David Fisher <df...@jmlafferty.com>
>>>>> wrote:
>>>>>> One possibility is to use Derby
>>>>>> (http://db.apache.org/derby/papers/ApacheCon.html look into "Saucer
>>>>>> Separation") to read an excel sheet into an internal structure. Derby
>>>>>> would then provide the sql capability.
>>>>>>
>>>>>> So, I would recommend you look into incorporating POI under Derby
>>>>>> somehow as the database file driver.
>>>>>>
>>>>>> I were forced to do this that is what I'd do.
>>>>>>
>>>>>
>>>>> Thanks, will take a look :) ..
>>>>>
>>>>
>>>> Hi Dave,
>>>>
>>>> I'm guessing, you were referring to the table functions feature that
>>>> is used in Derby. After going through it a little, I see that it
>>>> cannot be really used to the requirement I'm suggesting. If this
>>>> functionality is to be used in a generic way, the behavior have to be
>>>> dynamic, where the Excel sheets has to be mapped to database tables
>>>> and so in, at the runtime. But table functions require that you give a
>>>> specific static function providing a result set to the external data.
>>>> And also the SQL syntax also will be different when referring to the
>>>> imported tables, but I would prefer if the user is transparent from
>>>> those complexities and can use SQL via a JDBC driver in a way that it
>>>> is talking to a database directly.
>>>
>>> To add to that, I'm guessing you cannot support DML operations with
>>> that.
>>>
>>> Anjana.
>>>
>>> ---------------------------------------------------------------------
>>> To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
>>> For additional commands, e-mail: dev-help@poi.apache.org
>>>
>>>
>>>
>>
>> --
>> View this message in context:
>> http://old.nabble.com/SQL-based-data-access-for-POI-HSSF-and-POI-XSSF-tp28268341p28274406.html
>> Sent from the POI - Dev mailing list archive at Nabble.com.
>>
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
>> For additional commands, e-mail: dev-help@poi.apache.org
>>
>>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
> For additional commands, e-mail: dev-help@poi.apache.org
>
>
>
--
View this message in context: http://old.nabble.com/SQL-based-data-access-for-POI-HSSF-and-POI-XSSF-tp28268341p28276824.html
Sent from the POI - Dev mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
Re: SQL based data access for POI-HSSF and POI-XSSF
Posted by Anjana Fernando <la...@gmail.com>.
Hi Mark,
Thank you for the reply .. I actually came across xlSQL earlier, and I
was specifically looking for any Apache licensed libraries, and xlSQL
is GPLv2, which is incompatible with Apache's license as I know. Also
another reason for using Apache POI is, I guess it is the most mature
and actively in-development API for Excel support, e.g. having OOXML
functionality.
Cheers,
Anjana.
On Sat, Apr 17, 2010 at 12:10 PM, MSB <ma...@tiscali.co.uk> wrote:
>
> Have never used it but before committing too much effort to this endeavour -
> https://xlsql.dev.java.net/
>
> Touts itself as "xlSQL is a JDBC Driver for Excel ( CSV, XML and other )
> document data sources. Documents can be read and written with SQL as if they
> were tables in a database. In the case of Excel, xlSQL maps a directory with
> Excel files to a database, workbooks to schemas, and sheets to tables. These
> tables can be queried with the full SELECT syntax of either the HSQLDB or
> MySQL (more to follow) SQL dialect. Native xlSQL is a rich subset of SQL for
> creating, altering and populating documents ( spreadsheets ). The xlSQL Yn
> codebase, although alpha, has been taken into production at numerous sites
> worldwide. xlSQL has proven to be a very stable read-write SQL Excel JDBC
> Driver. Continuous development efforts are aimed to explore the limits of
> the XLS fileformat for storing relational data and to support other document
> formats as CSV and XML. ".
>
> Yours
>
> Mark B
>
>
> Anjana Fernando wrote:
>>
>> Hi,
>>
>> On Fri, Apr 16, 2010 at 10:28 PM, Anjana Fernando <la...@gmail.com>
>> wrote:
>>> On Fri, Apr 16, 2010 at 9:25 PM, Anjana Fernando <la...@gmail.com>
>>> wrote:
>>>> On Fri, Apr 16, 2010 at 9:17 PM, David Fisher <df...@jmlafferty.com>
>>>> wrote:
>>>>> One possibility is to use Derby
>>>>> (http://db.apache.org/derby/papers/ApacheCon.html look into "Saucer
>>>>> Separation") to read an excel sheet into an internal structure. Derby
>>>>> would then provide the sql capability.
>>>>>
>>>>> So, I would recommend you look into incorporating POI under Derby
>>>>> somehow as the database file driver.
>>>>>
>>>>> I were forced to do this that is what I'd do.
>>>>>
>>>>
>>>> Thanks, will take a look :) ..
>>>>
>>>
>>> Hi Dave,
>>>
>>> I'm guessing, you were referring to the table functions feature that
>>> is used in Derby. After going through it a little, I see that it
>>> cannot be really used to the requirement I'm suggesting. If this
>>> functionality is to be used in a generic way, the behavior have to be
>>> dynamic, where the Excel sheets has to be mapped to database tables
>>> and so in, at the runtime. But table functions require that you give a
>>> specific static function providing a result set to the external data.
>>> And also the SQL syntax also will be different when referring to the
>>> imported tables, but I would prefer if the user is transparent from
>>> those complexities and can use SQL via a JDBC driver in a way that it
>>> is talking to a database directly.
>>
>> To add to that, I'm guessing you cannot support DML operations with that.
>>
>> Anjana.
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
>> For additional commands, e-mail: dev-help@poi.apache.org
>>
>>
>>
>
> --
> View this message in context: http://old.nabble.com/SQL-based-data-access-for-POI-HSSF-and-POI-XSSF-tp28268341p28274406.html
> Sent from the POI - Dev mailing list archive at Nabble.com.
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
> For additional commands, e-mail: dev-help@poi.apache.org
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
Re: SQL based data access for POI-HSSF and POI-XSSF
Posted by MSB <ma...@tiscali.co.uk>.
Have never used it but before committing too much effort to this endeavour -
https://xlsql.dev.java.net/
Touts itself as "xlSQL is a JDBC Driver for Excel ( CSV, XML and other )
document data sources. Documents can be read and written with SQL as if they
were tables in a database. In the case of Excel, xlSQL maps a directory with
Excel files to a database, workbooks to schemas, and sheets to tables. These
tables can be queried with the full SELECT syntax of either the HSQLDB or
MySQL (more to follow) SQL dialect. Native xlSQL is a rich subset of SQL for
creating, altering and populating documents ( spreadsheets ). The xlSQL Yn
codebase, although alpha, has been taken into production at numerous sites
worldwide. xlSQL has proven to be a very stable read-write SQL Excel JDBC
Driver. Continuous development efforts are aimed to explore the limits of
the XLS fileformat for storing relational data and to support other document
formats as CSV and XML. ".
Yours
Mark B
Anjana Fernando wrote:
>
> Hi,
>
> On Fri, Apr 16, 2010 at 10:28 PM, Anjana Fernando <la...@gmail.com>
> wrote:
>> On Fri, Apr 16, 2010 at 9:25 PM, Anjana Fernando <la...@gmail.com>
>> wrote:
>>> On Fri, Apr 16, 2010 at 9:17 PM, David Fisher <df...@jmlafferty.com>
>>> wrote:
>>>> One possibility is to use Derby
>>>> (http://db.apache.org/derby/papers/ApacheCon.html look into "Saucer
>>>> Separation") to read an excel sheet into an internal structure. Derby
>>>> would then provide the sql capability.
>>>>
>>>> So, I would recommend you look into incorporating POI under Derby
>>>> somehow as the database file driver.
>>>>
>>>> I were forced to do this that is what I'd do.
>>>>
>>>
>>> Thanks, will take a look :) ..
>>>
>>
>> Hi Dave,
>>
>> I'm guessing, you were referring to the table functions feature that
>> is used in Derby. After going through it a little, I see that it
>> cannot be really used to the requirement I'm suggesting. If this
>> functionality is to be used in a generic way, the behavior have to be
>> dynamic, where the Excel sheets has to be mapped to database tables
>> and so in, at the runtime. But table functions require that you give a
>> specific static function providing a result set to the external data.
>> And also the SQL syntax also will be different when referring to the
>> imported tables, but I would prefer if the user is transparent from
>> those complexities and can use SQL via a JDBC driver in a way that it
>> is talking to a database directly.
>
> To add to that, I'm guessing you cannot support DML operations with that.
>
> Anjana.
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
> For additional commands, e-mail: dev-help@poi.apache.org
>
>
>
--
View this message in context: http://old.nabble.com/SQL-based-data-access-for-POI-HSSF-and-POI-XSSF-tp28268341p28274406.html
Sent from the POI - Dev mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
Re: SQL based data access for POI-HSSF and POI-XSSF
Posted by Anjana Fernando <la...@gmail.com>.
Hi,
On Fri, Apr 16, 2010 at 10:28 PM, Anjana Fernando <la...@gmail.com> wrote:
> On Fri, Apr 16, 2010 at 9:25 PM, Anjana Fernando <la...@gmail.com> wrote:
>> On Fri, Apr 16, 2010 at 9:17 PM, David Fisher <df...@jmlafferty.com> wrote:
>>> One possibility is to use Derby (http://db.apache.org/derby/papers/ApacheCon.html look into "Saucer Separation") to read an excel sheet into an internal structure. Derby would then provide the sql capability.
>>>
>>> So, I would recommend you look into incorporating POI under Derby somehow as the database file driver.
>>>
>>> I were forced to do this that is what I'd do.
>>>
>>
>> Thanks, will take a look :) ..
>>
>
> Hi Dave,
>
> I'm guessing, you were referring to the table functions feature that
> is used in Derby. After going through it a little, I see that it
> cannot be really used to the requirement I'm suggesting. If this
> functionality is to be used in a generic way, the behavior have to be
> dynamic, where the Excel sheets has to be mapped to database tables
> and so in, at the runtime. But table functions require that you give a
> specific static function providing a result set to the external data.
> And also the SQL syntax also will be different when referring to the
> imported tables, but I would prefer if the user is transparent from
> those complexities and can use SQL via a JDBC driver in a way that it
> is talking to a database directly.
To add to that, I'm guessing you cannot support DML operations with that.
Anjana.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
Re: SQL based data access for POI-HSSF and POI-XSSF
Posted by Anjana Fernando <la...@gmail.com>.
On Fri, Apr 16, 2010 at 9:25 PM, Anjana Fernando <la...@gmail.com> wrote:
> On Fri, Apr 16, 2010 at 9:17 PM, David Fisher <df...@jmlafferty.com> wrote:
>> One possibility is to use Derby (http://db.apache.org/derby/papers/ApacheCon.html look into "Saucer Separation") to read an excel sheet into an internal structure. Derby would then provide the sql capability.
>>
>> So, I would recommend you look into incorporating POI under Derby somehow as the database file driver.
>>
>> I were forced to do this that is what I'd do.
>>
>
> Thanks, will take a look :) ..
>
Hi Dave,
I'm guessing, you were referring to the table functions feature that
is used in Derby. After going through it a little, I see that it
cannot be really used to the requirement I'm suggesting. If this
functionality is to be used in a generic way, the behavior have to be
dynamic, where the Excel sheets has to be mapped to database tables
and so in, at the runtime. But table functions require that you give a
specific static function providing a result set to the external data.
And also the SQL syntax also will be different when referring to the
imported tables, but I would prefer if the user is transparent from
those complexities and can use SQL via a JDBC driver in a way that it
is talking to a database directly.
Cheers,
Anjana.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
Re: SQL based data access for POI-HSSF and POI-XSSF
Posted by Anjana Fernando <la...@gmail.com>.
On Fri, Apr 16, 2010 at 9:17 PM, David Fisher <df...@jmlafferty.com> wrote:
> One possibility is to use Derby (http://db.apache.org/derby/papers/ApacheCon.html look into "Saucer Separation") to read an excel sheet into an internal structure. Derby would then provide the sql capability.
>
> So, I would recommend you look into incorporating POI under Derby somehow as the database file driver.
>
> I were forced to do this that is what I'd do.
>
Thanks, will take a look :) ..
Cheers,
Anjana.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
Re: SQL based data access for POI-HSSF and POI-XSSF
Posted by David Fisher <df...@jmlafferty.com>.
One possibility is to use Derby (http://db.apache.org/derby/papers/ApacheCon.html look into "Saucer Separation") to read an excel sheet into an internal structure. Derby would then provide the sql capability.
So, I would recommend you look into incorporating POI under Derby somehow as the database file driver.
I were forced to do this that is what I'd do.
Dave
On Apr 16, 2010, at 8:34 AM, Nick Burch wrote:
> On Fri, 16 Apr 2010, Anjana Fernando wrote:
>> I was wondering, if it will be useful if there is an SQL based method (JDBC Driver) for querying data in Excel files using Apache POI. It can be a subset of standard SQL, where mostly used operations such as SELECT, INSERT, UPDATE etc .. can be run, and maybe also transaction support, i.e. serializable isolation level.
>
> I must confess I've never had a single occasion where I thought "I wish I
> could query an excel file through SQL"... However, if you have, then perhaps it's not that unusual!
>
>> I'm willing to put in the work for adding this functionality, if this is something worth doing .. hoping for comments :) ..
>
> If you'd like to do some work on this, I'm sure we'd be happy to put it into the contrib module or somewhere like that. Be aware though that I suspect it'll be even more memory hungry than POI normally is... You'd probably want to use the common (ss) usermodel, so it can work for both old and new files
>
> Nick
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
> For additional commands, e-mail: dev-help@poi.apache.org
>
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
Re: SQL based data access for POI-HSSF and POI-XSSF
Posted by Anjana Fernando <la...@gmail.com>.
On Fri, Apr 16, 2010 at 9:04 PM, Nick Burch <ni...@alfresco.com> wrote:
> On Fri, 16 Apr 2010, Anjana Fernando wrote:
>>
>> I was wondering, if it will be useful if there is an SQL based method
>> (JDBC Driver) for querying data in Excel files using Apache POI. It can be a
>> subset of standard SQL, where mostly used operations such as SELECT, INSERT,
>> UPDATE etc .. can be run, and maybe also transaction support, i.e.
>> serializable isolation level.
>
> I must confess I've never had a single occasion where I thought "I wish I
> could query an excel file through SQL"... However, if you have, then perhaps
> it's not that unusual!
>
Yeah, I'll give an example. I'm a developer in WSO2 Data Services
Server project, and we use Apache POI for Excel data source support.
At the moment, we only provide the user to query Excel data using the
column names/numbers and get the values. And doesn't currently support
for a way to insert data. So if there's a query language like SQL, the
user can very easily create complex queries, like . retrieving data
from several worksheets, manipulate that data, and inserting data to a
specific location.
>> I'm willing to put in the work for adding this functionality, if this is
>> something worth doing .. hoping for comments :) ..
>
> If you'd like to do some work on this, I'm sure we'd be happy to put it into
> the contrib module or somewhere like that. Be aware though that I suspect
> it'll be even more memory hungry than POI normally is... You'd probably want
> to use the common (ss) usermodel, so it can work for both old and new files
>
That's great :) ..
> Nick
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
> For additional commands, e-mail: dev-help@poi.apache.org
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
Re: SQL based data access for POI-HSSF and POI-XSSF
Posted by Nick Burch <ni...@alfresco.com>.
On Fri, 16 Apr 2010, Anjana Fernando wrote:
> I was wondering, if it will be useful if there is an SQL based method
> (JDBC Driver) for querying data in Excel files using Apache POI. It can
> be a subset of standard SQL, where mostly used operations such as
> SELECT, INSERT, UPDATE etc .. can be run, and maybe also transaction
> support, i.e. serializable isolation level.
I must confess I've never had a single occasion where I thought "I wish I
could query an excel file through SQL"... However, if you have, then
perhaps it's not that unusual!
> I'm willing to put in the work for adding this functionality, if this is
> something worth doing .. hoping for comments :) ..
If you'd like to do some work on this, I'm sure we'd be happy to put it
into the contrib module or somewhere like that. Be aware though that I
suspect it'll be even more memory hungry than POI normally is... You'd
probably want to use the common (ss) usermodel, so it can work for both
old and new files
Nick
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org