CODEWITHSUNDEEP
phpcsvload-data-infile
User0527
User0527

Reputation: 49

Data in CSV file only being imported partially into phpMyAdmin database table

I'm trying to import CSV file with complete data into phpMyAdmin database table. However, only part of the data in the CSV file is being imported. If I import the CSV file at phpMyAdmin, all the data is being imported. There is no error show and I'm not very sure where the problem occurs. I'm using LOAD DATA INFILE to load the data into the database table.

Here is my code:

<?php

session_start();

$host       = "localhost";
$user       = "root";
$password   = "";
$db         = "smposi";

$con        = mysqli_connect($host,$user,$password,$db);

$message = "";
$m_sfile    = $_SESSION['sfile'];

if (isset($_POST['submit'])) 
{

     $allowed = array('csv');
     $filename = $_FILES['file']['name'];
     $ext = pathinfo($filename, PATHINFO_EXTENSION);
     if (!in_array($ext, $allowed)) 
     {
          // show error message
          $message = 'Invalid file type, please use .CSV file!';
     } 
     else 
     {

          move_uploaded_file($_FILES["file"]["tmp_name"], "Imported CSV/" . 
          $_FILES['file']['name']);

          $file = "Imported CSV/" . $_FILES['file']['name'];

          $query = <<<eof
          LOAD DATA LOCAL INFILE '$file'
          INTO TABLE $m_sfile
          FIELDS TERMINATED BY ',' 
          OPTIONALLY  ENCLOSED BY '"'
          LINES TERMINATED BY '\n'
          IGNORE 1 LINES
          (sdate2,sdate,location,pcode,code,custarea,
           pdesc,packet,weight,max,ppacket,lqty,printed,
           corder,luserid,ltime)
eof;


          $query2       = mysqli_query($con,"DELETE FROM $m_sfile WHERE 
          location=''");
          if (!$result = mysqli_query($con, $query)) 
          {
               exit(mysqli_error($con));
          }
          $message = "CSV file successfully imported!";
     }
}
?>

Upvotes: 0

Views: 467

Answers (2)

Shahroze Nawaz
Shahroze Nawaz

Reputation: 589

I have imported the CSV data with the below simple script. Try it out if this helps. Also read the complete article for easily import/export CSV data. 

if(isset($_POST["Import"])){

        $filename=$_FILES["file"]["tmp_name"];      


         if($_FILES["file"]["size"] > 0)
         {
            $file = fopen($filename, "r");
            while (($getData = fgetcsv($file, 10000, ",")) !== FALSE)
             {


               $sql = "INSERT into employeeinfo (emp_id,firstname,lastname,email,reg_date) 
                   values ('".$getData[0]."','".$getData[1]."','".$getData[2]."','".$getData[3]."','".$getData[4]."')";
                   $result = mysqli_query($con, $sql);
                if(!isset($result))
                {
                    echo "<script type=\"text/javascript\">
                            alert(\"Invalid File:Please Upload CSV File.\");
                            window.location = \"index.php\"
                          </script>";       
                }
                else {
                      echo "<script type=\"text/javascript\">
                        alert(\"CSV File has been successfully Imported.\");
                        window.location = \"index.php\"
                    </script>";
                }
             }

             fclose($file); 
         }
    }    


 ?>

Upvotes: 1

Chandrasekhar Raman
Chandrasekhar Raman

Reputation: 696

Try running the following code.

<?php
$databasehost = "localhost"; 
$databasename = "smposi"; 
$databasetable = "sample"; 
$databaseusername="test"; 
$databasepassword = ""; 
$fieldseparator = ","; 
$lineseparator = "\n";
$csvfile = "filename.csv";

if(!file_exists($csvfile)) {
    die("File not found. Make sure you specified the correct path.");
}

try {
    $pdo = new PDO("mysql:host=$databasehost;dbname=$databasename", 
        $databaseusername, $databasepassword,
        array(
            PDO::MYSQL_ATTR_LOCAL_INFILE => true,
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
        )
    );
} catch (PDOException $e) {
    die("database connection failed: ".$e->getMessage());
}

$affectedRows = $pdo->exec("
    LOAD DATA LOCAL INFILE ".$pdo->quote($csvfile)." INTO TABLE `$databasetable`
      FIELDS TERMINATED BY ".$pdo->quote($fieldseparator)."
      LINES TERMINATED BY ".$pdo->quote($lineseparator));

   echo "Loaded a total of $affectedRows records from this csv file.\n";

?>

Upvotes: 0

Related Questions