How to import a CSV file to your MySQL database?

If you are a developer, you will someday face it to import a CSV file to your database table. It is not tough and you can do it. Importing CSV file is relatively fast and accurate. What you need is to prepare your CSV file with field names in the same order your mysql table has. You will have to remove the header part (mainly the column name). If your table has a primary field, you either write “NULL” for your primary column or fill with incrementing numbers in your CSV file. NULL will be a good option to write if you do not know what number it will be incremented with.

There are two ways to import CSV file to your mysql database. The first is through PhpMyAdmin and the other is through your own program. Through PhpMyAdmin, you select the table in which you are going to import and click on Import button on top of right frame. There are some requirements that will be asked during import like columns separated with (default is comma), enclosed with (default is double quotes), escaped with (default is double quotes), terminated with (default is auto), and lastly the column or field names. If columns names are different or custom field names are there, need to write the column names of the CSV files, it is to insert in the last option ‘Column names’. Click on go button and it will import all data from CSV file to table precisely.

Now if you want to import data through PHP program, it is also easy. Create an HTML form that takes files to upload on server. Similar to the one I am writing below:

For uploading we will be using the same upload jQuery script that will load the CSV file to server. First of all add the jQuery library file and then the upload script:

 

 

 

I already have explained what this script is all about. I just will explain things that will import CSV file to the table. Now check the code inside import.php file. Remember to include the connection file before importing your file.

Import.php

 

And that’s it. In the above import.php file, we find the extension and check if it is a CSV file and then open the temp file as we have not moved to some folder on the server. Extract the rows and loop through it to save in an array.

We loop the fields using foreach to escape the quotes and then insert into the table by imploding that array. For any queries / suggestion, please do write in the comment section below.

June 11, 2017 by suman singh

Add a Comment

Your email address will not be published. Required fields are marked *