What is CSV file and how to import it into MySQL Database?
In this tutorial we are going to learn about CSV file and how to import it into MySQL database using php. CSV (Comma Separated Values) file stores the plane data in plain text format. Every PHP developer needs to deal with CSV file import. So basically developer needs get the data from the CSV file and insert into the MySQL database.
Its actual common task for every web based software which has too much database and need to import data from CSV in MySQL database. This is a basic example.
Example image of importing Data from CSV File.
To import data from CSV file into MySQL Database table. I am doing following steps to import data from CSV file into MYSQL using PHP Script code.
STEPS
We need to follow the following steps below to import data from CSV file.
Create Database and students Table.
Open your phpMyAdmin and create a database with name (school) and create a table (students) in it using the below query:
CREATE TABLE `students` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`first_name` varchar(255) NOT NULL,
`last_name` varchar(255) NOT NULL,
`age` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
We have created a simple table in database with the columns like first_name, last_name, and age.
Create Database connection.
Create an index.php file and write a below code for database connection and save it.
<?php
$con=mysqli_connect("localhost","root","","school");
?>
Make sure to replace the placeholders with the actual values.
Sample Image of CSV file.
Now we are going to write a php code to import CSV file data of students.
PHP code to import CSV file data of students.
<?php
if (isset($_POST["import"])) {
$fileName = $_FILES["file"]["tmp_name"];
if ($_FILES["file"]["size"] > 0) {
$file = fopen($fileName, "r");
while (($column = fgetcsv($file, 10000, ",")) !== FALSE) {
$sqlInsert = "INSERT into students (first_name,last_name,age)
values ('" . $column[0] . "','" . $column[1] . "','" . $column[2] . "')";
$result = mysqli_query($con, $sqlInsert);
if (! empty($result)) {
$type = "success";
$message = "CSV Data Imported into the Database";
} else {
$type = "error";
$message = "Problem in Importing CSV Data";
}
}
}
}
?>
Now we are going to create html form to upload the CSV file on server.
<div id="response" class="<?php if(!empty($type)) { echo $type . " display-block"; } ?>"><?php if(!empty($message)) { echo $message; } ?></div>
<div class="outer-scontainer">
<div class="row">
<form class="form-horizontal" action="" method="post"
name="frmCSVImport" id="frmCSVImport" enctype="multipart/form-data">
<div class="input-row">
<label class="col-md-4 control-label">Choose CSV
File</label> <input type="file" name="file"
id="file" accept=".csv">
<button type="submit" id="submit" name="import"class="btn-submit">Import</button>
<br/>
</div>
</form>
</div>
</div>
HTML form image to upload CSV file.
Now we are going to create html table to display the data of students after importing the CSV file into database.
<table class="table datatable1 table-striped table-bordered">
<thead>
<tr>
<th>ID</th>
<th>First Name</th>
<th>Last Name</th>
<th>Age</th>
</tr>
</thead>
<tbody>
<?php
$query= mysqli_query($con,"SELECT * from students");
foreach($query as $val){
?>
<tr>
<td><?php echo $val['id']; ?></td>
<td><?php echo $val['first_name']; ?></td>
<td><?php echo $val['last_name']; ?></td>
<td><?php echo $val['age']; ?></td>
</tr>
<?php
}
?>
</tbody>
</table>
HTML table image to display the students data.
Here is complete script to import the CSV file into MySQL database using php including html form to upload the CSV file and html table to display the records of students.
<?php
// creat conection with database
$con=mysqli_connect("localhost","root","","school");
if (isset($_POST["import"])) {
// If the file is uploaded
$fileName = $_FILES["file"]["tmp_name"];
if ($_FILES["file"]["size"] > 0) {
$file = fopen($fileName, "r");
// Parse data from CSV file line by line
while (($column = fgetcsv($file, 10000, ",")) !== FALSE) {
// Insert students data in the database
$sqlInsert = "INSERT into students (first_name,last_name,age)
values ('" . $column[0] . "','" . $column[1] . "','" . $column[2] . "')";
$result = mysqli_query($con, $sqlInsert);
// Get status message
if (! empty($result)) {
$type = "success";
$message = "CSV Data Imported into the Database";
} else {
$type = "error";
$message = "Problem in Importing CSV Data";
}
}
}
}
?>
<!DOCTYPE html>
<html lang="en">
<head>
<title>How to import CSV file into MySQL database using PHP</title>
<!-- Bootstrap library -->
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0/css/bootstrap.min.css">
<!-- Stylesheet file -->
<link rel="stylesheet" href="style.css">
</head>
<body>
<div class="content-wrap">
<div class="main">
<div class="container-fluid">
<!-- /# row -->
<div id="main-content" >
<div class="row">
<div class="col-lg-6">
<div class="card alert">
<div class="card-header">
<h4>Import CSV file</h4>
</div>
<!-- Display status message -->
<div id="response" class="<?php if(!empty($type)) { echo $type . " display-block"; } ?>"><?php if(!empty($message)) { echo $message; } ?></div>
<div class="outer-scontainer">
<div class="row">
<!-- CSV file upload form -->
<form class="form-horizontal" action="" method="post"
name="frmCSVImport" id="frmCSVImport" enctype="multipart/form-data">
<div class="input-row">
<label class="col-md-4 control-label">Choose CSV
File</label> <input type="file" name="file"
id="file" accept=".csv">
<button type="submit" id="submit" name="import"class="btn-submit">Import</button>
<br/>
</div>
</form>
</div>
</div>
<!-- Data list table -->
<div class="bootstrap-data-table-panel">
<div class="table-responsive">
<table class="table datatable1 table-striped table-bordered">
<thead>
<tr>
<th>ID</th>
<th>First Name</th>
<th>Last Name</th>
<th>Age</th>
</tr>
</thead>
<tbody>
<?php
// fetch data from the database
$query= mysqli_query($con,"SELECT * from students");
foreach($query as $val){
?>
<tr>
<td><?php echo $val['id']; ?></td>
<td><?php echo $val['first_name']; ?></td>
<td><?php echo $val['last_name']; ?></td>
<td><?php echo $val['age']; ?></td>
</tr>
<?php
}
?>
</tbody>
</table>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</body>
</html>
The Bootstrap library is used to styling the Table, Form, and Buttons.
After adding the above code here is the final output of CSV file.
Final Output Image.
We hope you may learn how to import CSV file into MySQL Database using PHP. Please share your thoughts and queries in the contact section.
Below is the Download link of above tutorial.
Click here to download full source code