Loading Microsoft Excel data into MySQL

This is mainly a cheat sheet for me in recalling the best way to load MS Excel data into MySQL.

  1. Set dates to format YYYY-MM-DD
  2. Put columns in the same order as the database
  3. Save data as tab delimited
  4. Use BBedit (or another program) to convert line breaks to UNIX (^n)
  5. Put file in directory with no spaces in the path
  6. Launch MySQL
  7. Load data with command "load data infile '/PATH/FILE' into table `TABLE_NAME` LINES TERMINATED BY '\n';"

This is not comprehensive nor does it deal with all situations. Just a place holder for the information.


1 Comment

Don't forget to check dates

If bringing the file from Excel for Windows there may be a 4-year date discrepancy.