Hi, There! We have an another concept of comma separated values file’s data insertion. Many times, we have to store a large amount of data in database,but it becomes a very infeasible task as it consumes a large amount of time. So, to automate this task, many website provides a facility to store a bulk data by simply uploading a csv file and the data is then stored efficiently. We’ll here see how the data can be import from the files to database and export from database to files.
What is csv file?
It stands for “Comma Separated Values”. It is nothing but an excel sheet in which the data is stored in rows and columns.
This tutorial will show both importing data from csv to database and exporting the data from database to csv. So lets start by creating the database named csv_import_export and run query as follows.
CREATE TABLE IF NOT EXISTS `users` ( `id` int(11) NOT NULL AUTO_INCREMENT, `full_name` varchar(100) COLLATE utf8_unicode_ci NOT NULL, `email` varchar(100) COLLATE utf8_unicode_ci NOT NULL, `mobile` varchar(40) COLLATE utf8_unicode_ci NOT NULL, `age` int(11) NOT NULL, PRIMARY KEY (`id`) )
Create connection using config.php as follows. We are using a PDO connection method in php here. Because it is a secure and prevents sql injections.
<?php
// PDO connection method
function connect() {
$host = 'localhost';
$db_name = 'csv_import_export';
$db_user = 'root';
$db_password = '';
return new PDO('mysql:host='.$host.';dbname='.$db_name, $db_user, $db_password, array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));
}
?>
The values are assigned to the variables and they are used in the PDO function. Make sure you have replaced the values of hostname, username, database name and passwords with your ones.
Now create main file as index.php and add the following code in it.
<?php
// including the config file
include('config.php');
$pdo = connect();
// select all members
$sql = 'SELECT * FROM users ORDER BY id ASC';
$query = $pdo->prepare($sql);
$query->execute();
$list = $query->fetchAll();
?>
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Import CSV to MySQL and Export from MySQL to CSV in PHP</title>
<link rel="stylesheet" href="css/style.css" />
<script type="text/javascript" src="js/jquery.min.js"></script>
<script type="text/javascript" src="js/script.js"></script>
</head>
<body>
<div class="container">
<div class="header">
<img src="images/technopoints.png" alt="Technopoints" />
</div><!-- header -->
<h1 class="main_title">Import and Export CSV from MySQL in PHP</h1>
<div class="content">
<fieldset class="field_container align_right">
<legend> <img src="images/arrow.gif"> Operations</legend>
<span class="import" onclick="show_popup('popup_upload')">Import CSV</span>
<a href="export.php" class="export">Export CSV</a>
</fieldset>
<fieldset class="field_container">
<legend> <img src="images/arrow.gif"> Users list </legend>
<div id="list_container">
<table class="table_list" cellspacing="2" cellpadding="0">
<tr class="bg_h">
<th>Full name</th>
<th>Email</th>
<th>Age</th>
</tr>
<?php
$bg = 'bg_1';
foreach ($list as $rs) {
?>
<tr class="<?php echo $bg; ?>">
<td><?php echo $rs['full_name']; ?></td>
<td><?php echo $rs['email']; ?></td>
<td><?php echo $rs['age']; ?></td>
</tr>
<?php
if ($bg == 'bg_1') {
$bg = 'bg_2';
} else {
$bg = 'bg_1';
}
}
?>
</table>
</div><!-- list_container -->
</fieldset>
</div><!-- content -->
<div class="footer">
Tutorial on <a href="https://technopoints.co.in/">Technopoints</a>
</div><!-- footer -->
</div><!-- container -->
<!-- The popup for upload a csv file -->
<div id="popup_upload">
<div class="form_upload">
<span class="close" onclick="close_popup('popup_upload')">x</span>
<h2 class="dialog">Upload CSV file</h2>
<form action="import.php" method="post" enctype="multipart/form-data">
<input type="file" name="csv_file" id="csv_file" class="file_input">
<input type="submit" value="Upload file" id="upload_btn">
</form>
</div>
</div>
</body>
</html>
This file contains the front end view and a form is inserted. The dialog box opens to upload and download files by clicking on the respective links.
You’ll see the following view of index.php.
However, the views will not work for now. We’ll make it work by coding further. So, continue…
Import comma separated values file
Now create file import.php and paste following code in it. This is the main configuration code used for importing the data into the database.
<?php
// including the config file
include('config.php');
$pdo = connect();
$csv_file = $_FILES['csv_file']['tmp_name'];
if (is_file($csv_file)) {
$input = fopen($csv_file, 'a+');
// if the csv file contain the table header leave this line
$row = fgetcsv($input, 1024, ','); // here you got the header
while ($row = fgetcsv($input, 1024, ',')) {
// insert into the database
$sql = 'INSERT INTO users(full_name, email, age) VALUES(:full_name, :email, :age)';
$query = $pdo->prepare($sql);
$query->bindParam(':full_name', $row[1], PDO::PARAM_STR);
$query->bindParam(':email', $row[2], PDO::PARAM_STR);
$query->bindParam(':age', $row[3], PDO::PARAM_INT);
$query->execute();
}
}
// redirect to the index page
header('location: index.php');
?>
Export csv file
Lets code export.php as follows. The below code snippet will be used for exporting the data form the database and put into rows and columns.
<?php
// including the config file
include('config.php');
$pdo = connect();
// set headers to force download on csv format
header('Content-Type: text/csv; charset=utf-8');
header('Content-Disposition: attachment; filename=users.csv');
// we initialize the output with the headers
$output = "id,full_name,email,age\n";
// select all members
$sql = 'SELECT * FROM users ORDER BY id ASC';
$query = $pdo->prepare($sql);
$query->execute();
$list = $query->fetchAll();
foreach ($list as $rs) {
// add new row
$output .= $rs['id'].",".$rs['full_name'].",".$rs['email'].",".$rs['age']."\n";
}
// export the output
echo $output;
exit;
?>
You’ve seen a styling in the above screenshots. Here’s how it is done using style.css as follows.
* {
margin: 0;
padding: 0;
}
body {
padding: 10px;
background: #eaeaea;
text-align: center;
font-family: arial;
font-size: 12px;
color: #333333;
}
.container {
width: 1000px;
height: auto;
background: #ffffff;
border: 1px solid #cccccc;
border-radius: 10px;
margin: auto;
text-align: left;
}
.header {
padding: 10px;
}
.main_title {
background: #B91CF7;
color: #EED02E;
padding: 10px;
font-size: 20px;
line-height: 20px;
}
.content {
padding: 10px;
min-height: 100px;
}
.footer {
padding: 10px;
text-align: right;
}
.footer a {
color: #999999;
text-decoration: none;
}
.footer a:hover {
text-decoration: underline;
}
/* Operations ************/
.align_right {
text-align: right;
}
.import {
display: inline-block;
background: url('../images/import.png') no-repeat left;
background-size: auto 100%;
line-height: 20px;
padding-left: 25px;
color: #F78825;
font-size: 14px;
font-weight: bold;
text-decoration: none;
margin-right: 20px;
cursor: pointer;
}
.export {
display: inline-block;
background: url('../images/export.png') no-repeat left;
background-size: auto 100%;
line-height: 20px;
padding-left: 25px;
color: #F78825;
font-size: 14px;
font-weight: bold;
text-decoration: none;
}
.import:hover, .export:hover {
text-decoration: underline;
color: #2F0FF1;
}
.field_container {
padding: 10px;
margin: 0 0 10px 0;
border: 1px solid #cccccc;
border-radius: 10px;
}
.field_container legend {
padding: 0 5px 0 5px;
font-size: 14px;
font-weight: bold;
}
/* users list ************/
.table_list {
width: 100%;
border: 0;
}
.table_list td, .table_list th {
padding: 2px;
}
.delete_m {
color: #666666;
text-decoration: none;
font-weight: bold;
}
.delete_m:hover {
color: #999999;
}
.delete_m img {
height: 12px;
}
.bg_h {
background: #2F0FF1;
color: #ffffff;
text-align: center;
}
.bg_1 {
background: #DCD8F5;
text-align: center;
}
.bg_2 {
background: #BBB2F2;
text-align: center;
}
#list_container {
text-align: center;
}
/* popup --------------------------*/
#popup_upload {
position: fixed;
width: 100%;
height: 100%;
top: 0;
left: 0;
background: rgba(0, 0 ,0, 0.7);
z-index: 99;
text-align: center;
display: none;
overflow: auto;
}
.form_upload {
width: 300px;
height: 140px;
border: 1px solid #999999;
border-radius: 10px;
background: #ffffff;
color: #666666;
margin: auto;
margin-top: 160px;
padding: 10px;
text-align: left;
position: relative;
}
.form_upload h2 {
border-bottom: 1px solid #999999;
padding: 0 0 5px 0;
margin: 0 0 20px 0;
}
.file_input {
width: 97%;
background: #eaeaea;
border: 1px solid #999999;
border-radius: 5px;
color: #333333;
padding: 1%;
margin: 0 0 20px 0;
}
#upload_btn {
background: #2F0FF1;
color: #FFFFFF;
border: 1px solid #999999;
border-radius: 10px;
float: right;
line-height: 20px;
font-size: 14px;
font-weight: bold;
font-family: arial;
display: block;
padding: 5px;
cursor: pointer;
}
#upload_btn:hover {
box-shadow: 0 8px 16px 0 rgba(0,0,0,0.2), 0 6px 20px 0 rgba(0,0,0,0.19);
}
.close {
position: absolute;
display: block;
right: 10px;
cursor: pointer;
font-size: 20px;
line-height: 16px;
width: 18px;
height: 18px;
border: 1px solid #cccccc;
border-radius: 5px;
background: #F0F0F0;
text-align: center;
font-weight: bold;
}
.close:hover {
background: #cccccc;
color: #F00F0F
}
.dialog {
color: #2F0FF1;
}
That’s it! We’ve finished our coding part. You can see the dialog box for choosing files as follows. Give it a try and feel free to use the code in your projects.
Now your code should be working fine. Thanks for reading this tutorial.
For additional JavaScript files and source files, you can download the source code from the below link.
You can directly download the full working source code from the below link.
Download Source code
Please provide your feedback in the comments section below.
1 thought on “Import and export csv file in database using php tutorial”