Popular Posts

There was an error in this gadget

Aug 19, 2013

Easy Way to Insert Data into Mysql Table from a CSV or Excel File



Reading each row from excel/csv file and then insert to mysql table is a old way.we can use mysql inbuilt query. The same thing in sqlserver and .net we use bulk insert to insert data from excel to sqlserver.

first create a page named it index.html


<html>
<head>
<script> // This function will pop a window which will tell the 
 
                 function popitup(url)
                {
                        new_wind=window.open(url,'name','height=700,width=1500');
                        if (window.focus) {new_wind.focus();
                        return false;
                }
               }
        </script>
</head>
<body>
Add  data : The file should be a .csv file  <a href="index.html" 
 onclick="return popitup('index.html')"
  > Check the format here </a> </br></br> 
 will be directed to ins_data.php-->
        <form action="ins_data.php" method="post" enctype="multipart/form-data">
                <label for="file">   </label>
                <input  type="file" name="file" id="file" />
          </br></br>      <input type="submit" value="Load data" 
   name="button1"/>
        </form>
</div>
</body>
                </html>
 
 
 Create new file named it ins_data.php
 
 
 
<?php
         if (isset($_POST['button1'])) //  Do  THE FOLLOWING WHEN BUTTON IS PRESSED
        {
                echo "button on is pressed";
                 if ($_FILES["file"]["error"] > 0)
                {
         echo "Error: " . $_FILES["file"]["error"] . 
   "You have not selected a file or some other error <br />";
                }
                else
                {       //Errorless  start 
                        $file_name=$_FILES["file"]["name"];echo $file_name;
                        $file_type=$_FILES["file"]["type"];
                        if($file_type!='text/csv')
                        {
                                echo "Please the input file should be a .csv file";
                        }
                        else
                        {       
      //      only executed if file is .csv
       echo "its correct";
                                
   // Creating a temporary copy on the server 
     $location=""; //write the location of the uploaded file 
     // upload file to server
     move_uploaded_file($_FILES["file"]["tmp_name"], 
     $location . $_FILES["file"]["name"]);
                                                                                
                connect_db(); // MYSQL connection settings
                // I have provided a sample query : 
        // Please make changes as per your requirement 
                $q="LOAD DATA INFILE '$file_name' INTO TABLE t_log 
                              FIELDS TERMINATED BY \"\t\" 
                              LINES TERMINATED BY \"\n\" 
                              ( Lang,Doc_Type,Title,Issue,Keywords )";
                mysql_query($q) or die(mysql_error());
                        }
                }
}

?>
<?php
function connect_db()
{
        //  db config 
        $con = mysql_connect("localhost","username","password");
        if (!$con)
        {
                die('Could not connect: ' . mysql_error());
        }
        // enter your database name
        mysql_select_db("dbname", $con);
}
?>