Import and Export CSV file in PHP

  • Fetch users from the database and listed on the web page.
  • Import CSV file data using PHP and MySQL.
  • Export or download database table data on CSV format using PHP and MySQL.

Create a database table

To store the user’s data we have to create a table on MySQL database. The below MySQL code create the table named members. Copy the whole code and paste it onPHPMYADMIN SQL tab and click on GO.

CREATE TABLE `members` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
`email` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
`phone` varchar(15) COLLATE utf8_unicode_ci NOT NULL,
`created` datetime NOT NULL,
`modified` datetime NOT NULL,
`status` enum('Active','Inactive') COLLATE utf8_unicode_ci NOT NULL DEFAULT 'Active',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CSV File Format

When you start the import operation on the web page your CSV file looks like this,

Database Configuration (db.php)

On the database configuration file, we create connection and specify the database username, host, database name and database password.

<?php
// Database configuration
$con = mysqli_connect("localhost","root","password","impexp");
?>

CSV file Import and Export (index.php)

On this, there is an HTML tabular format where imported data shows and from there you can do below-listed operations.

  • Existing data shows on the HTML table.
  • An import button used to import CSV file whit these available database fields.
  • On the import operation, after clicking the import button, the form is submitted to importData.php file.
  • We use formToggle() function to show or hide the import form.
  • On the page, if the data is already imported than success message is showing.
  • On above the table there is an export button also which is used for download the imported data.
//bootstarp library
<script src="//ajax.googleapis.com/ajax/libs/jquery/1.11.1/jquery.min.js"></script>
<script src="http://demos.codexworld.com/includes/js/bootstrap.js"></script>
<link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/css/bootstrap.min.css">
<link rel="stylesheet" href="style.css">
<?php// Load the database configuration file
include 'db.php';
?>
<!-- Show/hide CSV upload form --><script>
function formToggle(ID){
var element = document.getElementById(ID);
if(element.style.display === "none"){
element.style.display = "block";
}else{
element.style.display = "none";
}
}
</script>
<?php
// Get status message
if(!empty($_GET['status'])){
switch($_GET['status']){
case 'succ':
$statusType = 'alert-success';
$statusMsg = 'Members data has been imported successfully.';
break;
case 'err':
$statusType = 'alert-danger';
$statusMsg = 'Some problem occurred, please try again.';
break;
case 'invalid_file':
$statusType = 'alert-danger';
$statusMsg = 'Please upload a valid CSV file.';
break;
default:
$statusType = '';
$statusMsg = '';
}
}
?>
<!-- Display status message -->
<?php if(!empty($statusMsg)){ ?>
<div class="col-xs-8 container">
<div class="alert <?php echo $statusType; ?>"><?php echo $statusMsg; ?></div>
</div>
<?php } ?>
<div class="row container">
<!-- Import & Export link -->
<div class="col-md-12 head">
<div class="float-right">
<a href="javascript:void(0);" class="btn btn-success" onclick="formToggle('importFrm');"><i class="plus"></i> Import</a>
<a href="exportData.php" class="btn btn-primary"><i class="exp"></i> Export</a>
</div>
</div>
<!-- CSV file upload form -->
<div class="col-md-12" id="importFrm" style="display: none;">
<form action="importData.php" method="post" enctype="multipart/form-data">
<input type="file" name="file" />
<input type="submit" class="btn btn-primary" name="importSubmit" value="IMPORT">
</form>
</div>
<!-- Data list table -->
<div class="col-md-12">
<table class="table table-striped table-bordered">
<thead class="thead-dark">
<tr>
<th>#ID</th>
<th>Name</th>
<th>Email</th>
<th>Phone</th>
<th>Status</th>
</tr>
</thead>
<tbody>
<?php
// Get member rows
$result = mysqli_query($con,"SELECT * FROM members ORDER BY id DESC");
if(mysqli_num_rows($result) > 0){
while($row = mysqli_fetch_assoc($result)){
?>
<tr>
<td><?php echo $row['id']; ?></td>
<td><?php echo $row['name']; ?></td>
<td><?php echo $row['email']; ?></td>
<td><?php echo $row['phone']; ?></td>
<td><?php echo $row['status']; ?></td>
</tr>
<?php } }else{ ?>
<tr><td colspan="5">No member(s) found...</td></tr>
<?php } ?>
</tbody>
</table>
</div>
</div>

