import csv file

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.

table book structure

Table Book Structure

Table Book Data

Table Book Data

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.

Data to import (still in .xlsx format)

Data to import (still in .xlsx format)

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.

Save as CSV

Save as CSV

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.

Open CSV file with Text Editor

Open CSV file with Text Editor

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).

Pre-cleaned CSV

Pre-cleaned CSV

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.

Cleaned CSV file

Cleaned CSV file

CSV file ready to be imported.

Choose file to import

Choose file to import

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.

Setting up configuration

Setting up configuration

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.

Data has been imported

Data has been imported

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.

you might also liked

comments

LEAVE A COMMENT


Get Latest Update

Weekly updates of best free resource on the web. Great solutions for designers & developers

advertisment

Please insert your advertisment shortcode

What Is Junglegraphic

junglegraphic is a website for designers & developers Enthusiast to search a freebies & Tutorials From beginner to intermediate.

junglegraphic development

junglegraphic has been developed by Illiyin studio with wordpress machine

stay in touch

Subscibe our RSS and don't be shy to like our Facebook follow our twitter and stalking our Instagram.