Fastest way to populate a Db

Question

I have an excel spreadsheet with products and categories with a many to many relationship and i need to populate the db with it. I am searching for a quick way to enter this data in and have the join table have all the correct info. I have phpmyadmin but it seems to be a bit slow unless there is a shortcut. Another approach i was thinking was writing some queries that I could copy and paste but getting the join table to be correct might be more challenging. Unless someone can think of a query that i can create the product and catagory and have the join connection.

PRODUCTS

* PRODUCT_ID (primary key)
* PRODUCT_NAME

CATEGORIES

* CATEGORY_ID (primary key)
* CATEGORY_NAME

PRODUCT_CATEGORIES_MAP

* PRODUCT_ID (primary key, foreign key to PRODUCTS)
* CATEGORY_ID (primary key, foreign key to CATEGORIES)
* QUANTITY

Answer 1

Most databases have some sort of optimized bulk insertion that is faster/less tedious than using INSERT statements - for MySQL, it's the LOAD DATA INFILE syntax.

Coming from a spreadsheet, it's easy to generate CSV ouptut so you can use:

LOAD DATA INFILE 'data.txt' INTO TABLE your_table
   FIELDS TERMINATED BY ',' ENCLOSED BY '"'
   LINES TERMINATED BY '\r\n'
   IGNORE 1 LINES;

Answer 2

Export them as 3 CSV files and then use LOAD DATA INFILE to load them into the server.

Answer 3

If you have to do this a lot (like I do), consider a using a database client software package like SquirrelSQL, or maybe write yourself a very simple PERL program that uses an Excel reader and DBI.