Import CSV file to Mysql Database in PHP

Question

I tried to import a csv file from another server using LOAD DATA LOCAL INFILE but it gives me an error The user update failed: Can't find file, even the file is exist in the path given. I also tried the LOAD DATA INFILE but User access denied

Is there's any other way to import a csv file directly to the file path (without using any submit button) in the mysql database and delete the current data in the dbase table if there's a new csv file uploaded?

EDIT: I tried also this PHP script, to import the csv file without any button, but the csv file data is not importing

PHP (2nd code):

$mysqli = new mysqli("localhost", "user", "password", "dbasename");
$file = "/var/www/html/files/filename.csv";
$handle = fopen($file,'r');
while (($data = fgetcsv($handle, 10000, ",")) !== FALSE){
     $num = count($data);
     for ($c=0; $c < $num; $c++){
         $col[$c] = $data[$c];
     }

     $col1 = $data[0];
     $col2 = $data[1];
     $col3 = $data[2];
     $col4 = $data[3];
     $col5 = $data[4];

     // SQL Query to insert data into DataBase
     $query = "INSERT INTO dbname.tablename(col1,col2,col3,col4,col5) VALUES('".$col1."','".$col2."','".$col3."','".$col4."','".$col5."')";
     $mysqli->query($query);
}
fclose($handle);

Here's the PHP:

$sql = "LOAD DATA LOCAL INFILE 'filename.csv'
       INTO TABLE dbname.tablename
       FIELDS TERMINATED BY ','
       OPTIONALLY ENCLOSED BY '\"' 
       LINES TERMINATED BY '\n' 
       IGNORE 1 LINES;";

$con=mysqli_connect("localhost","user","password","db");
// Check connection
if (mysqli_connect_errno()){
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
};

$result = mysqli_query($con, $sql);

if (mysqli_affected_rows($con) == 1){
  $message = "The data was successfully added!";
} else {
  $message = "The user update failed: ";
  $message .= mysqli_error($con); 
};

echo $message;

@e4c5, i'm not trying to export the data but to import it to mysql database. I don't this is duplicate — User014019, Sep 14 '16 at 07:41

score 0 · Answer 1 · answered Sep 14 '16 at 07:18

you also can try use

$con=mysqli_connect("localhost","user","password","db");
$file = fopen("filename.csv","r");
while ($data = fgetcsv ($file)){
     print_r($data);
     $sql = 'insert into ...'
     $result = mysqli_query($con, $sql);
}

to import a csv file

Ajit Kumar · Answer 2 · 2019-07-06T11:01:00.017

<?php 
 $connect = mysqli_connect("localhost", "root", "", "testing");
 if(isset($_POST["submit"]))
{
 if($_FILES['file']['name'])
 {
  $filename = explode(".", $_FILES['file']['name']);
  if($filename[1] == 'csv')
  {
   $handle = fopen($_FILES['file']['tmp_name'], "r");
   while($data = fgetcsv($handle))
   {
    $item1 = mysqli_real_escape_string($connect, $data[0]);  
    $item2 = mysqli_real_escape_string($connect, $data[1]);
    $query = "INSERT into excel(excel_name, excel_phone) values('$item1','$item2')";
    mysqli_query($connect, $query);
   }
   fclose($handle);
   echo "<script>alert('Import done');</script>";
  }
 }
}
?>
<!DOCTYPE html>
<html>
<head>
 <title></title>
 <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.1.0/jquery.min.js"></script>  
 <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css" />
 <style>
  table{
   border-collapse: collapse;
   width: 100%;
   color: #588c7e;
   font-family: monospace;
   font-size: 25px;
   text-align: left;
  }
  th{
   background-color: #588c7e;
   color: white;
  }
  tr:nth-child(even) {background-color: #f2f2f2}
 </style>
</head>
<body>
 <form method="post" enctype="multipart/form-data">
  <div align="center">
   <label>Select CSV File:</label>
   <input type="file" name="file" />
   <br />
   <input type="submit" name="submit" value="Import" class="btn btn-info" />
  </div>
 </form><br><br>
  </body>
  </html>

Import CSV file to Mysql Database in PHP

2 Answers2