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.

MySQL to MySQL Sync

I'm looking for good info on synchronizing two different MySQL databases. The table structure is the same. Updates are limited by userID - i.e. the owner of a record is the only one who can update the record but any user can query on multiple the fields. There is a discussion on DevShed about a year ago on the topic. Would there be synergy that could be had with Userland's xmlStorageSystem?


Subscribe to MySQL