Import CSV file to MySQL database via phpMyAdmin
Hello fellas! This time we will try to import data from a comma separated file (.csv) into a table in MySQL using phpMyAdmin. Actually there are many other ways to import CSV file to a MySQL database, but for now we will use phpMyAdmin because it’s quick, easy, and of course because we wouldn’t want the trouble to input loads of data manually into the database. Without further ado, let’s begin.
For example, I have prepared a table named “Book”. The table has 2 rows of data, and we’re going to insert more data into it.
And here we have an excel workbook file that contains 7 rows of data. We are going to import this data into table “Book” in MySQL database.
Now save the excel workbook file as CSV file. Select File (it’s on the top left of your screen), then Save as. Fill the filename as you wish, then choose CSV (Comma delimited)(*csv) as file type, and click the Save button.
So, we’re going to import it now? Nope. We need to modify the CSV file a little bit. Right click on the CSV file, select Open with, and choose Notepad (or whatever text editor you fancy).
Each of these lines in notepad will be imported into table “Book” as a row. Each column data divided by a comma (,). But wait, we don’t want the column names (first line in the Notepad) imported into the table as a row. We need to remove it, so cut the column names as we will need it to be pasted elsewhere soon.
CSV file ready to be imported.
Now back to phpMyAdmin and go to Import tab. (Note: before importing the CSV file into the table, you might want to copy the table first, just in case something goes wrong in the process). Now we’re going to import the CSV file. Firstly, click the Choose File button. Then browse the directory where you saved your CSV file and select it.
Choose CSV (not the LOAD DATA one) from the dropdown in Format panel. Then In the Column names, you can paste the column names we’ve cut from the CSV file. Make sure the column names are spelled correctly (identical with columns in your table), separated by commas, and not enclosed in quotes. If you spelled all your table columns in lowercase, then type it in lowercase or it will return an error (It will also return an error if you don’t fill the column names). When you’re done, click the Go button to import the CSV file.
There you are! New data has been imported to table “book”. Quick and easy isn’t it? Next time we’ll try to import CSV file to MySQL database via PHP script. Adios.