Import Excel Data into MySQL in 5 Easy Steps

To import data from Excel (or any other program that can produce a text file) is very simple using the LOAD DATA command from the MySQL Command prompt.

  1. Save your Excel data as a csv file (In Excel 2007 using Save As)
  2. Check the saved file using a text editor such as Notepad to see what it actually looks like, i.e. what delimiter was used etc.
  3. Start the MySQL Command Prompt (I’m lazy so I usually do this from the MySQL Query Browser – Tools – MySQL Command Line Client to avoid having to enter username and password etc.)
  4. Enter this command:
    LOAD DATA LOCAL INFILE ‘C:\\temp\\yourfile.csv’ INTO TABLE database.table FIELDS TERMINATED BY ‘;’ ENCLOSED BY ‘”‘ LINES TERMINATED BY ‘\r\n’ (field1, field2);
    [Edit: Make sure to check your single quotes (‘) and double quotes (“) if you copy and paste this code – it seems WordPress is changing them into some similar but different characters]
  5. Done!

Thanks to : http://blog.tjitjing.com/index.php/2008/02/import-excel-data-into-mysql-in-5-easy.html

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s