homepage Welcome to WebmasterWorld Guest from
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

Exporting Foreign keys from Excel to MySQL
The Cricketer

10+ Year Member

Msg#: 3252909 posted 11:32 pm on Feb 14, 2007 (gmt 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?



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

Msg#: 3252909 posted 4:53 pm on Feb 21, 2007 (gmt 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.

Global Options:
 top home search open messages active posts  

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved