Sunday, November 27, 2011

9:01 AM

Today i came across a functionality where i need to import the CSV/Excel file in to MYSQL database via PHP script. There is a special requirement from client where he can upload the CSV/Excel file in file upload field in HTML form and all data from CSV/Excel must import into MYSQL database table through PHP.

You can import data of CSV/Excel into MYSQL via PHP using fgetcsv() function along with some file handling functions. Here i would like to share that script with all of you. I hope that in future this article will be helpful to you when you need to implement this type of functionality.

     $fname = $_FILES['sel_file']['name'];
     $chk_ext = explode(".",$fname);
     if(strtolower($chk_ext[1]) == "csv")
         $filename = $_FILES['sel_file']['tmp_name'];
         $handle = fopen($filename, "r");
         while (($data = fgetcsv($handle, 1000, ",")) !== FALSE)
            $sql = "INSERT into user(name,email,phone) values('$data[0]','$data[1]','$data[2]')";
            mysql_query($sql) or die(mysql_error());
         echo "Successfully Imported";
         echo "Invalid File";
<form action='<?php echo $_SERVER["PHP_SELF"];?>' method='post'>
    Import File : <input type='text' name='sel_file' size='20'>
    <input type='submit' name='submit' value='submit'>

Above code will first check for valid csv file. If it is valid csv file than with the use of fopen() function , uploaded file will be opened in read mode. Now using fgetcsv() function , you will have a line by line data from csv file. 

Each line you will get is an array with all column values.  So now you have all data from csv file. You can play with them according to your needs. I have shown an example to insert 3 data in database table user. If your csv file contains more data than you will get in $data[0] , $data[1], $data[2],$data[3] and so on..

Thats it. Let me know your thoughts for the same. If you face any problem in this than let me know via comment