loading

How to Import and Export CSV File using PHP and MySQL

How to Import and Export CSV File using PHP and MySQL

How to Import and Export CSV File using PHP and MySQL

0 Sales

Free

The Import and Export tool is really helpful in the data management part. The Import feature enables the user to transfer and enter numerous data sets into the database. Bulk data may be imported into the database with a single click using the Import tool. The export feature enables to download the table data list and also save it to a file for later use. Multiple records can be transferred in a file format using the Export function.

The CSV file format is commonly used in online applications to import and export data. CSV (comma-separated values) files hold data in plain text format and aid in data transfer between programmes. Using PHP and MySQL, you can quickly implement import and export features with a CSV file. Importing CSV file data into a database and exporting data to a CSV file are both possible with PHP and MySQL. In this article, we'll teach you how to use PHP and MySQL to import and export CSV file data into a database.

Create Database Table

A field in the data must be built to keep the person's data. In the Database server, the below SQL generates an users table with certain basic columns.

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

The CSV file should have the following fields based on the database table structure: Name, Email, Phone, and Status. To import CSV file data into the database, the CSV file format will look like the screen below.


When you export data to a CSV file, the downloadable structure will look like this.

Database Configuration (dbConfig.php)

To connect to the database, use dbConfig.php. As per your MySQL database credentials, provide the database host ($dbHost), username ($dbUsername), password ($dbPassword), and name ($dbName).

<?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);
}

CSV File Upload and Download (index.php)

Initially, the data of the members is presented as an HTML table with import and export options.

 - Existing member information is retrieved from the database and shown in a tabular fashion.
 - At the top of the list is an Import button.
      - When you click the Import button, a CSV file upload form will display.
      - When the form is submitted, the data from the CSV file is imported into the database using the importData.php file.
      - formToggle() — This JavaScript function is used to show/hide the CSV upload form when the Import button is clicked.
 - If the CSV file import request has already been sent, the status message from the URL is obtained and the import status is shown on the web page.

 - At the top of the list is an Export button.

      - The Export link takes you to the exportData.php file, which allows you to export table data to a CSV file.

<?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>


This sample code styles the HTML Table, Form, and Links with the Bootstrap 4 module. If you do not want to use the Bootstrap framework, you may leave it out. Alternatively, incorporate the Bootstrap library and custom CSS files (if any).

<!-- Bootstrap library -->
<link rel="stylesheet" href="assets/bootstrap/bootstrap.min.css">

<!-- Stylesheet file -->
<link rel="stylesheet" href="assets/css/style.css">

Import CSV Data to Database (importData.php)

The importData.php file is in charge of the CSV file uploading and data import procedure using PHP and MySQL.

 - Check to see if the provided file is a genuine CSV file.
 - Using the PHP is uploaded file() method, you may determine the status of a CSV file upload.
 - Using the PHP fopen() function, open the CSV file.
 - Using the PHP fgetcsv() function, parse data from a CSV file.
 - Based on the member's email address, insert or update data in the database.

<?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);

Export Data to CSV (exportData.php)

The data export procedure is handled by the exportData.php file, which uses PHP and MySQL.

 - Get the data from the database.
 - Using the PHP fopen() function, create and open a file in writing-only mode.
 - Set the header columns, format it as CSV, then send it to the opened file with the PHP fputcsv() method.
 - Data from the database should be output, formatted as CSV, and saved to a file.

 - Forcing the browser to download data as CSV in a file.

Conclusion

Using PHP and MySQL, this example script demonstrates how to quickly add the import and export functionality to the data list. The export and import capability is a wonderful way to make the data management area more user-friendly. You may also customise our import CSV file and export data to CSV scripts with PHP and MySQL to meet your specific requirements.

LICENSE OF USE

You can use it for personal or commercial projects. You can't resell it partially or in this form.

PRODUCT INFO

Create Date : Feb 12, 2022

Updated Date : Feb 12, 2022

Ratings

Comments : 0

Downloads : 0