Import CSV to Database Using PHP and Mysqli
This post explains how to import data from a CSV(Comma Separated Values) file into a database table. To demonstrate how do we import data from a csv file into database we will be using an employees table and add employees records from uploaded csv file.
CSV is abbreviation of Comma Separated Values. As file extension tells its a text file that contains values separated with commas. Each row in this file is a record containing field values separated with commas. Let's move to importing uploaded csv file.
Files we are going to need are:
- constants.php: Holds constant values for our database connection.
- index.php: Contains the form to upload a csv file and code to import it into database table.
- style.css: Contains css styles for our form and table that shows the employees records from table.
constants.php
<?php
define("DB_HOST", "DATABASE_HOST"); //-- Your database host
define("DB_NAME", "DATABASE_NAME"); //-- Your database name
define("DB_USER", "DATABASE_USERNAME"); //-- Username for database
define("DB_PASSWORD", "DATABASE_PASSWORD"); //-- Password for database
?>
Ok so our main file index.php that will do all the job. We first establish a database connection and assign it to a variable. We then prepare a form with file type input field which will be used to upload a csv file. When the form is submitted we check if there was a file uploaded without any errors then we proceed to our next step and use pathinfo() to get extension of uploaded file to confirm it was csv file. Then we open uploaded file with file function fopen() with read permission we then read the file until it hits the end of file and use fgetcsv() to fetch the row in current iteration. $data array variable will be prepared with values to insert into our employees table.
After the data has been imported we run another query to get records from database table and show them in a table.
index.php
<?php
include("constants.php");
$dbcon = mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME) OR die(mysqli_connect_error());
?>
<!DOCTYPE html>
<html>
<head>
<title>Import CSV to Database using PHP and Mysqli</title>
<meta content="text/html; charset=UTF-8" http-equiv="Content-Type"/>
<link rel="stylesheet" href="css/style.css" />
</head>
<body>
<div class="main-container">
<div class="section">
<form name="csv-import" method="POST" enctype="multipart/form-data">
<div class="form-row">
<div class="col-4">
<input type="file" name="csv_file" class="form-control" />
</div>
<div class="col-4">
<input type="checkbox" name="first_row_header" id="first_row_header" />
<label for="first_row_header">First Row is Header</label>
</div>
<div class="col-4 text-right">
<button type="submit" class="btn-green">Import</button>
</div>
</div>
</form>
</div>
<div class="section">
<?php
//-- Check if file was uploaded & there were no errors
if ( $_FILES && $_FILES["csv_file"]["error"] == 0 ){
$extension = pathinfo( $_FILES["csv_file"]["name"], PATHINFO_EXTENSION );
//-- Check if extension is csv then proceed to import
if( $extension == "csv" ){
//-- Open file for reading
$file = fopen( $_FILES["csv_file"]["tmp_name"], "r" );
//-- Loop through all rows of file and insert them to database table
while ( !feof($file) ) {
//-- Get current row as recordset
$row = fgetcsv( $file );
if ( !empty($row) ){
$data = [];
$data["fullname"] = htmlentities($row[0]);
$data["email"] = htmlentities($row[1]);
$data["designation"] = htmlentities($row[2]);
$data["gender"] = htmlentities($row[3]);
mysqli_query($dbcon,"INSERT INTO employees (".implode(",",array_keys($data)).") VALUES ('".implode("','",array_values($data))."')");
}
}
}else{?>
<div class="alert alert-red">Uploaded file was not a csv file. Please upload a csv file</div>
<?php
}
}
?>
<table class="table">
<thead>
<tr>
<th>Name</th>
<th>Email</th>
<th>Gender</th>
<th>Designation</th>
</tr>
</thead>
<tbody>
<?php
$query = mysqli_query($dbcon, "SELECT * FROM employees");
//-- Loop through all records added to database table and display data
while ( $record = mysqli_fetch_assoc($query) ) {
?>
<tr>
<td><?php echo $record["fullname"];?></td>
<td><?php echo $record["email"];?></td>
<td><?php echo $record["designation"];?></td>
<td><?php echo $record["gender"];?></td>
</tr>
<?php
}
?>
</tbody>
</table>
</div>
</div>
</body>
</html>
style.css
*{
box-sizing: border-box;
}
html,body{
margin: 0px;
}
body{
background: #f0f0f0;
font: normal normal 14px Open Sans,Verdana, Arial;
}
.main-container {
max-width: 1024px;
margin: 0px auto;
}
.section:first-child{
padding: 15px;
background: #fff;
}
.form-row:before {
content: "";
display: table;
}
.form-row {
margin-left: -10px;
margin-right: -10px;
margin-bottom: 10px;
}
.col-4{
width: 33.33%;
float: left;
padding: 0px 10px;
}
.alert-red {
background: #e65442;
border: 1px solid #cc3a28;
width: 100%;
padding: 10px;
margin-bottom: 10px;
color: #fff;
transition: opacity 0.5s;
}
.btn-green {
background: #336611;
border: 1px solid #225500;
color: #fff;
display: inline-block;
padding: 5px 10px;
cursor: pointer;
float: right;
}
.table {
border: 1px solid #dddddd;
width: 100%;
border-collapse: collapse;
margin: 5px 0px;
background: #fff;
}
.table, .table > thead > tr > th,
.table > thead > tr > td,
.table > tbody > tr > th,
.table > tbody > tr > td{
border: 1px solid #dddddd;
}
.table td {
padding: 5px 10px;
}
.table th {
text-align: left;
padding: 10px;
}