Welcome to WebmasterWorld Guest from 220.127.116.11
Forum Moderators: open
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:
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?
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.