Calc as a Flat File Database

users
#1

Hi All

I was thinking the other day about using Calc as a Flat File Database - ie with no relationships.

Many users make use of a Spreadsheet as a kind of Database but the problem is viewing the data.
So I was wondering if there is and Add-On or Extension available to give you a "Form" to overlay the Spreadsheet so that you can view the data one row at a time??

Or if not would it be possible to achieve this using Macros??

I would be most interested in any suggestions or input.

Thanks

IanW
Pretoria RSA

#2

Hello Ian - I don't know the particulars but can say that the Form...
option in the Data menu works for me for a simple test file.

I created a spreadsheet and put column titles in row A then entered several
rows of data. Selecting Data>Form with any cell within the boundaries of
the column labels and data already selected brought up the form.

If the selected cell was outside the range then the form was empty.

There was no need to name the data range. Simply entered column titles and
rows of data and it worked.

#3

Thanks Dan and Alan

I know about the Base > Calc link but I have been using Base since about 2010 and had many, many crashes where in most cases the crash takes out the data as well and you need to spend hours and hours re-building the Database and re-designing your Forms etc. This has happened repeatedly with the embedded engine and also with the MySQL engine.
As far as I'm concerned there is basic unstability in Base itself which makes it unusable on a day-to-day basis!!
This was why I was wanting to try another route.
I tried Kexi but it is still to early in its development to be reliable.
I wanted to try Glom but it is unavailable on my OpSys (PClinuxOS).
So I was hoping to try a simpler option of Calc plus a Form.
Thanks for pointing out to me the 'Form' option under Data - I had not noticed that.
Unfortunately it is not very useable for me which is a great pity as this would be the perfect answer if it was.

Thanks for the pointers - my search continues..........
Any suggestions of a reliable Flat File Database would be of interest to me.

IanW
Pretoria RSA

#4

Hi Ian,

Database and re-designing your Forms etc. This has happened repeatedly
with the embedded engine and also with the MySQL engine.

I'm sorry, but crashing and subsequent corruption of an ODB file that
links to a mysql database does not lead to dataloss of the linked mysql
db, or at least it shouldn't if you know how to manage your mysql db. At
worst, you can mess up your forms, lose your query definitions and
report setups, but that's about it. Yes, with embedded hsqldb, dataloss
is a known problem, but not with an external database.

As far as I'm concerned there is basic unstability in Base itself which
makes it unusable on a day-to-day basis!!
This was why I was wanting to try another route.

I tried Kexi but it is still to early in its development to be reliable.
I wanted to try Glom but it is unavailable on my OpSys (PClinuxOS).
So I was hoping to try a simpler option of Calc plus a Form.

Why not separate out your forms from the ODB file if you are concerned
about the stability of the ODB file itself ? It is perfectly possible,
you can still link to a mysql db and you won't have the issues of
screwing up your ODB just because the power goes off, or your network
goes down, or some other life-event that seems to befall computer users
on a daily basis.

You could look at Dabo (a python based UI interface to mysql dbs), or
webforms using Ruby, Rails, or PHP. They all require some kind of effort
to obtain a "usable" solution. Even the MySQL UI admin tool has a simple
Form view mode, where you can enter data in what looks like a form -
sophisticated it isn't, but it works !

Alex

#5

Hi Alex

Why not separate out your forms from the ODB file if you are concerned
about the stability of the ODB file itself ? It is perfectly possible,
you can still link to a mysql db and you won't have the issues of
screwing up your ODB just because the power goes off, or your network
goes down, or some other life-event that seems to befall computer users
on a daily basis.

I wrote to you directly but it seems you don't like direct contact this way - sorry about that.

I have sort off given-up on trying to find an alternative to Base. Kexi is too "young" and still has many problems, Glom is not available on my OpSys, (and seems very difficult to get working on Linux in any case), there seems to be no GUI Front-end to MySQL that does what I need so I'm now looking at your idea as mentioned above.

However I need some help with this please. I've looked inside an ODB file and it seems very complicated so wonder if you can assist with details of which files to save and move to preserve my Forms, (and Queries??).

I would greatly appreciate any assistance with this - thanks

IanW
Pretoria RSA

#6

​Ian,
Regarding Kexi, to be honest, I found out that most of your requests
actually wishes, not bugs. So you received a 100% free support. Together
after discussions we eventually found out that there is _zero_ data loss.
If you remove database files or remove records from tables, that's not data
loss, that is evident user's action.

