Easily Import CSV File Data Into Mysql Using PHP

Many times you will need to import data from a CSV (comma separated value) file and insert it into your MySQL database. Consider a case when you have many records in a CSV file and you need to import them into your MySQL database then you can not insert each single record manually as it will take too much time. This condition arises mostly when you want to import existing data in your website. In this tutorial I will explain how easily you can do that. I am posting script which will do the work easily for you. The main focus of this post is the logic which makes your whole code a lot easier and simpler.

import csv file into Mysql

                                        DOWNLOAD

Lets understand this from the start:

I have created one csv file “infotuts.csv” for test purpose. I will be inserting data from infotuts.csv file to our MySQL database. I have created one test table with name “csvdata” with 3 columns (similar to columns in CSV file).

SQL query to create csvdata table:

CREATE TABLE IF NOT EXISTS `csvdata` (
`ID` int(10) NOT NULL AUTO_INCREMENT,
`name` varchar(50) NOT NULL,
`email` varchar(50) NOT NULL,
PRIMARY KEY (`ID`)
)

Code For csv-import.php:

<?php

$connect = mysql_connect('localhost','root','12345');
if (!$connect) {
die('Could not connect to MySQL: ' . mysql_error());
}

$cid =mysql_select_db('test',$connect);
// supply your database name

define('CSV_PATH','C:/wamp/www/csvfile/');
// path where your CSV file is located

$csv_file = CSV_PATH . "infotuts.csv"; // Name of your CSV file
$csvfile = fopen($csv_file, 'r');
$theData = fgets($csvfile);
$i = 0;
while (!feof($csvfile)) {
$csv_data[] = fgets($csvfile, 1024);
$csv_array = explode(",", $csv_data[$i]);
$insert_csv = array();
$insert_csv['ID'] = $csv_array[0];
$insert_csv['name'] = $csv_array[1];
$insert_csv['email'] = $csv_array[2];
$query = "INSERT INTO csvdata(ID,name,email)
VALUES('','".$insert_csv['name']."','".$insert_csv['email']."')";
$n=mysql_query($query, $connect );
$i++;
}
fclose($csvfile);

echo "File data successfully imported to database!!";
mysql_close($connect);
?>

Further you can use this script and make it more flexible by allowing user to upload the CSV file and import data from that CSV file on the fly. There are many tweaks that you can do with this script and use it the way you want. In my future post I will publish code on how to make this script user oriented.

