database2spreadsheet

[iplaw67]

Hi,

In a future step, I would like to integrate graphics into the
spreadsheet which could be created by a perl script with gnuplot from
data in the database; the images could reside in a directory and rows in
the database would have a field referring to the file name of the image.

If you are going to use Perl to manipulate graphic objects to insert
into a Calc document, why not use Perl for everything, including
querying your db, outputting your data in the desired format, and
inserting that array of data into a Perl-created/manipulated Calc file ?

Look at the following Perl modules :
ODF::lpod
OpenOffice::UNO
OpenOffice::OODoc

Alex

[Villeroy]

Hi,

In a future step, I would like to integrate graphics into the
spreadsheet which could be created by a perl script with gnuplot from
data in the database; the images could reside in a directory and rows in
the database would have a field referring to the file name of the image.

If you are going to use Perl to manipulate graphic objects to insert
into a Calc document, why not use Perl for everything, including
querying your db, outputting your data in the desired format, and
inserting that array of data into a Perl-created/manipulated Calc file ?

Because all this has been implemented already to be used by anyone
without coding.
Hit F4 and drag your query into your favourite spreadsheet template.
Add spreadsheet formulas, (conditional) formatting, charts and stuff.
Finally, you may remove the "store data" flag from the import range so
the user is prompted to update the import range when opening this
document. All this works reliably out of the box.

Look at the following Perl modules :
ODF::lpod
OpenOffice::UNO
OpenOffice::OODoc

How much time does it take to re-implemented the built-in functionality?
And why?

[iplaw67]

Hi Andreas,

In a future step, I would like to integrate graphics into the
spreadsheet which could be created by a perl script with gnuplot from
data in the database; the images could reside in a directory and rows in
the database would have a field referring to the file name of the image.

Because all this has been implemented already to be used by anyone
without coding.
Hit F4 and drag your query into your favourite spreadsheet template.
Add spreadsheet formulas, (conditional) formatting, charts and stuff.
Finally, you may remove the "store data" flag from the import range so
the user is prompted to update the import range when opening this
document. All this works reliably out of the box.

Including the image link from the Gnuplot trace that the original poster
requested ? I think not, at least not without some other form of
programming magic to convert the filename to a URL and then insert it in
the sheet.

The GUI only solution you propose only works within the metes and bounds
of what the GUI can offer without programming. My understanding of the
original poster's question was that he was already considering a
programmable solution, at least for the images.

If you need programming within LO to get where you want, and you're
already using Perl for some of what you need, you might as well go to
whole hog, and do it all in Perl (or Python, or some other language that
lets you manipulate the document format).

Alex

[hw1]

Creating LO documents directly would be nice and might have the advantage to be able to specify some formatting --- which I might need to do sooner or later. Unfortunately, it seems to be rather tedious, and it's only one way just like CSV.

Perhaps I could use some sort of overlay showing data from a table as a spreadsheet which lets the user edit only some of the fields. Some of the content would need to be filled in "on the fly" from somewhere else because the perl script writes data and forumals into the CSV that aren't in the database. --- Or I'd have to create yet another table instead of the CSV.

Some experimentation is required at this point ... Thank you for all the replies so far!

[hw1]

Exactly: What the perl script does is way beyond the capabilities of a query and of formulas in a spread sheet.

Even letting aside that using the BASIC dialect of LO is very awkward (just think of the inability to return from a function ...) and that it doesn't even have a decent editor, LO is far too unreliable for anything like this.

[Villeroy]

Exactly: What the perl script does is way beyond the capabilities of a
query and of formulas in a spread sheet.

In which way? You are rather unspecific about your requirements.

Even letting aside that using the BASIC dialect of LO is very awkward
(just think of the inability to return from a function ...) and that it
doesn't even have a decent editor, LO is far too unreliable for anything
like this.

Basic can not return from a function? I'm no fan of that awkward and
almost extinct language of the 90ies but sometimes I use quick&dirty
Basic functions in spreadsheets or even Basic functions calling Python
functions.

[Villeroy]

Formatting is a matter of style, cell styles in this case. Linked import
ranges filled with database data can be prepared with cell styles (I use
document templates for this type of database reports). The formatting
expands/shrinks with the imported data range.

