How to Import CSV Data into MySQL Using PHP?

Introduction :

CSV (Comma Separated Values) is a popular file format used for storing plain text data. It is widely used for importing and exporting data between different programs. In this guide, we will walk you through the steps to import CSV data into a MySQL database using PHP. This tutorial will provide you with a comprehensive understanding of the process, ensuring you can seamlessly manage your data.

Step 1: Create the Database Table :

First, you need to create a table in your MySQL database to store the data from the CSV file. Use the following SQL query to create a members table :

				
					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 : Create the Database Config File

Create a dbconfig.php file to handle the database connection :

				
					<?php
// Database configuration
const DB_HOST = "localhost";
const DB_USERNAME = "root";
const DB_PASSWORD = "";
const DB_NAME = "ImportTutorial";

// Create database connection
$db = new mysqli(DB_HOST, DB_USERNAME, DB_PASSWORD, DB_NAME);

// Check connection
if ($db->connect_error) {
    die("Connection failed: " . $db->connect_error);
}
?>

Step 3: Create the HTML Form for File Upload

Create an index.php file where users can upload their CSV files :

				
					<?php
// Include the database configuration file
require_once 'dbconfig.php';
?>

<!DOCTYPE html>
<html lang="en-US">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Import CSV Data into MySQL Using PHP</title>
    <link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/css/bootstrap.min.css">
</head>
<body>
<div class="container">
    <h2>Import CSV Data</h2>
    <?php if (!empty($_GET['status'])): ?>
        <div class="alert alert-<?php echo $_GET['status'] == 'succ' ? 'success' : 'danger'; ?>">
            <?php echo $_GET['status'] == 'succ' ? 'Data imported successfully.' : 'Some problem occurred, please try again.'; ?>
        </div>
    <?php endif; ?>
    <form action="importcsvfile.php" method="post" enctype="multipart/form-data">
        <div class="form-group">
            <input type="file" name="file" class="form-control" />
        </div>
        <div class="form-group">
            <input type="submit" class="btn btn-primary" name="importSubmit" value="IMPORT">
        </div>
    </form>
</div>
</body>
</html>

Step 4: Handle CSV File Import :

Create an importcsvfile.php file to handle the CSV import logic :

				
					<?php
require_once 'dbconfig.php';

if (isset($_POST['importSubmit'])) {
    $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'
    );

    if (!empty($_FILES['file']['name']) && in_array($_FILES['file']['type'], $csvMimes)) {
        if (is_uploaded_file($_FILES['file']['tmp_name'])) {
            $csvFile = fopen($_FILES['file']['tmp_name'], 'r');
            fgetcsv($csvFile); // Skip the first line

            while (($line = fgetcsv($csvFile)) !== false) {
                $name   = $line[0];
                $email  = $line[1];
                $phone  = $line[2];
                $status = $line[3];

                $prevQuery = "SELECT id FROM members WHERE email = '".$email."'";
                $prevResult = $db->query($prevQuery);

                if ($prevResult->num_rows > 0) {
                    $db->query("UPDATE members SET name = '".$name."', phone = '".$phone."', status = '".$status."', modified = NOW() WHERE email = '".$email."'");
                } else {
                    $db->query("INSERT INTO members (name, email, phone, created, modified, status) VALUES ('".$name."', '".$email."', '".$phone."', NOW(), NOW(), '".$status."')");
                }
            }

            fclose($csvFile);
            $qstring = '?status=succ';
        } else {
            $qstring = '?status=err';
        }
    } else {
        $qstring = '?status=invalid_file';
    }
}

header("Location: index.php".$qstring);
?>

Conclusion :

You have successfully learned how to import CSV data into a MySQL database using PHP. This process involves creating a database table, setting up the database configuration, creating a file upload form, and writing the import logic. With this knowledge, you can efficiently manage your data import tasks.

How to Import CSV Data into MySQL Using PHP?

How to Import CSV Data into MySQL Using PHP?