Monday 18 April 2016

Upload CSV and Insert Data into Mysql Using PHP



In this post We will show you how to import data from CSV File to and Insert into Mysql Database table using PHP Script. In this post you can find how to fetch data from uploaded CSV and then after Insert Data into Mysql table. If you want to insert large amount of data which is store under CSV file and you want insert into to Mysql database then at that time this type of concept you can use. For this get data from CSV file data, We have use simple fgetcsv() function for fetch all data from CSV file and after that We have run insert query for insert data into table.


First what is CSV?, It is comma-separated values file that stores large amount of the tabular data in simple plain text format. Mostly, CSV type file is utilize to import to or export data from the table. So we can easily large amount of exchange data from Mysql database server because it takes less space than other tabular format data like Excel. After Exchange of data we can use that data according to our requirement. In most of the enterprise level application data has been import and export in CSV file format because we can fastly exchange data from database server. So In this post we have discuss topic like How to import data from CSV file by using PHP Script.



Source Code



<?php  
$connect = mysqli_connect("localhost", "root", "", "testing");
if(isset($_POST["submit"]))
{
 if($_FILES['file']['name'])
 {
  $filename = explode(".", $_FILES['file']['name']);
  if($filename[1] == 'csv')
  {
   $handle = fopen($_FILES['file']['tmp_name'], "r");
   while($data = fgetcsv($handle))
   {
    $item1 = mysqli_real_escape_string($connect, $data[0]);  
                $item2 = mysqli_real_escape_string($connect, $data[1]);
                $query = "INSERT into excel(excel_name, excel_email) values('$item1','$item2')";
                mysqli_query($connect, $query);
   }
   fclose($handle);
   echo "<script>alert('Import done');</script>";
  }
 }
}
?>  
<!DOCTYPE html>  
<html>  
 <head>  
  <title>Webslesson Tutorial</title>
  <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.1.0/jquery.min.js"></script>  
  <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css" />
 </head>  
 <body>  
  <h3 align="center">How to Import Data from CSV File to Mysql using PHP</h3><br />
  <form method="post" enctype="multipart/form-data">
   <div align="center">  
    <label>Select CSV File:</label>
    <input type="file" name="file" />
    <br />
    <input type="submit" name="submit" value="Import" class="btn btn-info" />
   </div>
  </form>
 </body>  
</html>


Database



--
-- Database: `testing`
--

-- --------------------------------------------------------

--
-- Table structure for table `excel`
--

CREATE TABLE IF NOT EXISTS `excel` (
  `excel_id` int(11) NOT NULL AUTO_INCREMENT,
  `excel_name` varchar(250) NOT NULL,
  `excel_email` varchar(250) NOT NULL,
  PRIMARY KEY (`excel_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

--
-- Dumping data for table `excel`
--

14 comments:

  1. Thanks brother. Your site is so resourceful

    ReplyDelete
  2. Thanks!

    I use

    $connect = new mysqli("localhost", "root", "", "testing", 3307);

    to make it work.

    Furthermore, make sure that Excel uses commas instead of semicolons as separator.

    ReplyDelete
  3. Nice piece of code. Thanks much. This could do what PHPExcel and PHPSpreadsheet couldn't do. Using a TSV instead of aCSV will help load huge files in the database without causing any memory problems. Thanks much for the code. It was great.

    ReplyDelete
  4. Thanks

    i really impressed

    thanks again for this coading

    ReplyDelete
  5. Hi there
    I'm getting this error : Undefined offset: 1 on line 14. Any idea what might be ?
    thanks

    ReplyDelete
  6. You know your work 👌

    ReplyDelete
  7. I have an existing file and I want to update it? Please help on this. Thank you...

    ReplyDelete
  8. Hi I'm getting this error. Can you please help me? Thank you very much.
    Notice: Undefined index: file in C:\xampp\htdocs\SAMPLE\sample_upload.php on line 5

    ReplyDelete
  9. Great read. Thanks. Excited to see more.

    ReplyDelete
  10. Sorry I have tried it but didn't work, I received error message of "Note: undefined index file at line 4 in php section"

    ReplyDelete