How to Import and Export CSV Files Using PHP and MySQL


How to Import and Export CSV Files Using PHP and MySQL
Hello Friends, In this article we show you how to import data from CSV files and paste them into MySQL database tables using PHP Script. In this post, you will learn how to extract data from uploaded CSVs and then insert data into MySQL tables. If you want to enter a large amount of data stored under a CSV file and put it in a MySQL database, you can use this type of concept at this time. To retrieve data from CSV data files, we use the simple function fgetcsv () to extract all data from CSV files, and then run an insert query to enter data into the table.
 
What is CSV? This is a comma-separated value file that stores large amounts of tabular data in plain text format. CSV files are mainly used to import or export data from a table. This allows us to easily exchange large amounts of data exchange from a MySQL database server, because this requires less space than other spreadsheet formats like Excel. After exchanging data, we can use it according to our needs. Most enterprise-level application data is imported and exported in CSV file format because we can quickly exchange data from a database server.
 

Here we are using Four Files to Import and Export CSV files using PHP and Mysql

1. Create database connection file
2. Create a HTML page display records 
3. Create PHP code for Export data excel from database Table
4. Create PHP code for Import data from database Table
 

Create database connection file

config.php
<?php
// Database configuration
$dbHost = "localhost";
$dbUsername = "root";
$dbPassword = "";
$dbName = "testing";

// Create database connection
$con = mysqli_connect($dbHost, $dbUsername, $dbPassword, $dbName);

// Check connection
if ($con->connect_error) {
die("Connection failed: " . $con->connect_error);
}
?>
 

Create a HTML page display records

index.php
<!Doctype html>
<html lang="en">
<head>
<meta charset="utf-8">
<title>How to Import and Export CSV Files Using PHP and MySQL</title>
<meta name="viewport" content="width=device-width, initial-scale=1">
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.5.0/css/bootstrap.min.css">
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.5.1/jquery.min.js"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/4.5.0/js/bootstrap.min.js"></script>
</head>
<body>
<div class="container" style="margin-top:50px">
<h1 style="text-align:center;">How to Import and Export CSV Files Using PHP and MySQL</h1><br>
<div class="row">
<!--- this form is for the Import data--->
<form action="import_data.php" method="POST" enctype="multipart/form-data" style="margin-left: 500px;">
<div class="form-group">
<label>Select File</label>
<input type="file" name="file" class="form-control" required="">
</div>
<div class="form-group">
<input type="submit" name="import" class="btn btn-primary" value="Upload">
</div>
</form>

<!--- This form is for the Export data--->
<form action="export_data.php" method="POST" style="margin-left:1010px;margin-bottom: 20px;">
<input type="submit" class="btn btn-success" name="export" value="Export to Excel">
</form>

<table class="table table-striped">
<thead>
<tr>
<th>Id</th>
<th>Name</th>
<th>Email</th>
<th>Username</th>
<th>Age</th>
<th>City</th>
</tr>
</thead>
<?php
// include database connectivity

include_once('config.php');

// fetch data from users table

$query = "SELECT * FROM users";
$result = mysqli_query($con, $query);
if (mysqli_num_rows($result) > 0) {
while ($data = mysqli_fetch_assoc($result)) {
?>
<tr>
<td><?php echo $data['id']?></td>
<td><?php echo $data['name']?></td>
<td><?php echo $data['email']?></td>
<td><?php echo $data['username']?></td>
<td><?php echo $data['age']?></td>
<td><?php echo $data['city_name']?></td>
</tr>
<?php } } ?>
</table>
</div>
</body>
</html>

 

Create PHP code for Export data excel from database Table

export_data.php
<?php

    // Include database connectivity

include_once('config.php');

// Export file in php

if (isset($_POST['export'])) {
$query = "SELECT * FROM users";
$result = mysqli_query($con, $query);
if (mysqli_num_rows($result) > 0) {
$output = "";
$output .= "<table class='table table-striped'>
             <thead>
     <tr>
    
<th>Id</th>
     <th>Name</th>
<th>Email</th>
     <th>Username</th>
     <th>Age</th>
     <th>City Name</th>
     </tr>
    </thead>"
;
while ($data = mysqli_fetch_assoc($result)) {
$output .= "<tr>
<td>"
.$data['id']."</td>
<td>"
.$data['name']."</td>
<td>"
.$data['email']."</td>
<td>"
.$data['username']."</td>
<td>"
.$data['age']."</td>
<td>"
.$data['city_name']."</td>
</tr>";
}

$output .="</table>";

header('Content-Type: application/xls');
header('Content-Disposition: attachment; filename=download.xls');

echo $output;
}else{
echo "No record found";
}
}

?>

 

Create PHP code for Import data from database Table

import_data.php
<?php

// include database connectivity

include_once('config.php');

// Import file in php

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

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

if ($_FILES['file']['size'] > 0) {

$file = fopen($fileName, "r");

while (($importData = fgetcsv($file, 10000, ",")) !== FALSE) {

$name = "";
if (isset($importData[0])) {
$name = mysqli_real_escape_string($con, $importData[0]);
}
$email = "";
if (isset($importData[1])) {
$email = mysqli_real_escape_string($con, $importData[1]);
}
$username = "";
if (isset($importData[2])) {
$username = mysqli_real_escape_string($con, $importData[2]);
}
$age = "";
if (isset($importData[3])) {
$age = mysqli_real_escape_string($con, $importData[3]);
}
$city_name = "";
if (isset($importData[4])) {
$city_name = mysqli_real_escape_string($con, $importData[4]);
}

$query = "INSERT INTO users (name, email, username, age, city_name)
VALUES('"
.$name."','".$email."','".$username."','".$age."','".$city_name."')";

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

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);
}
}

?>
 

Output

How to Import and Export CSV Files Using PHP and MySQL
 
You can always support by sharing on social media or recommending my blog to your friends and colleagues.
If you have any suggestions / problems about this tutorial, please comment on the  form below.😊

Leave a Reply