splitting excel data in to multiple mysql tables

Question

Basically I have a export from an old system with timesheets in it.

Each line to simplify it has an employee name, description and time.

I have thousands of these in excel rows of timesheets in excel. I want to pop these into two tables

Employee and Timesheet

Naturally the employee name would repeat many times but I want to have unique employees only in the employee table e.g. 30 employees but 10,000 timesheets

This is a one off task, I've PHP at my disposal if need but I recon there is probably a smart easy way to do this.

I have a few ideas of my own but I'd be interested to know what way you would do this.

Note: there is actually alot more complexity in the amount of tables but by solving the above example effectively it solves it for the more complex example where clients, projects, tasks, etc are all included in each row.

Answer 1

I almost always solve this type of (one off) problem with CONCATENATE in excel and build a .sql command file. In the columns after the data i build some of these...

=CONCATENATE("insert into tableA (col1,col2,col3) values ('",A3,"','",A4,"','",A5,"');")

copy/paste the statements to a text file and save as mysqlfile.sql (or whatever). Then insert into your db (provided the database tables already exist)

mysql -umyname -p -hmyhost.com mydatabasename < mysqlfile.sql

it's kinda hacky, but it works quickly for this type of thing.

Answer 2

Good old copy/paste/macro. I usually use EditPlus for this sort of jobs. It records macros keystrokes to a shortcut key like Alt-1. So I just write copy/paste everything into editplus and record a "REPLACE INTO ..." for the first line and repeat it for the rest of the file. The paste it as a query. Works unless you have millions of lines.

Notice: REPLACE. Define your table with employee as a key and each employee will be unique and there'll be no errors.