MySQL - LO Base question.

users
#1

Hi All

I have been running a MySQL Database with LO Base as a front-end for some time now -
and VERY successfully indeed compared to the built-in engine in LO Base!!
(My OpSys is PClinuxOS 32-bit)

For a variety of reasons I decided to "rebuild" my system last week. I have a back-up of my Database as a CSV file and have completely wiped the hard drive and reinstalled the OpSys and all relevant programs and copied all my data into /home.

I now need to set-up my Database again but have a question.
(My Database has only one table with about 2500 records and each record has 80 fields.)

Which is the correct procedure??

1) Set-up the Database in MySQL and create an empty table to import the CSV into. Then link Base to it.
2) Set-up the Database in MySQL then use LO Base to create the table and do the import
3) Set-up the Database using LO Base and then import the data.

Thanks for any pointers.

IanW
Pretoria RSA

#2

I would suggest option 2.

Sorry about the long post, but I hope this helps.

Login to MySQL as normal
In MySQL create an EMPTY database mysql> create database newname ; or reuse your old database name.
Quit MySQL \q

Take your CSV file and open it in LibreOffice calc.
Save the spreadsheet

Open new database in LibreOffice Base

Select "connect to an Existing Database"
Select "MySQL"
on the next screen
Select the driver (I use JDBC)
on the next screen
Enter the name of the database you created in MySQL
Enter the server, probably "Localhost" or "127.0.0.1"
Accept the default Port
on the next screen
Enter your MySQL logon name
Tick "Password required"
on the Final screen
Select "Yes register the database"
Click Finish

Now LibreOffice will ask for a new database name -- this is the name of the Base Front End you will be using to connect to the MySQL server (it is NOT a database!).
When LibreOffice creates the new front end it should open with a blank set of tasks.
Open the spreadsheet you created earlier.
Select ALL the data including column headings
Copy this data
Go to your front end
Make sure that "Tables" are selected in the list of tasks
Click in the blank area below the Options panel
Paste your data!!
LibreOffice will ask you to confirm that you want to add the definition & data, and that the first row is column headings
Click OK
On the next screen you need to select the columns (or fields) that you want to import. Just select all.
Click "Create"
LibreOffice should, all being well, create a new table with all your data in the correct columns.
You may be asked if you want to add a primary key. I suggest that you say yes. You can always change this later but it saves problems when inserting a pasted set of data.

You should now have a new database with all your original data as previously.
You may have to do some "tidying up" if the data types did not import correctly, such as Dates.

Sorry about the long post, but I hope this helps.

Tony Bray
tonybsa@mac.com
MacBook Pro 15 inch Mid 2009
2.8 GHz Intel Core 2 Duo, 4 GB RAM,
Mac OS X 10.9.5.
Ubuntu 14.04 on a Acer PC 4GB RAM
LibreOffice 4.2.6 & 4.3