Import Excel, CSV file data into MySQL database table

Posted by Shek on August 20, 2008 under MySQL | 4 Comments to Read | Total View: 5,572 views

To import Excel data into the MySQL database’s table is easy. All you have to do is to save the Excel file as “CSV” Format, and use phpmyadmin to complete the job for you. Here I have create an example to show you how to do so:


Here are the program I use in this example:
Excel 2003 and phpmyadmin 2.7.0.

First in the MySQL database, I have create a table named “test” with 2 fields “name” and “number”.

Now we create an Excel sheet with 2 columns:
First column’s data will be insert to “name” field.
Second column’s data will be to insert to “number” field.
Remember there is no need to type in the field names in the beginning of the columns.

The Excel sheet should look like this:

Peter 1
Mary 2
John 3
Sam 4



Now after we have done the Excel sheet and click the “file” and “save as”, in the “save as type” choose “CSV(Comma delimited)

CSV

CSV


I named that file as “asheet.csv”, After the files being saved, you get:
Peter,1
Mary,2
John,3
Sam,4

You can see that now the columns are separated with a comma.

Now open phpmyadmin and open the table where you want to import the data to, in this case it is the table “test”. then click on the “import” tag.

Interface

Interface


Reference with the image above, in the setting screen, we have to do:

  1. click on the “browse” button and select the csv file.
  2. In the “Format of imported file” choose “CSV using LOAD DATA
  3. Under the “Options for CSV import using LOAD DATA ” put “,” in the “Fields terminated by” field. Empty the “Fields enclosed by” because in this case the fields are not enclosed by anything.
  4. At last click “go” button and if everything is right, the data has imported into the MySQL table.

Not what you want? Try a Search

  • Ripon said,

    Hi Buddy,

    Thanks for your tutorial. I have visited many website for this tutorial. But failed to implement.

    But the way you have presented this tutorial is very easy and every one can understand it very easily.

    Thanks again.

    Ripon

  • Shek said,

    Thank you for your interest on my site!

  • Sital Prasad Mandal said,

    Thank ful to U!

    I have been success ! BY YOU!!!!!!

    You have solved my problem:

  • ahmad balavipour said,

    Thanks alot, very good tutorial

Add A Comment

*