Import MS Excel Data into MySQL

USE THIS QUERY : Import MS Excel Data into MySQL

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\\WORKSHEET.csv” INTO TABLE EXCEL.COOLTABLE
    FIELDS TERMINATED BY “,” LINES TERMINATED BY “\n” (ID,NAME,ENO);
    [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!

 

FOR EXAMPLE:

MYSQL has database name EXCEL and table name as COOLTABLE

and 3 fields shown below

————————————————————————————-

ID                                  NAME                                ENO

—————————————————————————————

Very quick and simple once you know it :)

Some notes from my own import – may not apply to you if you run a different language version, MySQL version, Excel version etc…

  • TERMINATED BY – this is why I included step 2. I thought a csv would default to comma separated but at least in my case semicolon was the deafult
  • ENCLOSED BY – my data was not enclosed by anything so I left this as empty string ”
  • LINES TERMINATED BY – at first I tried with only ‘\n’ but had to add the ‘\r’ to get rid of a carriage return character being imported into the database
  • Also make sure that if you do not import into the primary key field/column that it has auto increment on, otherwhise only the first row will be imported

Leave a Reply to assist you..

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s