[ale] sql query on oo spreadsheet?

[Tim Watts]

On Sunday 08 March 2009 10:29:00 pm Brian Pitts wrote:
> 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-GettingStart
>edWithBase.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

Thanks Brian.

Using a spreadsheet gives me a simple, robust UI. Option 2 is what I'm looking 
for. Apparently, the base Kubuntu install does not include OO with database 
support. That's what puzzled me. So I'm installing that now and I'll see what 
happens.


-- 
One is left with the horrible feeling now that war settles nothing; that to 
win a war is as disastrous as to lose one.
 -- Agatha Christie