How to import and export CSV files using PHP and MySQL

Today, In this tutorial, we will be explaining how to import and export CSV files using PHP and MySQL. so let’s discuss Import and export CSV file.

It’s also very helpful in such as if you want to backup of data and you have data of CSV file then you can import the data into the database.

CSV extension stands for “Comma Separated Values” and contains all data in comma-separated. Normally, we have large data and need to import data into the database that time we use the following file types.

index.php

<!DOCTYPE html>
<html lang="en">
<head>
  <title>How to Import and Export CSV files using PHP and MySQL - XpertPhp</title>
  <meta charset="utf-8">
  <meta name="viewport" content="width=device-width, initial-scale=1">
  <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.0/css/bootstrap.min.css">
  <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
  <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.0/js/bootstrap.min.js"></script>
</head>
<body>

<div class="container" style="margin-top:50px;">
  <div class="row">
	<div class="col-lg-10"><h2>Import and export csv file</h2></div>
	<div class="col-lg-1"><a href="export.php" class="btn btn-success btn-sm">Export</a></div>
	<div class="col-lg-1"><button type="button" class="btn btn-info btn-sm" data-toggle="modal" data-target="#importModal">Import</button></div>
  </div>  
  <table class="table table-striped">
    <thead>
      <tr>
        <th>Id</th>
        <th>Firstname</th>
        <th>Lastname</th>
        <th>Email</th>
        <th>Phone</th>
        <th>Created</th>
      </tr>
    </thead>
    <tbody>
	<?php
	include('connection.php');
	$sql = 'SELECT * FROM users ORDER BY id desc';
    $result = mysqli_query($conn, $sql);
	while($row = mysqli_fetch_array($result)) {
	?>
      <tr>
        <td><?php echo $row['id'];?></td>
        <td><?php echo $row['first_name'];?></td>
        <td><?php echo $row['last_name'];?></td>
        <td><?php echo $row['email'];?></td>
        <td><?php echo $row['phone'];?></td>
        <td><?php echo $row['created'];?></td>
      </tr>
	<?php 
	} ?>	
    </tbody>
  </table>
</div>

<!-- Modal -->
  <div class="modal fade" id="importModal" role="dialog">
    <div class="modal-dialog">
    
      <!-- Modal content-->
      <div class="modal-content">
        <div class="modal-header">
          <button type="button" class="close" data-dismiss="modal">&times;</button>
          <h4 class="modal-title">Upload Csv file</h4>
        </div>
        <div class="modal-body">
          <form action="import.php" method="post" enctype="multipart/form-data">
			<div class="col-lg-12">
				<div class="form-group">
					<input type="file" name="csv_file" id="csv_file" class="filestyle" data-icon="false">
				</div>
			</div>	
			<div class="col-lg-12">
				<input type="submit" value="Upload file" id="upload_btn">
			</div>	
		  </form>
        </div>
        <div class="modal-footer">
          <button type="button" class="btn btn-default" data-dismiss="modal">Close</button>
        </div>
      </div>
      
    </div>
  </div>
</body>
</html>

<!DOCTYPE html>

<head>

<title>How to Import and Export CSV files using PHP and MySQL - XpertPhp</title>

</head>

<body>

<div class="col-lg-10"><h2>Import and export csv file</h2></div>

<div class="col-lg-1"><a href="export.php" class="btn btn-success btn-sm">Export</a></div>

<div class="col-lg-1"><button type="button" class="btn btn-info btn-sm" data-toggle="modal" data-target="#importModal">Import</button></div>

</div>

<thead>

<tr>

<th>Firstname</th>

<th>Lastname</th>

<th>Email</th>

<th>Phone</th>

<th>Created</th>

</tr>

</thead>

<tbody>

<?php

include('connection.php');

$sql = 'SELECT * FROM users ORDER BY id desc';

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

while($row = mysqli_fetch_array($result)) {

<tr>

</tr>

<?php

} ?>

</tbody>

</table>

</div>

<h4 class="modal-title">Upload Csv file</h4>

</div>

</div>

</div>

</form>

</div>

<button type="button" class="btn btn-default" data-dismiss="modal">Close</button>

</div>

</body>

</html>

connection.php

<?php
 $dbhost = 'localhost';
 $dbuser = 'root';
 $dbpass = '';
 $dbname = 'csv_export_import';
 $conn = mysqli_connect($dbhost, $dbuser, $dbpass,$dbname);

 if(! $conn ) {
	die('Could not connect: ' . mysqli_error());
 }
?>

<?php

$dbhost = 'localhost';

$dbuser = 'root';

$dbpass = '';

$dbname = 'csv_export_import';

$conn = mysqli_connect($dbhost, $dbuser, $dbpass,$dbname);

if(! $conn ) {

die('Could not connect: ' . mysqli_error());

}

export.php

<?php
// including the connection file
include('connection.php');
 
// set headers of csv format and force download
header('Content-Type: text/csv; charset=utf-8');
header('Content-Disposition: attachment; filename=users.csv');
 
$output = "First Name,Last Name,Email,Phone\n";

$sql = 'SELECT * FROM users ORDER BY id desc';
$result = mysqli_query($conn, $sql);
while($row = mysqli_fetch_array($result)) {
    $output .= $row['first_name'].",".$row['last_name'].",".$row['email'].",".$row['phone']."\n";
}
echo $output;
exit;
?>

<?php

// including the connection file

include('connection.php');

// set headers of csv format and force download

header('Content-Type: text/csv; charset=utf-8');

header('Content-Disposition: attachment; filename=users.csv');

$output = "First Name,Last Name,Email,Phone\n";

$sql = 'SELECT * FROM users ORDER BY id desc';

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

while($row = mysqli_fetch_array($result)) {

$output .= $row['first_name'].",".$row['last_name'].",".$row['email'].",".$row['phone']."\n";

}

echo $output;

exit;

import.php

here, We will use the fopen() and fgetcsv() php function in the below file. The fgetcsv() method reads for the field in the csv formate and returns the array.

<?php 
include('connection.php');
 
$csv_file =  $_FILES['csv_file']['tmp_name'];
if (is_file($csv_file)) {
	$input = fopen($csv_file, 'a+');
	$row = fgetcsv($input, 1024, ','); // here you got the header
	while ($row = fgetcsv($input, 1024, ',')) {
		// insert into the database
		$date = date('Y-m-d H:i:s'); 
		$sql = 'INSERT INTO users(first_name,last_name,email,phone,created) VALUES("'.$row[0].'","'.$row[1].'","'.$row[2].'","'.$row[3].'","'.$date.'")';
		mysqli_query($conn, $sql);
	}
}
header('location: index.php');
?>

<?php

include('connection.php');

$csv_file = $_FILES['csv_file']['tmp_name'];

if (is_file($csv_file)) {

$input = fopen($csv_file, 'a+');

$row = fgetcsv($input, 1024, ','); // here you got the header

while ($row = fgetcsv($input, 1024, ',')) {

// insert into the database

$date = date('Y-m-d H:i:s');

$sql = 'INSERT INTO users(first_name,last_name,email,phone,created) VALUES("'.$row[0].'","'.$row[1].'","'.$row[2].'","'.$row[3].'","'.$date.'")';

mysqli_query($conn, $sql);

}

header('location: index.php');