How do I use LO Calc as form to enter data into a database?

Can I use LO Calc columns to append data to a database?
The database is MySql. Is it possible to do this?
If not with MySql I could also use LO Base

Yes, you can, using drag and drop (or copy/paste) of selected rows from the Calc sheet to an open ODB file configured to connect to your mysql database and containing the table to which you want to append the data. If your data is already in CSV format, you can import that directly into mysql without ever having to open LibreOffice using the mysql command line (or a graphical tool such as phpmyadmin or something similar).

Hello,

First starting with the database, Base is not a database. Base is a front end to databases. LO does include, currently, HSQLDB embedded v1.8 as the default DB but you can certainly use a variety of other DB’s including MySQL. For further info on this, please see LO documentation found here → LibreOffice Base Handbook - Chapter 2 Creating a Database.

You can copy & append data from Calc to a DB table (see my answer in this post → can i enter Calc column into Base to create form records), possibly use macros to pass data to the DB or other methods. It all depends upon your expertise and procedures. With a registered Base file you can access data from the database but actually updating the database directly from Calc is more difficult.

1 Like

Your answer was certainly helpful, but not with respect to the solution I am looking for. I have to update the spreadsheet daily about 200-300 rows and 2-4 columns. Within a month Calc starts lagging due to the data accumulated. So I was wondering if there was a way to just enter the daily column in the spreadsheet and to automatically update the database and clear the spreadsheet simultaneously and automatically.

As stated in the answer - write your own code (macros). Set up MySQL with Base, then code in Calc to access the database. Process what you want - update table(s) from sheet & then delete what is no longer wanted. For the best guide on coding macros, see the PDF book by Andrew Pitonyak → Open Office Macros Explained.

There is no general purpose code for this. It must fit your specific needs.

1 Like

I am still unclear whether you ever need the info to be in the spreadsheet at all.

With this volume of data entry I would want to be doing it in a base form. It is easier to code in sanity checks, and each row is committed as the next is entered, meaning that power outages etc will only lose you the row you were on at the time.

So I would endorse the previous suggestion by JimK to do the data entry direct to Base

@Ratslinger described how to do specifically what you asked. However, why not create a form in Base and enter the data there? That would require little or no macro programming, and is likely to be faster.

To get started, create a Base file that connects to MySQL. Then go to the Forms section and create a new form. Table controls are grids, similar to Calc spreadsheets.

You’ll probably need to read some documentation to get it right, such as the link suggested by @Ratslinger, or search the many answers on this site that deal with Base forms.

The Frugal Computer Guy has some great video tutorials on using Base that might help http://thefrugalcomputerguy.com/libreoffice-base/index.php. He uses the default HSQLDB.

Video 3 describes importing data from a spreadsheet into an existing table. You select and copy the source data, then paste it into the database table where you want it. It’s pretty simple and it works. I do it.

Video 4 describes importing data from a spreadsheet and using this to create a new table. But it sounds like you already have an existing database and table, in which case you would need to follow his instructions from video 3.

You may wish to try this procedure out first with a dummy database and just a few lines of copied data to make sure everything imports correctly and into the correct fields. It took me a few tries to get it right. I kept having to delete what I had imported and fool around with the formatting and the Paste dialog options.

Once you get it, though, it’s easy. I just wouldn’t try it on an existing database or table until I was sure I had everything importing correctly.

None of this will auto-delete your spreadsheet rows. But since you have to select, copy, and paste them, they remain selected in your original spreadsheet. Once you’re sure they’re in your database, simply click back over to your spreadsheet and hit Delete.

Working with MySQL inside Base may be different enough that none of this applies. If so, my apologies, and please ignore this answser.

If it did solve your problem, please click the big checkmark inside the circle to the left of this answer.