Apache OpenOffice Community Forum

Hello

I think it is not a good idea to go to the details of a script language or the API. It is more important to learn how you can use OpenOffice without any difficult script. When you are working with a database then it is important to understand what the database engine you use can do for you. Villeroy give wonderful information. When you read each day the post of Villeroy you will learn how to use OOo.

Romke

RPG wrote:When you read each day the post of Villeroy you will learn how to use OOo.

Wise advice, Romke. Villeroy has already given me sound advice in this forum, as have you. This is a great resource and your time is appreciated.

Most developers write data into a PostgreSQL database without using any office suite. This office suite makes everything far more complex than it needs to be. If you need to store keyboard input into your database, Open/LibreOffice can be useful but then you should simply connect a Base document to your database and create an input form.

Villeroy wrote:Most developers write data into a PostgreSQL database without using any office suite.

Believe me, if I could start from scratch I would use something better than spreadsheets.

Unfortunately for me, I have inherited a club financial recording system written entirely using MS Excel spreadsheets. Gak!

I need to maintain the existing spreadsheet-based system and over time build underneath it with something rather more intelligent using a relational database: I have chosen PostgreSQL. As I have to use LibreOffice (which I like anyway) to operate the spreadsheets, I am building forms and writing code allowing me to update the spreadsheets and simultaneously update the new database.

Awful. Messy. Bad. Bigly. Yet it is what I am dealing with. The spreadsheets have never been subject to data validation and it is only due to the typing skills of successive treasurers that any results at all have been presented to the AGMs!

I appreciate your sage advice as always, Villeroy. "8-)

But then I would write the database application with some test data while the old Excel sheets keep on running -- with MS Excel of course.
The transfer of valid spreadsheet data into a ready made database structure can be done by copy&paste after some preparation directly on spreadsheets. A spreadsheet is an appropriate visual tool for this task because you can freely move, transpose, validate, lookup and convert things until they fit exactly into the relational database structure.
On the sheets, you have to lookup the right primary key values for the entered sheet values in order to populate foreign keys with valid data.
Then you have to dump the detail tables with primary keys first before dumping the dependent tables with related foreign keys to avoid integrity errors.

Copy and paste from Calc to Base works with all simple data types: text, numbers, booleans and date/times without time zone info.
You copy a whole cell range which contains a normalized table, select the icon of an existing database table in the Base window and paste. A dialog pops up where you can check an option wether the clipboard data include a row of column headers or not. In a second step you have to map the incoming columns to the existing columns of the database table unless you arranged the column order in the spreadsheet.

Villeroy wrote:But then I would write the database application with some test data while the old Excel sheets keep on running ...

All good advice Villeroy. In broad I am doing as you suggest, building the new system in parallel with maintaining the old. In fine there are differences, in that I am using code rather than cut-and-paste to draw data from the spreadsheets into PostgreSQL. The problems of normalisation and validation is the same regardless of how I transfer the data.

Useful exercise for the little grey cells. "8-)