[Solved] Moving from spreadsheet to MySQL

Discuss the database features
Post Reply
User avatar
fustbariclation
Posts: 16
Joined: Thu Jan 27, 2011 11:59 am

[Solved] Moving from spreadsheet to MySQL

Post by fustbariclation »

I've got all my data in a spreadsheet. It imports nicely into openoffice, but, quite reasonably, it doesn't support relations.

I've got one table from each sheet in the spreadsheet, which is great. Each links by the same key, as it happens.

Now I'd like to convert this lot to mysql (jdbc or anything else really) so that these can work as relations.

I'm obviously not understanding something as I wanted to use the spreadsheet as the initial source of the data, but then have it as an openoffice .odb database. It seems, though, to be kept as a spreadsheet with only information in the .odb database.

It may help to say what I'm actually wanting to do:

- One table contains all the definitions. I'd like to set up a form for this, so I can enter all the definitions. Then, for each ID, I'd like to enter the relevant data, through a form, into the other tables.
Last edited by Hagar Delest on Thu Mar 03, 2011 9:04 am, edited 1 time in total.
Reason: tagged [Solved].
Arineckaig
Volunteer
Posts: 828
Joined: Fri Nov 30, 2007 10:58 am
Location: Scotland

Re: Simple question - moving from spreadsheet to mysql

Post by Arineckaig »

I've got one table from each sheet in the spreadsheet, which is great. Each links by the same key, as it happens.
Can you clarify "each links by the same key"? What are the nature of the links: does it mean the primary key for each sheet/table holds a value that is the same for each matching row/record?
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Moving from spreadsheet to MySQL

Post by Villeroy »

0. Create a draft of tables, field types and relations. Pencil & paper will suffice as development tool.
1. Create your MySQL database. Do NOT use OOo for this task. There are plenty of mature development tools for MySQL.
2. Connect OOo to the MySQL database. This will create a so called "database document".
3. Before you populate your database with spreadsheet data, the spreadsheet data have to fulfill the rules of your database regarding referencial integrity, field types and null values. I can provide a small set of formulas: http://user.services.openoffice.org/en/ ... 78#p169878
4. Copy the spreadsheet ranges onto the table icons in your database window. The order of fields, field labels or superfluous fields don't matter. You can easily map the fields to each other. What matters is the validity and consistency of field data. Therefore you have to import the tables in a given order. The one-side of a relation first, then the dependent n-side of a relation. You may also release the relations, import in any order and then rebuild the relation.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
fustbariclation
Posts: 16
Joined: Thu Jan 27, 2011 11:59 am

Re: Moving from spreadsheet to MySQL

Post by fustbariclation »

Brilliant - thank you! That makes a lot of sense.

I've got all the tables, fields and relations sorted out, so it'll be easy with mySQL.
OpenOffice 3.3 on Mac OS/X 10.6.6 - 2.66GHz - 4Gb Memory - 1TB disc - ~80Gb free
Post Reply