Import CSV File Data into MySQL Database using PHP

A CSV (comma-separated values) file stores the tabular data in plain text format. Mostly, the CSV file format is used to import or export data in the web application. Each line of the CSV file is a data record that consists of one or more fields. When there is needed to add huge data into the MySQL database, it will very time-consuming to add data one by one. In that situation, the import feature helps to insert a bunch of data in the database with a single click.

Bulk Import is a very useful feature to add multiple records in the database without insert manually. Using the CSV file you can store the data and import the CSV file data into the database at once using PHP and MySQL. Import CSV into MySQL helps to save the user time and avoid repetitive work. In this tutorial, we will show you how to upload CSV file and import data from CSV file to MySQL database using PHP.

In the example script, we will import the member’s data from a CSV file and insert it into the database using PHP and MySQL. According to this script functionality, the user can upload a CSV file that holds member records, and import bulk members data in the MySQL database using PHP.

Create Database Table

To store the member’s records, a table is required in the database. The following SQL creates a members table with some basic fields in the MySQL database.

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

CSV File Format

Based on the database table structure, the CSV file should have the following fields:

1. Name
2. Email
3. Phone
4. Status (1 or 0)

To import the data from a CSV file, the format must be similar to the following screen.

Database Configuration (dbConfig.php)

The dbConfig.php is used to connect and select the database. Specify the database host ($dbHost), username ($dbUsername), password ($dbPassword), and name ($dbName) as per your MySQL database 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);
}

CSV File Upload (index.php)

Initially, the existing member’s records are listed with CSV file import option.

• Existing member’s data are fetched from the database and listed in a tabular format.
• An Import button is placed at the top of the HTML table data list.
• By clicking the Import button, an HTML form will appear to select and upload a CSV file.
• On submission, the form is submitted to the importData.php file for importing the CSV data to the database.
• formToggle() – It is a JavaScript function that helps to Show/Hide the CSV upload form. This function is triggered on click event of the Import button.

If the form has already been submitted, the submission status is retrieved from SESSION and displayed on the web page.

<?php 
// Start session 
if(!session_id()){ 
    session_start(); 
} 
 
// Load the database configuration file 
include_once 'dbConfig.php'; 
 
// Get status message 
if(!empty($_SESSION['response'])){ 
    $status = $_SESSION['response']['status']; 
    $statusMsg = $_SESSION['response']['msg']; 
    unset($_SESSION['response']); 
} 
?>

<!-- Display status message -->
<?php if(!empty($statusMsg)){ ?>
<div class="col-xs-12">
    <div class="alert alert-<?php echo $status; ?>"><?php echo $statusMsg; ?></div>
</div>
<?php } ?>

<div class="row">
    <!-- Import link -->
    <div class="col-md-12 head">
        <div class="float-end">
            <a href="javascript:void(0);" class="btn btn-primary" onclick="formToggle('importFrm');"><i class="plus"></i> Import</a>
        </div>
    </div>
    <!-- CSV file upload form -->
    <div class="col-md-12" id="importFrm" style="display: none;">
        <form action="importData.php" method="post" class="row g-2 float-end" enctype="multipart/form-data">
            <div class="col-auto">
                <input type="file" name="file" class="form-control" required/>

                <!-- Link to download sample format -->
                <p class="text-start mb-0 mt-2">
                    <a href="sample-csv-members.csv" class="link-primary" download>Download Sample Format</a>
                </p>
            </div>
            <div class="col-auto">
                <input type="submit" class="btn btn-success mb-3" name="importSubmit" value="Import CSV">
            </div>
        </form>
    </div>

    <!-- Data list table --> 
    <table class="table table-striped table-bordered">
        <thead class="table-dark">
            <tr>
                <th>ID</th>
                <th>Name</th>
                <th>Email</th>
                <th>Phone</th>
                <th>Status</th>
            </tr>
        </thead>
        <tbody>
        <?php 
        // Fetch member records from database 
        $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'] == 1?'Active':'Inactive'; ?></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>

Import CSV Data to Database (importData.php)

The importData.php file handles the file upload and CSV data import operations using PHP and MySQL.

• Validate the posted file whether it is a valid .csv file.
• Check whether the CSV file is uploaded using is_uploaded_file() function.
• Open the CSV file in read-only mode using fopen() function.
• Read and Parse data from the opened CSV file using fgetcsv() function.
• Retrieve the CSV data line by line.
• Insert/Update member data in the database based on the email address.
• Store CSV data importing status in SESSION with PHP.
• Redirect to the listing page.

<?php 
 
// Start session 
if(!session_id()){ 
    session_start(); 
} 
 
// Load the database configuration file 
include_once 'dbConfig.php'; 
 
$res_status = $res_msg = ''; 
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'); 
     
    // 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){ 
                $line_arr = !empty($line)?array_filter($line):''; 
                if(!empty($line_arr)){ 
                    // Get row data 
                    $name   = trim($line_arr[0]); 
                    $email  = trim($line_arr[1]); 
                    $phone  = trim($line_arr[2]); 
                    $status = trim($line_arr[3]); 
                     
                    // Check whether member already exists in the database with the same email 
                    $prevQuery = "SELECT id FROM members WHERE email = '".$email."'"; 
                    $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); 
             
            $res_status = 'success'; 
            $res_msg = 'Members data has been imported successfully.'; 
        }else{ 
            $res_status = 'danger'; 
            $res_msg = 'Something went wrong, please try again.'; 
        } 
    }else{ 
        $res_status = 'danger'; 
        $res_msg = 'Please select a valid CSV file.'; 
    } 
 
    // Store status in SESSION 
    $_SESSION['response'] = array( 
        'status' => $res_status, 
        'msg' => $res_msg 
    ); 
} 
 
// Redirect to the listing page 
header("Location: index.php"); 
exit(); 
 
?>

Export Data to CSV File using PHP and MySQL

Conclusion

Here, we have tried to make the CSV import process simple. The example code will help you to implement the import functionality in the web application. You can easily enhance our CSV Import to MySQL script functionality, to add more fields or restrictions on data import based on your requirements. If you are looking to export data from Excel file, use this script – Import Excel File Data into MySQL Database using PHP