Sunday, June 22, 2014

CSV to Mysql import in PHP

Today i am going to explain how to import values form CSV to Mysql database.It is very useful to import bulk amount data's very easily.

Let's have look.



Step 1: You need a database connection

db.php

     <?php
        define ("DB_HOST", "localhost"); // set database host
        define ("DB_USER", "root"); // set database user
        define ("DB_PASS",""); // set database password
        define ("DB_NAME","db"); // set database name
      
        $link = mysql_connect(DB_HOST, DB_USER, DB_PASS) or die("Couldn't make connection.");
        $db = mysql_select_db(DB_NAME, $link) or die("Couldn't select database");
    ?>
 
Create a database and import the following sql query.

users.sql

CREATE TABLE IF NOT EXISTS `student` (
  `id` int(5) NOT NULL AUTO_INCREMENT,
  `name` varchar(200) NOT NULL,
  `email` varchar(200) NOT NULL,
  `city` varchar(200) NOT NULL,
  `image` blob NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

Used functions and Definition:

is_file() — Checks wheather the specified file is a regular file.
fopen - Used to open a file or url.
fgetcsv — Gets line from file pointer and parse for CSV fields

csv.php

      <?php
    if (isset($_POST['csvimport']))
{
  $csv_file = $_FILES['csvfile']['tmp_name'];

  if (!is_file($csv_file))
    exit('File not found.');

  $sql = '';

  if (($handle = fopen( $csv_file, "r")) !== FALSE)
  {
      while (($data = fgetcsv($handle, 1000, ",")) !== FALSE)
      {
          $sql .= mysql_query("INSERT INTO `users` SET
            `id` = '$data[0]',
            `name` = '$data[1]',
            `email` = '$data[2]',
            `city` = '$data[3]',
            `image` = '$data[4]'") or die("could not insert into DB");
      }
      fclose($handle);
  }

  exit( "Complete!");
}
?>

csv.html

<form enctype="multipart/form-data" method="POST" action="">
      <input name="csvfile" type="file">
      <input type="submit" name="csvimport" value="Upload">
    </form>

Please share your comments and feedback.Thanks.Please subscribe my updates via email.

No comments:

Post a Comment