[hw1]

Exactly: What the perl script does is way beyond the capabilities of a
query and of formulas in a spread sheet.

In which way? You are rather unspecific about your requirements.

That's partly because the requirements are still evolving and not fixed yet. The perl script performs some calculations and updates data in various tables based on data from a number of other tables, sometimes intertwining multiple queries to achieve the desired results. It's non-trivial.

Even letting aside that using the BASIC dialect of LO is very awkward
(just think of the inability to return from a function ...) and that it
doesn't even have a decent editor, LO is far too unreliable for anything
like this.

Basic can not return from a function? I'm no fan of that awkward and
almost extinct language of the 90ies but sometimes I use quick&dirty
Basic functions in spreadsheets or even Basic functions calling Python
functions.

It's what you get when you write a macro to provide you with a function you can use in your spread sheets. I don't like it, either, and I'd gladly do it in perl instead.

Can I just point LO to the perl script that provides the required functions? I can't even do that with the built-in BASIC. Will it interpret the perl script?

[hw1]

Ok so I made a new spreadsheet and dragged a query from the datasources view into it. Apparently that fills rows in the spreadsheet with the data obtained from the query.

This can be useful and doesn't exactly seem to be what I would want in each case: What if the data in the underlying tables changes? Will the spreadsheet be updated automatically?

What if I edit the data in the spreadsheet? I suppose I cannot update the underlying tables through the query. So I pulled a table into the spreadsheet and after an hour or so, I had to kill LO because it didn't seem to do anything anymore.

[Villeroy]

Exactly: What the perl script does is way beyond the capabilities of a
query and of formulas in a spread sheet.

In which way? You are rather unspecific about your requirements.

That's partly because the requirements are still evolving and not fixed
yet. The perl script performs some calculations and updates data in
various tables based on data from a number of other tables, sometimes
intertwining multiple queries to achieve the desired results. It's
non-trivial.

Not trivial, but this is what SQL does so you simply link the result of
one or more SQL queries to a spreadsheet. Having the data in a
spreadsheet you may perform spreadsheet calculations record wise and you
can lookup values from other record sets.

Can I just point LO to the perl script that provides the required
functions? I can't even do that with the built-in BASIC. Will it
interpret the perl script?

No, but there are Python, JavaScript and Java as alternatives to the
Basic lingo. Just because this happens to be an office suite, nobody
forces you to work with Basic.

Being a Perl monger, why don't you dump all your results into csv files
and then link those files to a spreadsheet? (if it really has to be a
spreadsheet ... many users insist in using spreadsheets for no reason).

[Villeroy]

Some experimentation is required at this point ... Thank you for all
the replies so far!

Ok so I made a new spreadsheet and dragged a query from the datasources
view into it. Apparently that fills rows in the spreadsheet with the
data obtained from the query.

This can be useful and doesn't exactly seem to be what I would want in
each case: What if the data in the underlying tables changes? Will the
spreadsheet be updated automatically?

Click any single cell in that import range and call Data>Refresh
A one-line macro can update this on file open.
You can also set a flag to that import range to not store the data
within the spreadsheet. In this case you hit Enter when are prompted to
update the unsaved import data on file open.
You find this option under menu:Data>Define... pick your import range,
[More Options]
You can add a timer to the import range so it updates every x seconds.

[Tutorial] Using registered datasources in Calc:

https://forum.openoffice.org/en/forum/viewtopic.php?f=75&t=18511

What if I edit the data in the spreadsheet? I suppose I cannot update
the underlying tables through the query. So I pulled a table into the
spreadsheet and after an hour or so, I had to kill LO because it didn't
seem to do anything anymore.

Your edits will be lost on next refresh.
For database input you can add a true input form to this spreadsheet
document or any other office document (Writer, Calc Draw).
In some cases you may use the data source window for data entry into
updatable record sets.

[Tom_Davies1]

Hi
Ahh, i thought Writer, Calc etc could only look-up data and could not input
or edit data. I thought that was where the Base's internal Forms and
Reports were really useful?

So normal users could be kept in their safe familiar environment with no
fear of accidentally breaking anything much. Meanwhile progressively more
skilled/knowledgeable workers could easily be given more and more
abilities.

