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.
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.
See also:
- Import Excel File into MySQL Database using PHP
- How to Export data to Excel sheet in PHP MySQL
- Generate PDF File From MySQL Database Using PHP
- How to Export excel data from Database in PHP using Spreadsheet
- Import Excel File into MySQL Database in PHP using Spreadsheet
How to Import and Export CSV files using PHP and Mysql
Import and export of data to and from databases is a common enough procedure in PHP development. Another important activity is the backup and transfer of databases.
Create a Database in MySQL
The first step in this tutorial is the creation of a MySQL database. you can create tables by running SQL queries. Create a table ‘users’ in database using the following SQL query.
-- -- Database: `testing` -- Table structure for table `users` -- CREATE TABLE `users` ( `id` int(11) NOT NULL PRIMARY KEY AUTO_INCREAMENT, `name` varchar(50) NOT NULL, `username` varchar(50) NOT NULL, `email` varchar(50) NOT NULL, `age` int(50) NOT NULL `city_name` int(50) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
This will create a new table ‘users’ in the database. I will use this table to insert data from the CSV file.
Create MySql Connection in PHP
For importing and exporting database in MySql will make a separate file ‘config.php’. Add the following code and replace the database credentials with yours. You can find your db credentials in Application Access details:
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
After the database has been created, I next need an HTML file that could upload CSV file. For this HTML file, I will use HTML File uploader in a simple bootstrap form.
Create a file and name it ‘index.php’. This is a simple form for uploading CSV file. This file will also show the results in a simple table on the same page. When the user submits the form, all records will be saved in the database.
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 in CSV 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 in 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
Conclusion
In this article, I discussed how you could export data from and to CSV files using PHP and MySQL. This is a simple example you can Add more complex logic and validations as per your requirements.
You can always support by sharing on social media or recommending my blog to your friends and colleagues. If you have any suggestions or problems about this tutorial, please comment on the form below.😊
