How to Get Data from LibreOffice Base to MySQL on Windows

This is a slightly off topic post but I spent most of yesterday wrestling with LibreOffice Base (like OpenOffice) trying to get my research data out of it into a format I could generate reports from.  The Report Builder for which comes with LibreOffice isn't really much good unless you fancy learning LibreOffice BASIC, and I needed to be able to customise how my data looked.  The database is a collection of shot by shot comments and analysis for a few films I'm studying for my dissertation - notably the two Wim Wenders classics, "Paris, Texas" and "Kings of the Road".

I used Base because I could easily design a simple form and flick between records.  In retrospect I should have used MySQL and PHP from the start and coded all my forms in PHP.  After several failed attempt the way which I found which worked was.

1. Download WAMP server.  Make sure you get a version which has MySQL 5.1, not the latest 5.5.  You can simply download the latest WAMP if you wish, then click the icon in the system tray, choose 'MySQL' then 'Version', then get more.
2. Check the WAMP is fired up and running the right version:  click the icon in the system tray, choose 'MySQL' then 'Version', then 5.1.xx.
3. Install a MySQL to ODBC connector which speaks to version 5.1 (here).
4. Add a database to MySQL (however you wish, but the easy way is with phpMyAdmin from the WAMP system tray icon.  Make sure you set up the access permissions for a user name.
5. Add a connection to the database in the Data Sources list in the Windows Control Panel > Administrative Tools (more instructions here).  The port number is probably 3306 if you haven't touched anything.
6. Create a new database in LibreOffice Base which connects to the ODBC source by clicking File > New > Connect to an Existing Database > choose MySQL from the dropdown, then click Next.  Choose 'Connect using ODBC' click Next, then browse for the data source you just added in Data Sources in step 5.
7. Finally you can copy your table from your original LibreOffice Base file and paste it into the new file which is linked to MySQL via ODBC.  You'll probably have to convert some data types along the way and rename some fields if the names are too long or use forbidden characters.  LibreOffice will ask you about this if you need to do it.

That took me a good few hours to work out yesterday, so I hope that it helps anyone else who has entered a huge amount of data into LibreOffice Base only to discover they need to get it out again.  I couldn't export to text, or do the copy and paste into a LibreOffice Calc sheet as I had binary data which made the text file too large to be uploaded again into MySQL.

Now on with the research!