Regards from
Tom

[hw1]

How would I do something like this with CSVs? I guess I'd need some sort of "overlay spreadsheet" which defines the formatting and is then being filled with the data from a CSV file. The fields in the CSV remain the same while the number of rows will vary.

[Brian_Barker]

As suggested above, if you already had suitable styling configured as cell styles, it would be very simple to apply these styles to the data after it was positioned. Alternatively, if you have a document with space for the data already formatted - which would indeed sensibly be created from a template - you could add the data without upsetting the formatting.

The trick here is to use Edit | Paste Special... (or Ctrl+Shift+V) instead of ordinary Paste. If you paste from elsewhere in the same or another spreadsheet document, ensure that "Paste all" and Formats are both *not* ticked in the Paste Special dialogue; if you paste from another source, select "Unformatted text" in the Paste Special dialogue.

I trust this helps.

Brian Barker

[Villeroy]

Creating LO documents directly would be nice and might have the
advantage to be able to specify some formatting --- which I might need
to do sooner or later.

Formatting is a matter of style, cell styles in this case. Linked import
ranges filled with database data can be prepared with cell styles (I use
document templates for this type of database reports). The formatting
expands/shrinks with the imported data range.

How would I do something like this with CSVs? I guess I'd need some
sort of "overlay spreadsheet" which defines the formatting and is then
being filled with the data from a CSV file. The fields in the CSV
remain the same while the number of rows will vary.

Exactly like with any other database. For the formatted output of
database data does not matte if your Base document is connected to csv,
dBase, spreadsheets, Oracle server, MySQL, embedde HSQL or any kind of
ODBC data source.

[Example] Loading CSV into preformatted spreadsheets

https://forum.openoffice.org/en/forum/viewtopic.php?f=100&t=23727

File #1 is a csv file.
File #3 is a Base document connected to the csv file, more precisely to
its directory which may contain more csv files.
File #4 is a pre-formatted spreadsheet template with a preformatted
import range.

[hw1]

The data is in a CSV file. Opening the CSV creates a new spreadsheet. I don't want to apply formatting or copy and paste anything manually.

The formatting should be applied automatically, for example based on the name of the CSV file, using a regexp, when the CSV file is opened.

[Brian_Barker]

Creating LO documents directly would be nice and might have the advantage to be able to specify some formatting --- which I might need to do sooner or later.

Formatting is a matter of style, cell styles in this case. Linked import ranges filled with database data can be prepared with cell styles (I use document templates for this type of database reports). The formatting expands/shrinks with the imported data range.

How would I do something like this with CSVs? I guess I'd need some sort of "overlay spreadsheet" which defines the formatting and is then being filled with the data from a CSV file. The fields in the CSV remain the same while the number of rows will vary

As suggested above, if you already had suitable styling configured as cell styles, it would be very simple to apply these styles to the data after it was positioned. Alternatively, if you have a document with space for the data already formatted - which would indeed sensibly be created from a template - you could add the data without upsetting the formatting.

The trick here is to use Edit | Paste Special... (or Ctrl+Shift+V) instead of ordinary Paste. If you paste from elsewhere in the same or another spreadsheet document, ensure that "Paste all" and Formats are both *not* ticked in the Paste Special dialogue; if you paste from another source, select "Unformatted text" in the Paste Special dialogue.

The data is in a CSV file.

As you already indicated.

Opening the CSV creates a new spreadsheet.

Not necessarily: you can import a CSV file as a new sheet in an existing spreadsheet - and you can very simply copy and paste from there to wherever you want it.

I don't want to apply formatting ...

The previous suggestion - pasting as "Unformatted text" into a previously formatted sheet, probably derived from a template - avoids this. You will need to indicate what formatting you want at some point, of course, and you can easily do this by creating a template.

... or copy and paste anything manually. The formatting should be applied automatically, for example based on the name of the CSV file, using a regexp, when the CSV file is opened.

Then you may well need not Calc but SuperCalc. You could volunteer to help create it at www.iwanttohelpwritesupercalc.org . With luck it may be developed to read your mind as well.

Or you could employ an assistant.

Brian Barker