35 thoughts on “Easily Import CSV File Data Into Mysql Using PHP”

  1. Pingback: Easily Export Data From MySQL to CSV File - InfoTuts

    1. Hi Liza,

      You can put this code in a function which will accept one parameter (url/path of uploaded csv file). create a code so that user can upload the csv file and as soon as the file is uploaded, call the function and pass the ‘path of file’ as the parameter.

      If I get tym, I will post this tutorial also.

      Thanks:
      Sanjeev

      1. Hi, i need to manually browse a .csv file from my pc and want to upload it to the mysql database, Is the code available to do that

  2. Great Script, However how do i get it to replace the data in the table, for example im using it for a user in an admin section of a site to upload information on an exhibition where the table holds size, price etc etc of exhibition stands. So the user could upload the file that holds updates therefore needing to to replace the current ones and add any extras, Hope i explained that well enough.

    Thanks in advance for any help.

    Ian

    1. Hi Ian Snook,

      That’s a good query, In your case you will have to fire a ‘truncate table table_name’ query before ‘insert into’ statement. Truncate table query will make the table empty and then you can populate it with new data from your excel sheet.

      Thanks:
      Sanjeev

  3. hi bro , i have 1 column in csv file with comma seprated values but when i read thes vlaues using ur code then i not be able to fetch 1 column complate values(comma seprate values)

    column name: Contact Detail
    Value in csv: patric ha, owner

  4. I can upload the file but i get undefined offset 1 at line 23 and undefined offset 2 at line 24.

  5. Am getting an error on line 3

    Parse error: syntax error, unexpected ‘<', expecting T_VARIABLE or '$' in D:\xampp\htdocs\csv-import.php on line 3

  6. i want help from you for importing csv file in user friendly manner it should have browse option and submit button pls help….

  7. Pingback: How to Import CSV File Data Into Mysql Using PHP | Share your word with the world

  8. I just want to insert data of csv file without heading. I want to insert data from the begining of the row. In that what I have to do

  9. hi.. this code works well.. but when i import my file i get an empty row inserted in database since i have only two rows .. and why i get empty rows inserted in it???

  10. Hi, I was wondering if you can help me with your code. The only thing I didn’t use was the SQL query to create a table because I wasn’t for sure how to use it? Was I supposed to put it in the same file as the php code? Anyway, I have the table made so not sure if I need it. But I wrote your code and made a csv file and I ran it from my computer with an xampp server, apache and MySQL turned on, and I connected your code to my database address in my godaddy hosting site, and I went to localhost page, it says that “File data successfully imported to database!!”, but when I check my database, nothing is added to the table. I have remote access also. Not sure why this is, can you help me make it work, I must’ve missed something. Thanks for your time, I like the code though and would love to be able to use it successfully. Hope you have a good day.

    1. Hi Jerry,

      I suggest you to upload your PHP file to your hosting and then execute it. There may be something wrong with remote connection to MYSQL, but that would have definitely thrown an error. Can you email me your files so that I can check.

      Thanks:
      Sanjeev

  11. Your implementation is vulnerable to SQL injection. Please fix your code and/or switch to mysqli for better security.

  12. IN theory this could be really helpful code for me but I’ve run into a snag. My CSV file is UTF8 and some of the fields contain commas within the fields. When I use PHPMyAdmin, the file imports no problem, but the

    $csv_array = explode(“,”, $csv_data[$i]);

    treats all the commas as field delimiters even though some of them are enclosed within quotes “” as part of a field. Is there an easy way to modify this routine to address this issue?

    Thanks. I appreciate you making this code available for others to use.

    1. My data file had all data fields enclosed in double quotes ” as well being delimted by commas. In other words “,” separated each field. By modifying the explode to the following, I was able to properly capture all the fields.

      $csv_array = explode(“\”,\””, $csv_data[$i]);

        1. Hi, I was wondering if you got my email? I was contacting you to see if you could help me write the code to insert more than one CSV file into more than one table at a time?

  13. Great php script, I came very far in comparison what I would have achieved on my own. But (there’s always a but) my script tries to import a cdv file with not only , (comma’s) as delimiter, but also ” as text quotes. So in the end I end up with values for variables like ‘”362″‘, embedded as well in single as well as in double quotes.

    My question is how to get rid of these additional double quotes (“)?

    BR, Liebling.

  14. how to filter the data inside the csv.. ?? I can upload multiple time and data are duplicated

  15. Does this work if a CSV file is on some web location: “http://www.someurl/file.csv”? What in that case? Is it possible at all?

  16. Great Work.. but I need your help while testing your code. Extra one row add at end of the table. kindly guide my query

  17. The script is good but when i use this script i could not import the unicoded data .My data in csv is in nepali language.Collation for the fields in mysql is utf8-unicode-ci.The data from csv in nepali language after being inserted is like ????.How can i solve this issue?

  18. Great work !
    anyway I would like to know if it’s possibile to automate the upload of the csv file through ftp every day at a specific time ? and automate also the uploading of the file on the mysql database ?

  19. i want to upload a csv file but have a one problem in database table have 5 columns but csv file have 3 columns in database table in have column name 1>name 2>address 3>phn 4>email 5>age or csv file have 3 columns likes 1>address 2>phn 3>name how can i insert data proper position to this table please solve this problem.

  20. Great work !
    But was having a query that how to avoid records’ duplication while inserting records? For example I am having a CSV of products to insert into MySQL using PHP. If I uploaded 10 products first. Then I modified that CSV and added next 10 products. Then I again used the same to insert next 10 to MySQL. So how can I avoid first 10 and start insertion from 11th?

    Thanx in advance.

Comments are closed.