All your request are noted if there are actually understood: please excuse
me but report of this kind (
https://forum.kde.org/viewtopic.php?f=221&t=131466) is rather hard to
decipher. Let's be more systematic so also other users can follow the
progress.

It also would help when users don't move from one solution/idea to another
and from topic to topic even in a single forum thread. Like here, jumping
to Calc or even calling ODS a flat file. Experimentation is encourages in
FOSS but if it's not part of the core offering, it's often more like user's
risk.

An idea on the back of my head is that (with support from users) eventually
major FOSS products interoperate, be it Kexi launched in 2003 or its
younger brother Base.

Please note that in our development model you're co-author of the software
so the responsibility to work efficiently is shared, well, then the fun is
shared too :slight_smile:
​

#7

Hi Jaroslaw,

It seems that Ian wants a solution that doesn't require too much effort
and just works, at least in the way that he would like. In that case,
Base, and from your own answer, Kexi, will be unsuited to that task.
Defining the metes and bounds of one's expectations with free software,
and then expecting these things just to happen, as we all know,
generally leads to disappointment.

Keep up the good work with Kexi !

Alex

#8

Hi Ian,

I wrote to you directly but it seems you don't like direct contact this
way - sorry about that.

Yes, that's not my thing, sorry, but I only contribute to this list when
I think it is worthwhile, or I'm having a rant :wink:

there seems to be no GUI Front-end to MySQL that does what I need so I'm
now looking at your idea as mentioned above.

You really need to define your requirements first, look into what each
potential solution can do, and then decide what's best for you, rather
than moving organically, as that is pretty guaranteed to hit a rock one day.

There are plenty of GUI front-ends to mysql, seemingly just none that
fit your requirements. I am someone who doesn't really need forms in all
their GUI splendor to do their database work. I know there are several
ways of producing a form to interface with a mysql server, one of them
being a web form. I also know that I can choose a myriad of languages to
program that form and get it to interact with the user in a way that
would be acceptable. However, all of that takes time and effort, in
particular, I would have to learn to program the web server in one or
more of those myriad languages - all of this doable, if one takes the
time and puts in the effort. The alternative is to take an off-the-shelf
solution, and accept its limitations. The expression "there is no such
thing as a free lunch", comes to mind - it might come pretty close, but
it would be pretty rare to find an open source, free software product
that does everything anyone could ever need (one could probably say the
same for commercial software too).

However I need some help with this please. I've looked inside an ODB
file and it seems very complicated so wonder if you can assist with
details of which files to save and move to preserve my Forms, (and
Queries??).

Open your Base file in LO.
Open any one of the forms, e.g. by double-clicking on it.
Now go to "File - Save copy as" and save the form in an appropriate
place on your hard disk/USB key, etc.

The form has now been saved outside of the ODB. In order to be able to
use it with the data from your mysql datasource, you will have to load
the form (it is generally a Writer ODT file) into LO and then enter Form
Design Mode (via the form control toolbar), activating form design mode,
choosing Form properties, and then re-link to a datasource that has been
registered as such within LO (in this case the mysql datasource) via the
Data tab of the Form properties dialog.

Alex

#9

You could plausibly also design your form from the ground up using a
Calc sheet, inserting form controls onto it, and linking that form to
your datasource, and then binding your form controls to the data fields.

Alex

#10

Thanks all - appreciated!!

My background is 30 years in the Computer Business - Training, installations, support etc etc.
Since retiring I left Windows behind and changed to Linux.
What I'm not is a Programmer - except with BASIC about 20 years ago!!!

My needs are to have a SOLID, RELIABLE simple Members Database that does not crash all the time and take the data with it as well.
I don't even need a RDB as I have one table, one Form and a few queries, it is single user on a single machine. My Form only uses Text Boxes, Drop-Down Boxes, Check Boxes and Photos and Graphics. End of story!! i don't want to become a DB expert just a basic layout person and every day user.

I did not think this would be a big problem in Linux - how wrong I was. I have battled with Base for several years and after my last crash in January, (Data lost, as is common, even with MySQL. The file is still there but it can not be accessed).

I thank Jarasow for his help with Kexi but after 10 crashes in 2 weeks and some missing features and no help to try and get Drop-boxes to work I had to give up on it. It does look very promising but has a way to go yet I fear.

So Alexander has told me how to save my Form (that helps a lot) and I decided to get back into Base as it seems to be the only way to go for me. But MySQL is giving me up-hill. It does not seem to want to save an empty Table for me to import my CSV into and gives me this error ...

"#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'varchar(10), `NULL` varchar(10), `NULL` varchar(10), `NULL` varchar(21), `NULL` ' at line 1"

I seem to remember this from when I first set it up last year. It's not MY SQL - this is what the program did!! Also my first line of the CSV is the column names only and it works OK with Calc and did so as well with Kexi!! So where is the problem??!!
So I'm unable to populate the DB in MySQL for Base to link to it!!

Thanks all.

#11

Hi Ian and all:

  Excuse me if I understood bad your problem.

  I understood that you need import a .CSV file to BASE with BASE. And
You can't do it.

  I suggest to you to open the .CSV file with CALC. Then save it as .ODS.
Finally, import from BASE the .ODS file.

  I hope this help.

Regards,

Jorge Rodríguez

#12

Hi Jorge,

  I suggest to you to open the .CSV file with CALC. Then save it as .ODS.
Finally, import from BASE the .ODS file.

The import wizard from Calc to Base is not perfect, so unless one is
absolutely sure that the data in your CSV or Calc file exactly matches
the definition of the fields in the table, then one should expect either
import errors or failure. Particular things to watch out for :

- date / time / datetime representations
- text values over integers
- null fields
- values intended to be used in an autoincrement field
- boolean values
- memo fields
- unsigned INT field values
- zero-fill values

etc, etc

Alex

#13

Hi Alexander and all:

  Thank you for your clear explanation. If I understood you well, this
happen when we predefined the table in Base that we think that we will
need. But when I have needed do it, I didn't make the table, I only use
connect option (when start BASE), with a .db (spreadsheet -.ods-) that I
have in my file directory. I think that in this way BASE create the
table with the characteristic of the origin (.ods file).

Regards,

Jorge Rodríguez

#14

Hi Alexander

So Alexander has told me how to save my Form (that helps a lot) and I
>decided to get back into Base as it seems to be the only way to go for
>me. But MySQL is giving me up-hill. It does not seem to want to save an
>empty Table for me to import my CSV into and gives me this error ...

Again, I don't understand why you are attempting to use LO and a CSV
file to import your data into your mysql database.

I have managed - after a 2 day battle - to get all my CSV data back into MySQL as a Table.
I needed to do this as that was the only source of my original data.
I have also successfully linked Base to this Table. And can view it all in Base.

But I now have the problem of bringing in the Form you told me how to save.
I have it as an ODT file but can not find a way of coping it into Base!!

Can you assist me with this please??

Thanks a lot.

#15

Hi Ian,

But I now have the problem of bringing in the Form you told me how to save.
I have it as an ODT file but can not find a way of coping it into Base!!

Can you assist me with this please??

The whole point of saving the form as an ODT outside of an ODB is that
it becomes standalone, providing you link it to the declared datasource
as I mentioned in my previous mail. You can not simply slide the ODT
file back into the ODB file - why would you want to do that if your
original aim was to get the form out ? If you create the form in the
ODB, it stays in the ODB, you don't have to bring it in again.

I, imagine, although I have never tried this, that it would
theoretically be possible to unzip the ODB file and copy the contents of
an equally unzipped ODT file into the appropriate subfolder (e.g.
/forms/Obj11/) within the unzipped ODB, re-zip it again, and thereby
access it anew from within the ODB. When I look in the forms folder of
an ODB file, I see a subfolder called ObjXX, where XX are digits. In
this Obj folder I see 4 files, plus another subfolder /Configurations2.

The 4 files of the ObjXX folder are :
content.xml
manifest.rdf
settings.xml
styles.xml

These files are required to represent the form's content, style and
settings, and you can probably find them in your ODT file once unzipped.
The Configurations2 subfolder should be optional, but from what I
understand it is sometimes required, depending on the options that
appear in settings.xml. The Configurations2 subfolder contains all of
the xml files for various UI parts of the UI space that a form can use.

Alex

#16

Hi Ian,

I think what you are wanting to discover is how to open your Form - the .odt
file.

One possibility would be to right click on the .odt file, then choose Send
to | Desktop (create shortcut).
After that, simply clicking on the shortcut on the Desktop will open the .
odt file. No doubt to get the Form working I would expect that you need to
also have Base running before you click on the shortcut - you may need to
experiment.

I tried it on one of my databases. The .odt file opened OK, but I did not
do the work to connect it back to the database, (or to my macros), so it
didn't do anything. But if you have connected your .odt file to the
database, then I think it would work. It's worth a try, if this is what
you want to happen.

Regards,

Noel

#17

Calc menu:Data>Form...

https://forum.openoffice.org/en/forum/viewtopic.php?f=21&t=77543 includes a
macro to extract and reconnect all forms from a database document.
Background: https://forum.openoffice.org/en/forum/viewtopic.php?f=83&t=40493