Re: [libreoffice-users] MySQL - LO Base question.

On 24/01/2015, at 6:13, Ian Whitfield <whitfield@telkomsa.net> wrote:

> 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??
> 2) Set-up the Database in MySQL then use LO Base to create the table and do the import

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



-- 
To unsubscribe e-mail to: users+unsubscribe@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted