CODEWITHSUNDEEP
phpmysqlsqlexcel
P33BHOIY
P33BHOIY

Reputation: 11

Import multiple rows of data in excel to mysql using php

I tried importing excel to mysql table with different data type, inclusive a date time column, but the code only seems to pick the first row of the excel data and the date is displayed as: 0000-00-00. How do I also ensure the file does not upload if trade_number($emapData[2]) is repeated as this is a primary key in my database table? Please I do need assistance to rectify this problems.

My HTML is this:

<form enctype="multipart/form-data" method="post" role="form">
        <div class="form-content">
            <h1>Traded Securities Data Upload</h1>

            <div><label for="exampleInputFile">File Upload: </label><br><input type="file" name="file" id="file"><p style="font-size: .8em; color: rgb(51,0,255);">Only Excel/CSV File to be uploaded.</p></div>
            <div><button type="submit" id="Import" class="btn btn-default" name="Import" value="Import">Upload</button></div>

          </div><!--end of form content div--><br class="clear-fix">
            </form>

while my PHP code block is 

    <?php 
    if(isset($_POST["Import"])){
    // Connnection string to the database
    mysql_select_db($database_connect, $connect);
        echo $filename=$_FILES["file"]["tmp_name"];
        if($_FILES["file"]["size"] > 0){
            $file = fopen($filename, "r");
            $count = 0;                                         // add this line
            while (($emapData = fgetcsv($file, 10000, ",")) !== FALSE){
                //print_r($emapData);
                //exit();
                $count++;// add this line

                if($count>1){// add this line
                  $sql = mysql_query("INSERT into trades_data(symbol,trade_date,trade_number,buy_order_date,buy_order_number,sell_order_date,sell_order_number,sell_account,buy_account,buy_firm,sell_firm,buy_firm_name,sell_firm_name,entry_time,price,quantity,settle_date,trade_value,settlement_value,updated) values ('$emapData[0]','$emapData[1]','$emapData[2]','$emapData[3]','$emapData[4]','$emapData[5]','$emapData[6]','$emapData[7]','$emapData[8]','$emapData[9]','$emapData[10]','$emapData[11]','$emapData[12]','$emapData[13]','$emapData[14]','$emapData[15]','$emapData[16]','$emapData[17]','$emapData[18]',now())") or die (mysql_error());
                  mysql_query($sql);
                }  // if count closed                                            // add this line
            } //while loop closed

            fclose($file);
            echo 'CSV File has been successfully Imported';
            header('Location: ../edit_pages.php');
        }
            else{
            echo 'Invalid File:Please Upload CSV File';}
    } // close first if
?>

Upvotes: 1

Views: 3855

Answers (2)

Hara Prasad
Hara Prasad

Reputation: 724

<form enctype="multipart/form-data" method="post" role="form">
                        <div class="form-group">
                                 <label for="exampleInputFile">File Upload</label>
                                 <input type="file" name="file" id="file" size="150">
                                <p class="help-block">
                                        Only Excel/CSV File Import.
                                </p>
                        </div>
                         <button type="submit" class="btn btn-default" name="Import" value="Import">Upload</button>


                 </form>

And my PHP code as like, I used PHP function fgetcsv()

<?php 
if(isset($_POST["Import"]))
{
//First we need to make a connection with the database
$host='localhost'; // Host Name.
$db_user= 'root'; //User Name
$db_password= '';
$db= 'product_record'; // Database Name.
$conn=mysql_connect($host,$db_user,$db_password) or die (mysql_error());
mysql_select_db($db) or die (mysql_error());
echo $filename=$_FILES["file"]["tmp_name"];
if($_FILES["file"]["size"] > 0)
{
$file = fopen($filename, "r");
//$sql_data = "SELECT * FROM prod_list_1 ";
while (($emapData = fgetcsv($file, 10000, ",")) !== FALSE)
{
//print_r($emapData);
//exit();
$sql = "INSERT into prod_list_1(p_bench,p_name,p_price,p_reason) values ('$emapData[0]','$emapData[1]','$emapData[2]','$emapData[3]')";
mysql_query($sql);
}
fclose($file);
echo 'CSV File has been successfully Imported';
header('Location: index.php');
}
else
echo 'Invalid File:Please Upload CSV File';
}
?>

Upvotes: 0

Oscar M.
Oscar M.

Reputation: 1086

  1. USE PDO or mysqli and prepared queries. Not only will this gaurd against SQL injection attacks or other errors (like a cell having an apostrophe in it) but your inserts will be faster in the loop.

  2. Ensure the dates that you are inserting are in a format that Mysql expects, safest is YYYY-MM-DD. Either update your spreadsheet to use that format, or transform the date value when you import it using PHP's DateTime class and its format() method

Upvotes: 1

Related Questions