How To Import CSV File In MySQL Using PHP

In this article, we will see how to import CSV files in MySQL using PHP. Here, we will learn about importing CSV files into MySQL database in PHP. For this example, we will use fgetcsv() function. The fgetcsv() function parses a line from an open file, checking for CSV fields.

So, we will create a database after that configure a database connection then we will create an HTML form for importing CSV files to the database.

So, let's see import CSV file in PHP, how to import CSV file into MySQL, import CSV file data into MySQL database using PHP, and how to import a large CSV file in MySQL using PHP.

Step 1: Create User Table

In this step, we will create a user table. So, add the following code and run the below code.

CREATE TABLE `users` (
 `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, 
 `created_at` datetime NOT NULL,
 `updated_at` datetime NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

 

Step 2: Database Configuration

 Now, we will create a config.php file and add the database configuration.

<?php
$host = "localhost";
$user = "root";
$password = "password";
$dbname = "import_csv_example";

$con = mysqli_connect($host, $user, $password, $dbname);

if (!$con) {
 die("Connection failed: " . mysqli_connect_error());
}

 

 

Step 3: Create HTML Form For Upload CSV File

In this step, we will create an HTML form for uploading a CSV file. So, add the following code to the index.php file.

<!doctype html>
<html lang="en">
    <head>
        <meta charset="utf-8">
        <meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">  
        <link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.5.2/css/bootstrap.min.css">
        <title>How To Import CSV File In MySQL Using PHP - Techsolutionstuff</title>
        <style>
            .custom-file-input.selected:lang(en)::after {
                content: "" !important;
            }
        
            .custom-file {
                overflow: hidden;
            }
        
            .custom-file-input {
                white-space: nowrap;
            }
        </style>
    </head>
    <body>
        <div class="container">
            <form action="upload.php" method="post" enctype="multipart/form-data">
                <div class="input-group">
                    <div class="custom-file">
                        <input type="file" class="custom-file-input" id="customFileInput" name="file">
                        <label class="custom-file-label" for="customFileInput">Select File:</label>
                    </div>
                    <div class="input-group-append">
                        <input type="submit" name="submit" value="upload" class="btn btn-primary">
                    </div>
                </div>
            </form>
        </div>
    </body>
</html>

 

Step 4: Create PHP File For Import the CSV File Into the Database

In this step, we will create an upload.php file. It is used to read CSV files and insert all CSV file data into MySQL database and also we will include a config.php file. So, add the following code.

<?php

include_once 'config.php';
 
if (isset($_POST['submit']))
{
     
    $fileMimes = 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 selected file is a CSV file or not
    if (!empty($_FILES['file']['name']) && in_array($_FILES['file']['type'], $fileMimes))
    {
 
        // 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 (($getData = fgetcsv($csvFile, 10000, ",")) !== FALSE)
        {
            // Get row data
            $name = $getData[0];
            $email = $getData[1];            

            // If user already exists in the database with the same email
            $query = "SELECT id FROM users WHERE email = '" . $getData[1] . "'";

            $check = mysqli_query($con, $query);

            if ($check->num_rows > 0)
            {
                mysqli_query($conn, "UPDATE users SET name = '" . $name . "', created_at = NOW() WHERE email = '" . $email . "'");
            }
            else
            {
                mysqli_query($con, "INSERT INTO users (name, email, created_at, updated_at) VALUES ('" . $name . "', '" . $email . "', NOW(), NOW())");
            }
        }

        // Close opened CSV file
        fclose($csvFile);

        header("Location: index.php");         
    }
    else
    {
        echo "Please select valid file";
    }
}

 


You might also like:

RECOMMENDED POSTS

FEATURE POSTS