Loading CSV data from Excel into MySQL on a Mac

Loading data from a CSV file into MySQL requires that your file has the same number of columns in the same order as the MySQL table.

That means that you need to populate any column with an auto incrementing primary key, as well as any timestamp column that might be generated by your application. This is particularly relevant if you want to populate a tables used in a Rails web application.

1: Build your table in Excel, adding a primary key column if necessary and populating it using the Edit->Fill->Series... feature in Excel.

2: Save it in CSV (Comma delimited) format - don't bother with the CSV Windows, etc options. Don't worry about Excel's warnings about losing formatting (unless of course you have important formatting...)

3: Optionally load it into an editor like TextMate (turn on 'show invisibles') and do any fine tuning you might want or need to do.

4: Run the Mysql client and from the mysql> prompt load in the data using a command like this:

mysql> load data local infile 'products.csv' into table products fields terminated by ',' lines terminated by '\r';

I needed the '\r' line termination loading the file from Mac OS X. It always takes me a couple of tries before I get the right \n and/or \r combination...