[ale] sql query on oo spreadsheet?

[Brian Pitts]

Tim Watts wrote:
> Hi,
> 
> I have an open office spreadsheet which will grow to a considerable size 
> (~1000+/- rows in a year) and I want to run sql queries against it. I guess 
> I'm looking for an ODBC driver that can do this but I don't see any support 
> for it in OO. The help talks about it but I don't see anything in the UI. I'm 
> using OO 2.4 packaged with Ubuntu 8.10.

I see a few possibilities.

1) Store it in a database to begin with.

Do you really have to use spreadsheet? What about using OO Base [0], a
"fully featured desktop database management system". In my experience
this program is a bit clunky, but it's been two years since I last used
it so things may have changed

2) Store it in a oo spreadsheet and have oo base use that as a data source.

>From the documentation [1]: "Select File > New > Database. Select
Connect to an existing database. Select Spreadsheet as the Database
type. Click Browse to locate the spreadsheet you want to access. If the
spreadsheet is password protected, check the Password required box.
Click Next. If the spreadsheet requires a user’s name, enter it. If a
password is also required, check its box. Click Next.
Caution     This method of accessing a spreadsheet does not allow you to
change anything in the spreadsheet. All modifications must be made in
the spreadsheet itself. This method only allows you to view the contents
of the spreadsheet, run queries, and create reports based upon the data
already entered into the spreadsheet."

3) Store it in a oo spreadsheet which is periodically dumped to csv.

You can then import the csv into your database of choice or use DBD::CSV
[2] to run queries directly on the csv file.

4) Store it in a oo spreadsheet which is periodically dumped to excel.

If your spreadsheet can't be converted to csv (maybe it had multiple
worksheets, formulas, etc) you can probably save it in excel format. You
might be able to use DBD::Excel to run queries directly on the excel
file. If that doesn't work, you may still find that parsing the excel
file in Perl [4] or Python [5] is faster than doing the operations by
hand or writing yucky oo macros.

[0] http://www.openoffice.org/product/base.html
[1]
http://documentation.openoffice.org/manuals/oooauthors2/0110GS-GettingStartedWithBase.pdf
[2] http://search.cpan.org/dist/DBD-CSV-0.22/lib/DBD/CSV.pm
[3] http://search.cpan.org/dist/DBD-Excel-0.06/Excel.pm
[4] http://search.cpan.org/dist/Spreadsheet-ParseExcel/
[5] http://pypi.python.org/pypi/xlrd

-- 
All the best,
Brian Pitts