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
– User014019, Commented Sep 14, 2016 at 7:41

zhiqin_guo · Accepted Answer · 2016-09-14 07:18:32Z

0

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

answered Sep 14, 2016 at 7:18

zhiqin_guo

10710 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ajit Kumar · Accepted Answer · 2019-07-06 11:01:00Z

<?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>

Collectives™ on Stack Overflow

Import CSV file to Mysql Database in PHP

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related