Import file csv in php

by Vincy. Last modified on July 8th, 2022.

Are you searching for data-migration code from a CSV to MySQL database? Here you go!

There are many ways to implement CSV import. We have seen one or two examples of it previously. Even, this article gives two examples on CSV to MySQL import.

About two examples:

1. A quick example to simply importing data from an existing CSV file.
2. An interactive featured code to choose CSV via form and process CSV import.

This simple code imports CSV to MySQL database using PHP. All it needs is a source CSV file and a target database. See the prerequisites to experiment with this example in your environment.

Quick Example

CSV to MySQL PHP import

prepare("INSERT INTO tbl_users (userName, firstName, lastName) VALUES (?, ?, ?)");
    $stmt->bind_param("sss", $row[1], $row[2], $row[3]);
    $stmt->execute();
}
?>

Prerequisites:

The below blocks contain the prerequisites of running the CSV to MySQL quick example.

It displays source data in CSV format and a database structure to have the tbl_users table.

(1) Source CSV file

1,kevin_tom,Kevin,Thomas
2,vincy,Vincy,Jone
3,tim_lee,Tim,Lee
4,jane,Jane,Ferro

(2) Target MySQL database

CREATE TABLE `tbl_users` (
  `id` int(11) NOT NULL,
  `userName` varchar(255) NOT NULL,
  `firstName` varchar(255) NOT NULL,
  `lastName` varchar(255) NOT NULL,
  `create_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
);
ALTER TABLE `tbl_users`
  ADD PRIMARY KEY (`id`);

ALTER TABLE `tbl_users`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

Steps to implement importing CSV to MySQL

1. Create a target database to store CSV records.
2. Design import form and handle validation.
3. Develop PHP file reading endpoint to import CSV to MySQL database.

Example 2: Full-fledge and featured CSV to MySQL import

Let us see this elaborate example by following the above three steps. It shows the code created to achieve each step to implement the CSV to MySQL import.

This example uses the same database as its target to import the CSV data.

HTML form to choose CSV source

This HTML form allows users to choose the CSV source file. It accepts CSV and Excel files to read data to import.

index.php (Import Form)

	

		Coose your file. Download
				template 
		

			Import
				CSV and Save Data
		
	

CSS and jQuery validation script

The above form calls the validation script on submit. The validation script is in jQuery to check if the file input is not empty. It is in the head section of the same index.php page.

index.php (Validation)

and the styles are,

style.css

body {
	font-family: Arial;
	width: 550px;
	font-size: 0.9em;
	margin: 60px auto;
}

.heading {
	text-align: center;
}

.form-container {
	border: #e0dfdf 1px solid;
	padding: 30px 30px 10px 30px;
	border-radius: 15px;
	margin: 10px auto;
	width: 350px;
	text-align: center;
}

.input-row {
	margin-top: 0px;
	margin-bottom: 20px;
}

.btn-submit {
	background: #efefef;
	border: #d3d3d3 1px solid;
	width: 100%;
	border-radius: 20px;
	cursor: pointer;
	padding: 12px;
}

.btn-submit:hover {
	background: #d9d8d8;
	border: #c3c1c1 1px solid;
}

.outer-container table {
	border-collapse: collapse;
	width: 100%;
}

.outer-container th {
	border-top: 2px solid #dddddd;
	background: #f9f9f9;
	padding: 8px;
	text-align: left;
	font-weight: normal;
}

.outer-container td {
	border-top: 1px solid #dddddd;
	padding: 8px;
	text-align: left;
}

.outer-container label {
	margin-bottom: 5px;
	display: inline-block;
}

#response {
	padding: 10px;
	border-radius: 15px;
}

.success {
	background: #c7efd9;
	border: #bbe2cd 1px solid;
}

.error {
	background: #fbcfcf;
	border: #f3c6c7 1px solid;
}

.file {
	border: 1px solid #cfcdcd;
	padding: 10px;
	border-radius: 20px;
	color: #171919;
	width: 100%;
	margin-bottom: 20px;
}

CSV to MySQL import in PHP

On submitting the form, the PHP code triggers the CSV read handler. The readUserRecords() function of the UserModel class is doing this CSV parsing.

index.php (Triggering CSV read in PHP)

readUserRecords();
}
?>

and the UserModel class contains the functions to do the following.

• Read CSV data using PHP fgetcsv()
• Verify CSV to ignore empty rows.
• Read all imported data from the MySQL database.

This model class connects the database in its constructor and set the connection object.

UserModel.php

conn = new DataSource();
    }

    function getAllUser()
    {
        $sqlSelect = "SELECT * FROM users";
        $result = $this->conn->select($sqlSelect);
        return $result;
    }

    function readUserRecords()
    {
        $fileName = $_FILES["file"]["tmp_name"];
        if ($_FILES["file"]["size"] > 0) {
            $file = fopen($fileName, "r");
            $importCount = 0;
            while (($column = fgetcsv($file, 10000, ",")) !== FALSE) {
                if (! empty($column) && is_array($column)) {
                    if ($this->hasEmptyRow($column)) {
                        continue;
                    }
                    if (isset($column[1], $column[3], $column[4])) {
                        $userName = $column[1];
                        $password = $column[2];
                        $firstName = $column[3];
                        $lastName = $column[4];
                        $insertId = $this->insertUser($userName, $password, $firstName, $lastName);
                        if (! empty($insertId)) {
                            $output["type"] = "success";
                            $output["message"] = "Import completed.";
                            $importCount ++;
                        }
                    }
                } else {
                    $output["type"] = "error";
                    $output["message"] = "Problem in importing data.";
                }
            }
            if ($importCount == 0) {
                $output["type"] = "error";
                $output["message"] = "Duplicate data found.";
            }
            return $output;
        }
    }

    function hasEmptyRow(array $column)
    {
        $columnCount = count($column);
        $isEmpty = true;
        for ($i = 0; $i < $columnCount; $i ++) {
            if (! empty($column[$i]) || $column[$i] !== '') {
                $isEmpty = false;
            }
        }
        return $isEmpty;
    }

    function insertUser($userName, $password, $firstName, $lastName)
    {
        $sql = "SELECT userName FROM users WHERE userName = ?";
        $paramType = "s";
        $paramArray = array(
            $userName
        );
        $result = $this->conn->select($sql, $paramType, $paramArray);
        $insertId = 0;
        if (empty($result)) {
            $hashedPassword = password_hash($password, PASSWORD_DEFAULT);
            $sql = "INSERT into users (userName,password,firstName,lastName)
                       values (?,?,?,?)";
            $paramType = "ssss";
            $paramArray = array(
                $userName,
                $hashedPassword,
                $firstName,
                $lastName
            );
            $insertId = $this->conn->insert($sql, $paramType, $paramArray);
        }
        return $insertId;
    }
}
?>

List imported data from the database

This is for closing the CSV to MySQL import process loop. With this step, the user experiences that the import is completed successfully.

It shows the imported data on the screen. Instead of showing a static message like ‘Imported successfully’, this will give a good user experience.

list.php

getAllUser();
if (! empty($result)) {
    ?>
Imported records:

	

		

			
User Name
			
First Name
			
Last Name
		
	

    

		

			

			

			

		
                    
    

Output: CSV to MySQL import

↑ Back to Top

How do I import and export CSV using php and MySQL?

How do I read a csv file in column wise in php?