Today we talk about a very important feature of the data management system is Import and Export CSV file in PHP script from front-end. Using Import action you can save bulk data on the database using your website. And on the Export user can download the complete database table data for their external use.
Download complete code from below link
Step by step process of Import and Export CSV file in PHP
On this we use .csv (comma-separated values) format for import and export operation. In this task, the import and export script, the following functionality will be implemented.
- Fetch users from the database and listed on the web page.
- Import and Export CSV file in 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 on PHPMYADMIN SQL tab and click on GO.
|
1 2 3 4 5 6 7 8 9 10 11 |
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,
You can see the database table data which is imported through the CSV, after importing the CSV in the database using PHP and MySQL.
Complete source code of Import and Export CSV file in PHP
Database Configuration (db.php)
On the database configuration file, we create connection and specify the database username, host, database name and database password.
|
1 2 3 4 5 6 7 |
<?php // Database configuration $con = mysqli_connect("localhost","root","password","impexp"); ?> |
On this above database configuration code, mysqli_connect is function and the first localhost is the host of the server, second is database username, third is database password and the last parameter is database Name.
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 |
<strong>//bootstarp library</strong> <script src="ajax.googleapis.com/ajax/libs/jquery/1.11.1/jquery.min.js"></script> <script src="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/js/bootstrap.min.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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 |
<?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 operation checks of the export process using PHP and MySQL.
- Create and open a file with writing-only mode using the 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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 |
<?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(); |
This script helps you to integrate the Import and Export CSV file in PHP and MySQL in the database. If you have any query please comment below.
Check Out: jQuery autocomplete ajax with Text and Image
[sociallocker]Click And Download Complete Source[/sociallocker]
Happy Coding..!
Was this article helpful?
YesNo
Visitor Rating: 5 Stars
Excellent tutorial.I have a wordpress site. This tutorial is really helpful our website.
[…] Import and Export CSV file in PHP […]