Importing Excel Sheets into MySQL with Values that relate to a separate table

First up, this might be the wrong place to ask this question.. So, sincere apologies for that!

I have two MySQL Tables as follows:

First Table: employee_data

id   name        开发者_StackOverflow中文版 address        phone_no

1    Mark         Some Street    647-981-1512

2    Adam         Some Street    647-981-1214

3    John         Some Street    647-981-1452

Second Table: employee_wages

id   employee_id         wages        start_date

1       3                $15          12 March 2007

2       1                $20          10 Oct 2008

3       2                $18          2 June 2006

I know, both these tables can be combined into one and there is no need to split this data into two tables. But, what i'm working on requires this data to be separate and in two different tables.

Now, previously my company used to handle all this data in Excel sheets and they followed the conventional method of having these two tables combined into one sheet as follows:

Excel Sheets

id   name           wages         start_date

1    Mark           $20           10 Oct 2008

2    Adam           $18           2 June 2006

3    John           $15           12 March 2007

Now, the objective is to Export the data from Excel sheets into MySQL Tables.

As you can notice employee_data.id is linked to employee_wages.employee_id

How can i replace the values in the Excel Sheet 'name' column so that they represent the actual unique ID they're given in the employee_data.id column..

May be i can do it with PHP/MySQL or i can get this done in VB Script.. BUt, I'm not an expert in VB Script..

Any help will be much appreciated..

Thanks!

Save your Excel data as a csv file (In Excel 2007 using Save As)

Check the saved file using a text editor such as Notepad to see what it actually looks like, i.e. what delimiter was used etc.

Start the MySQL Command Prompt (I’m lazy so I usually do this from the MySQL Query Browser – Tools – MySQL Command Line Client to avoid having to enter username and password etc.)

Enter this command:

LOAD DATA LOCAL INFILE ‘C:\\temp\\yourfile.csv’ INTO TABLE database.table FIELDS TERMINATED BY ‘;’ ENCLOSED BY ‘”‘ LINES TERMINATED BY ‘\r\n’ (field1, field2);

[Edit: Make sure to check your single quotes (') and double quotes (") if you copy and paste this code - it seems WordPress is changing them into some similar but different characters] Done!

use the vlookup function in excel.

1. import the data into excel
2. use vlookup to combine what you need

You can query MySQL from Excel, this example uses INSERT: Excel VBA: writing to mysql database

A recordset can be written to Excel with CopyRecordset : http://support.microsoft.com/kb/246335