PHP Script to Import CSV File Data into a MySQL Table

Handling large volumes of data is a common task in web development. Whether you’re migrating user data, updating the product catalog, or analyzing logs, very often you need an efficient way to import CSV into MySQL. Manually adding thousands of rows is far from practical and too error-prone. Fortunately, you can automate this entire process with a Import CSV to MySQL Database.

A CSV, or Comma-Separated Values, is a plain text file in which values are separated by commas; it becomes a universal format to exchange data between different applications like Microsoft Excel or Google Sheets. For instance, a marketing team might export a list of new customer sign-ups from a spreadsheet into a CSV file. Our goal is to take that file and populate our database without any manual effort.

Setting Up Your MySQL Table for CSV Import

You will need to have a MySQL table where the data from your CSV file will be stored. The structure of the table, as you have probably guessed, should correspond to the columns in your CSV file. For this example, let’s assume we are importing a list of users with a name, email, and phone number. Here is the SQL query to create a users table:

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

Creating the HTML File Upload Form

Now, we’ll create an HTML form in which the user can select a CSV file to upload. Store this code in a file called index.php. This form is necessary to post the file to our PHP script on the server:

<form action="" method="post" enctype="multipart/form-data">
 <input type="file" name="file" />
 <input type="submit" name="importSubmit" value="IMPORT">
</form>

Importing CSV file Data In PHP Script

Now for the main part. We’ll add the PHP code to the same index.php file. This script will handle the file upload, connect to the database, read the data from the CSV, and insert it into the users table. In this example, we will take user input as file, connect to database, read and import CSV file to MySQL database.

<?php
// Database configuration
$dbHost = 'localhost';
$dbUsername = 'root';
$dbPassword = '';
$dbName = 'test';

// Create a database connection
$db = new mysqli($dbHost, $dbUsername, $dbPassword, $dbName);

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

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);
            
            while(($line = fgetcsv($csvFile)) !== FALSE){
                $name = $line[0];
                $email = $line[1];
                $phone = $line[2];
                
                $prevQuery = "SELECT id FROM users WHERE email = '".$line[1]."'";
                $prevResult = $db->query($prevQuery);
                
                if($prevResult->num_rows > 0){
                    $db->query("UPDATE users SET name = '".$name."', phone = '".$phone."', modified = NOW() WHERE email = '".$email."'");
                }else{
                    $db->query("INSERT INTO users (name, email, phone, created, modified) VALUES ('".$name."', '".$email."', '".$phone."', NOW(), NOW())");
                }
            }
            
            fclose($csvFile);
            
            $qstring = '?status=succ';
        }else{
            $qstring = '?status=err';
        }
    }else{
        $qstring = '?status=invalid_file';
    }
}
?>

It will read CSV file row by row and then insert data to table using SQL query. At end of script it will show status.

Conclusion

You have now developed a complete solution using PHP to import CSV into MySQL. This script has automated an otherwise time-consuming process, thus saving you a lot of time and reducing the possibility of human error. Checking for existing entries further makes the script robust to run multiple times.