Import Excel, CSV file data into MySQL database table
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:
Now after we have done the Excel sheet and click the “file” and “save as”, in the “save as type” choose “CSV(Comma delimited)”
I named that file as “asheet.csv”, After the files being saved, you get:
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.
Reference with the image above, in the setting screen, we have to do:
- click on the “browse” button and select the csv file.
- In the “Format of imported file” choose “CSV using LOAD DATA“
- 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.
- At last click “go” button and if everything is right, the data has imported into the MySQL table.