Friday, January 28, 2011

PHP, load EXCEL/CSV file to mysql


Excel files can be saved as csv  format first.
1. First Create a database and table to match columns in csv, for example: first column name, then email and phone
CREATE DATABASE IF NOT EXISTS mydatabase;
USE mydatabase;
DROP TABLE IF EXISTS `user`;
CREATE TABLE user
(
    name VARCHAR(50)      NOT NULL DEFAULT '',
    email VARCHAR(50) NOT NULL DEFAULT '',
    phone int(11),
    PRIMARY KEY (`email`)
)ENGINE=InnoDB  DEFAULT CHARSET=utf8;


PHP script
<?php
ini_set('display_errors', 1);
error_reporting(E_ALL);
$db_host = 'localhost';
$db_user = 'root';
$db_pwd = 'mypasswd';

$database = 'mydatabase';
$table = 'user';

if (!mysql_connect($db_host, $db_user, $db_pwd))
    die("Can't connect to database");

if (!mysql_select_db($database))
    die("Can't select database");


    if(isset($_POST['submit']))
    {
         $fname = $_FILES['sel_file']['name'];
         echo 'upload file name: '.$fname.' ';
         $chk_ext = explode(".",$fname);
       
         if(strtolower(end($chk_ext)) == "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());
             }
      
             fclose($handle);
             echo "Successfully Imported";
           
         }
         else
         {
             echo "Invalid File";
         }  
    }
   
    ?>
    <h1>Import CSV file</h1>
    <form action='<?php echo $_SERVER["PHP_SELF"];?>' method='post' enctype="multipart/form-data">
        Import File : <input type='file' name='sel_file' size='20'>
        <input type='submit' name='submit' value='submit'>
    </form>


8 comments:

  1. how to check duplicate data when you import the csv file?

    ReplyDelete
  2. what if duplication of entry in the database from csv file like same name? any code for that? thanks in advance

    ReplyDelete
  3. This comment has been removed by the author.

    ReplyDelete
  4. for phone number if i put 0101234567 in excel CSV file, it will became 101234567 and display as 101234567 in database..how to solve it?
    thanks

    ReplyDelete
  5. simile boy i think that depends of the type of data you define for the colon , if you need to keep the 0 , i think you must use the type VARCHAR.

    ReplyDelete
  6. I get the below error:

    upload file name: test.csv You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'name,Country,Currency) values('Company name','Country','Currency')' at line 1

    Please help me resolve this.
    Thanks in advance

    ReplyDelete
  7. getting error on running file
    Cannot GET /%3C

    ReplyDelete