How to Import and Export Data in CSV using PHP?
When we talk about data management, no one can ignore the importance of Import and Export features. The Import functionality enables users to upload multiple data in the database, meaning transferring bulk data in the database with a single click.
At the same time, Export functionality enables the download of the table data list and saves it for offline use. The feature allows users to download multiple records in a single file. There are many file formats available, some of which may support your system, while others may not. In such a situation, there arises a question – Is it possible to import and export data in CSV using PHP? The answer is Yes!
Mostly, CSV file format is used in the web app for the import and export of data. CSV files, abbreviated for Comma-separated Values, often store data in plain text form. Therefore, users can easily import and export data in CSV using PHP and MySQL. In this tutorial, we will show you the stepwise process of importing and exporting CSV file data using PHP. So, let’s get started!
Step-by-Step Process to import and Export Data in CSV using PHP
Here are quick steps to get started with the process of how to import and export data in CSV using PHP:
Step 1: Create Database Table
You will first need to create a table in the database to store the member’s data. The following command will help you create a member table in the MySQL database.
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;
Step 2: Organize the Data in the Table
The next step is to organize the data in the relevant fields based on the database structure. You may have to create fields like – Name, Email, Phone, and Status. The data should be well-organized to shorten the process.
Step 3: Database Configuration (dbConfig.php)
You will now need to configure your data by using dbConfig.php to connect the database. Specify the host ($dbHost), name ($dbName) username ($dbUsername), and password ($dbPassword) according to your credentials.
<?php
// Database configuration
$dbHost = "localhost";
$dbUsername = "root";
$dbPassword = "root";
$dbName = "codexworld";
// Create database connection
$db = new mysqli($dbHost, $dbUsername, $dbPassword, $dbName);
// Check connection
if ($db->connect_error) {
die("Connection failed: " . $db->connect_error);
}
Step 4: Download and Upload CSV Files (index.php)
The HTML table displays the data of the members that are stored in the database with Import and Export options.
- A button labeled Import is located above the table. When the user clicks on this button, a form appears to upload a CSV file. The user can select a CSV file and submit the form to importData.php, which will insert the data from the file into the database. The function formToggle() in JavaScript is used to toggle the visibility of the upload form when the user clicks on the Import button. The function also checks if there is any status message in the URL, which indicates the result of the import operation. The status message is shown on the web page if it exists.
- Another button labeled Export is also located above the table. This button links to exportData.php, which will export the data from the table to a CSV file and download it to the user’s device.
<?php
// Load the database configuration file
include_once 'dbConfig.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-12">
<div class="alert <?php echo $statusType; ?>"><?php echo $statusMsg; ?></div>
</div>
<?php } ?>
<div class="row">
<!-- 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 -->
<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 = $db->query("SELECT * FROM members ORDER BY id DESC");
if($result->num_rows > 0){
while($row = $result->fetch_assoc()){
?>
<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>
<!-- 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>
Step 5: Import Data from CSV File to Database (importData.php)
The file importData.php handles uploading the CSV file and importing the data into the database using PHP.
Validate the CSV file.
Check if it was uploaded successfully using the PHP function is_uploaded_file().
Open the CSV file with the PHP function fopen() and read the data from it with the PHP function fgetcsv().
Insert or Update the data in the database based on the email of the member.
<?php
// Load the database configuration file
include_once 'dbConfig.php';
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 = $db->query($prevQuery);
if($prevResult->num_rows > 0){
// Update member data in the database
$db->query("UPDATE members SET name = '".$name."', phone = '".$phone."', status = '".$status."', modified = NOW() WHERE email = '".$email."'");
}else{
// Insert member data in the database
$db->query("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);
Step 6: Export Data to CSV (exportData.php)
The file exportData.php handles the CSV file data exporting to the database using PHP.
Fetch the data record from the database.
Open the CSV file using the PHP function fopen().
Set Header columns using the PHP function fputcsv().
Output the data format as CSV and write it to a file.
Now, download the data in CSV format.
<?php
// Load the database configuration file
include_once 'dbConfig.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 = $db->query(“SELECT * FROM members ORDER BY id DESC”);
if($result->num_rows > 0){
// Output each row of the data, format line as csv and write to file pointer
while($row = $result->fetch_assoc()){
$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();
Conclusion
Through this tutorial, we have tried to simplify the process of importing and exporting data in CSV using PHP with our example script. The import and export functionalities are a great option for managing data in the database. Hope with this tutorial, you have gained a good grasp of the topic.