Import CSV file to database (importData.php)

The importData.php file has all the operation check of importing the CSV file using PHP and MySQL.

  • Check the CSV file is valid or not.
  • Check the upload status of CSV file using PHP is_uploaded_file() function.
  • Using PHP fopen() function, we open the CSV file.
  • Fetch data from the CSV file using PHP fgetcsv() function.
<?php
// Load the database configuration file
include 'db.php';
// print_r($_POST);
if(isset($_POST['importSubmit'])){
// Allowed mime types
$csvMimes = array('text/x-comma-separated-values', 'text/comma-separated-values', 'application/octet-stream', 'application/vnd.ms-excel', 'application/x-csv', 'text/x-csv', 'text/csv', 'application/csv', 'application/excel', 'application/vnd.msexcel', 'text/plain');
// Validate whether selected file is a CSV file
if(!empty($_FILES['file']['name']) && in_array($_FILES['file']['type'], $csvMimes)){
// If the file is uploaded
if(is_uploaded_file($_FILES['file']['tmp_name'])){
// Open uploaded CSV file with read-only mode
$csvFile = fopen($_FILES['file']['tmp_name'], 'r');
// Skip the first line
fgetcsv($csvFile);
// Parse data from CSV file line by line
while(($line = fgetcsv($csvFile)) !== FALSE){
// Get row data
$name = $line[0];
$email = $line[1];
$phone = $line[2];
$status = $line[3];
// Check whether member already exists in the database with the same email
$prevQuery = "SELECT id FROM members WHERE email = '".$line[1]."'";
$prevResult = mysqli_query($con,$prevQuery);
if(mysqli_num_rows($prevResult) > 0){
// Update member data in the database
mysqli_query($con,"UPDATE members SET name = '".$name."', phone = '".$phone."', status = '".$status."', modified = NOW() WHERE email = '".$email."'");
}else{
// Insert member data in the database
mysqli_query($con,"INSERT INTO members (name, email, phone, created, modified, status) VALUES ('".$name."', '".$email."', '".$phone."', NOW(), NOW(), '".$status."')");
}
}
// Close opened CSV file
fclose($csvFile);
$qstring = '?status=succ';
}else{
$qstring = '?status=err';
}
}else{
$qstring = '?status=invalid_file';
}
}
// Redirect to the listing page
header("Location:index.php".$qstring);

Export table Data as CSV (exportData.php)

The exportData.php file has all the opertion check of export process using PHP and MySQL.

  • Create and open a file with writing-only mode using PHP fopen() function.
  • Fetch the data from the database.
  • Set header columns, CSV format and opened file using PHP fputcsv() function.
  • Force browser to download data as CSV format in a file.
<?php
// Load the database configuration file
include 'db.php';
$filename = "members_" . date('Y-m-d') . ".csv";
$delimiter = ",";
// Create a file pointer
$f = fopen('php://memory', 'w');
// Set column headers
$fields = array('ID', 'Name', 'Email', 'Phone', 'Created', 'Status');
fputcsv($f, $fields, $delimiter);
// Get records from the database
$result = mysqli_query($con,"SELECT * FROM members ORDER BY id DESC");
if(mysqli_num_rows($result) > 0){
// Output each row of the data, format line as csv and write to file pointer
while($row = mysqli_fetch_assoc($result)){
$lineData = array($row['id'], $row['name'], $row['email'], $row['phone'], $row['created'], $row['status']);
fputcsv($f, $lineData, $delimiter);
}
}
// Move back to beginning of file
fseek($f, 0);
// Set headers to download file rather than displayed
header('Content-Type:text/csv');
header('Content-Disposition:attachment;filename="'.$filename.'";');
// Output all remaining data on a file pointer
fpassthru($f);
// Exit from file
exit();

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Bikash Panda

Bikash Panda

4 Followers

I am a web application developer. FB Community: @mrpandatech, Twitter: @phpcodertech My PHP problem-solving site http://phpcoder.tech