Anthony E. Greene wrote:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Franco Fornari wrote:
I don't need to modify anything. I'd only wish to be able to build
queries based on more than one sheet/table. At present this isn't
allowed in Base, if the database backend is a worksheet.
Hi Franco, Anthony
I understand about you not needing anything more then queries with
joins. Ocke has mentioned that this is an enhancement that is on the
list, but ( and please anyone correct me if I am wrong ) even if someone
volunteered to tackle this work in the OOo code today I don't see how
you could expect the final product in a stable release sooner then six
months. For this reason you might want to seriously look at one of these
alternate approaches for now.
I understand. What I'm saying is that maybe there is an easy way to get
the data into a database instead of using a spreadsheet.
On Linux, you could have a Perl script that would:
1. Verify that the CSV file exists.
2. Open a connection to the DB.
3. Issue an SQL DELETE statement to delete the existing data.
4. Read the CSV file line by line,
a. Parse the line and place the values into an array.
b. Create and execute an SQL INSERT statement to create a
record from the array elements.
5. Notify you of the number of lines processed and the number of records
successfully inserted into the DB.
After writing such a script, your actions would be:
1. Download the data, saving it to a specified location with a specified
name.
2. Run the script.
This is simpler than a spreadsheet import, but only if your environment
supports a scripted connection to your DB.
Of course his environment supports scripting, at a minumum one has
access to OOBasic. :-) This is more then powerful enough to automate
this task. Of corse if a person wanted to use a differnt scripting
language they could do so, I am just pointing out that OpenOffice always
has at least one available.
The first question then is: "How consistent is the data layout in the
Excel document you get?".
If the answer is "Quite consistent" then automating this process with a
script will not be very difficult. If the data layout is changing every
time you download the file - then you can still automate it, but the
script will be a bit more complicated, but not too much so.
The second question is: "How comfortable are you with writing your own
scripts?".
If the thought doesn't throw you into a tail spin then I would suggest
you try to put together an example spreadsheet with data then you can
make available to others and come on over to the Base board at OOoForum.
There are plenty of guys willing to help with this type of project.
If on the other hand the idea is not very enticing and you don't want to
wait for the enhacement then ( and please no throwing of tomatos at me )
there are at least 2 individuals I know of that would take on this type
of project for hire. I don't see how the cost would be very much, but it
would cost you something, beyond that there is a whole list of OOo
consultants mainained on the OpenOffice main web site.
Either way, if you want pursue the scripting approach let the folks
here, or at the Forum, know and I believe you will find plenty of people
ready to help.
Sincerely
Drew
