Welcome to WebmasterWorld Guest from

Forum Moderators: open

Message Too Old, No Replies

Exporting Foreign keys from Excel to MySQL

11:32 pm on Feb 14, 2007 (gmt 0)

Full Member

10+ Year Member

joined:Aug 18, 2003
votes: 0

Exporting from Excel to MySQL
It looks quite straight forward to export fields from Excel into fields in the one table of a MySQL database (by maybe using a converter tool or a simple PHP script).

However my knowledge is lacking as to the best approach to export from Excel to MySQL when the target is more than one MySQL table (i.e. the MySQL table relationship utilises foreign keys).

I'll give you an example of the column headers of my excel spreadsheet.

firstname surname telephonenumber town

then I would like to export the data underneath these headings into a mysql database (below):

Here's an example of the MySQL tables:

firstname (Dave)
surname (Smith)
telephonenumber (01234322333)
town (3)

townID (3)
town (London)

The problem here is that the data in the excel file will include the word 'London' but will not include the townID. Assuming that the Towns mysql table is already populated, would the best approach be to put some kind of dropdown field in the excel town field where the value is the TownID. I suppose the data that needs to populate this Excel drop-down field could come from an initial query to the MySQL database?

Are they any MySQL users out there who appreciate what I'm trying to do and can lend some advice? Should I try and produce a PHP script or can anyone recommend a tool that would do this, or a script that has already been written?

4:53 pm on Feb 21, 2007 (gmt 0)


WebmasterWorld Administrator coopster is a WebmasterWorld Top Contributor of All Time 10+ Year Member

joined:July 31, 2003
votes: 0

There are quite a few approaches you could take. One might be to create a temporary table first which has the same layout as the people table except that it has the town name as text, or even add the text version to the table:

firstname (Dave)
surname (Smith)
telephonenumber (01234322333)
town (3)
towntext (London)

Populate the "town" column with a NULL value as you import your data and put the town name into the "towntext" column. Then, after you have populated the TownsTable you could run an UPDATE statement over your PeopleTable to fill in the town column. Then ALTER TABLE PeopleTable to drop the towntext column.

This is just one approach, there are many ways to accomplish the task.


Join The Conversation

Moderators and Top Contributors

Hot Threads This Week

Featured Threads

Free SEO Tools

Hire Expert Members