Loading Microsoft Excel data into MySQL

Posted by joshb

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

Set dates to format YYYY-MM-DD
Put columns in the same order as the database
Save data as tab delimited
Use BBedit (or another program) to convert line breaks to UNIX (^n)
Put file in directory with no spaces in the path
Launch MySQL
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.

Average:

Your rating: None

Tags:

Comments

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

Anonymous | Sep 8th, 2003 at 3:36 pm

Your name: *

E-mail: *

The content of this field is kept private and will not be shown publicly.

Homepage:

Subject:

Comment: *

Link to Amazon products with: [amazon product_id inline|full|thumbnail]. Example: [amazon 1590597559 thumbnail]
You may post code using <code>...</code> (generic) or <?php ... ?> (highlighted PHP) tags.
You may insert videos with [video:URL]
Use [fn]...[/fn] (or <fn>...</fn>) to insert automatically numbered footnotes.
Allowed HTML tags: <a> <b> <dd> <dl> <dt> <i> <li> <ol> <u> <ul><p> <img> <table> <tr> <td><strong><em><sup><div><fn><h1><h2><h3><h4><blockquote><img style="">
Lines and paragraphs break automatically.
Web page addresses and e-mail addresses turn into links automatically. (Better URL filter.)