How to import CSV File into MySQL using PHP and JavaScript.

by Varun Singh
2022-02-03

14762

This article will illustrate how to import CSV file into MySQL database using Php. Here user will import CSV file using Html form. According to this script functionality, the user would be able to upload a CSV file and CSV file data will be inserted into the MySQL database using PHP.

Download Script

You can download CSV file from here: Download

MySql Script to create users Table:

 CREATE TABLE `users` (
  `id` int(11) NOT NULL,
  `userId` int(8) NOT NULL,
  `userName` varchar(55) NOT NULL,
  `email` varchar(55) NOT NULL,
  `mobile` varchar(255) NOT NULL,
  `address` varchar(255) NOT NULL
);

ALTER TABLE `users`
  ADD PRIMARY KEY (`id`);

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

DataSource.php:

<?php

namespace Coderglass;


class DataSource
{

const HOST = 'localhost';

const USERNAME = 'username';

const PASSWORD = 'password';

const DATABASENAME = 'database name';

private $conn;

function __construct()
{
$this->conn = $this->getConnection();
}

public function getConnection()
{
$conn = new \mysqli(self::HOST, self::USERNAME, self::PASSWORD, 
self::DATABASENAME);

if (mysqli_connect_errno()) {
trigger_error("Problem with connecting to database.");
}

$conn->set_charset("utf8");
return $conn;
}

public function select($query, $paramType = "", $paramArray = array())
{
$stmt = $this->conn->prepare($query);

if (! empty($paramType) && ! empty($paramArray)) {

$this->bindQueryParams($stmt, $paramType, $paramArray);
}
$stmt->execute();
$result = $stmt->get_result();

if ($result->num_rows > 0) {
while ($row = $result->fetch_assoc()) {
$resultset[] = $row;
}
}

if (! empty($resultset)) {
return $resultset;
}
}


public function insert($query, $paramType, $paramArray)
{
$stmt = $this->conn->prepare($query);
$this->bindQueryParams($stmt, $paramType, $paramArray);

$stmt->execute();
$insertId = $stmt->insert_id;
return $insertId;
}


public function execute($query, $paramType = "", $paramArray = array())
{
$stmt = $this->conn->prepare($query);

if (! empty($paramType) && ! empty($paramArray)) {
$this->bindQueryParams($stmt, $paramType, $paramArray);
}
$stmt->execute();
}


public function bindQueryParams($stmt, $paramType, $paramArray = array())
{
$paramValueReference[] = & $paramType;
for ($i = 0; $i < count($paramArray); $i ++) {
$paramValueReference[] = & $paramArray[$i];
}
call_user_func_array(array(
$stmt,
'bind_param'
), $paramValueReference);
}


public function getRecordCount($query, $paramType = "", $paramArray = array())
{
$stmt = $this->conn->prepare($query);
if (! empty($paramType) && ! empty($paramArray)) {

$this->bindQueryParams($stmt, $paramType, $paramArray);
}
$stmt->execute();
$stmt->store_result();
$recordCount = $stmt->num_rows;

return $recordCount;
}
}

?>

index.php:

<?php
use Coderglass\DataSource;

require_once 'DataSource.php';
$db = new DataSource();
$conn = $db->getConnection();

if (isset($_POST["import"])) {

$fileName = $_FILES["file"]["tmp_name"];


if ($_FILES["file"]["size"] > 0) {

$file = fopen($fileName, "r");

while (($column = fgetcsv($file, 10000, ",")) !== FALSE) {

$userId = "";
if (isset($column[0])) {
$userId = mysqli_real_escape_string($conn, $column[0]);
}
$userName = "";
if (isset($column[1])) {
$userName = mysqli_real_escape_string($conn, $column[1]);
}
$email = "";
if (isset($column[2])) {
$email = mysqli_real_escape_string($conn, $column[2]);
}

$mobile = "";
if (isset($column[1])) {
$userName = mysqli_real_escape_string($conn, $column[3]);
}
$address = "";
if (isset($column[2])) {
$email = mysqli_real_escape_string($conn, $column[4]);
}


$sqlInsert = "INSERT into users (userId,userName,email,mobile,address)
   values (?,?,?,?,?)";
$paramType = "issss";
$paramArray = array(
$userId,
$userName,
$email,
$mobile,
$address
);
$insertId = $db->insert($sqlInsert, $paramType, $paramArray);

if (! empty($insertId)) {
$type = "success";
$message = "CSV Data Imported into the Database";
} else {
$type = "error";
$message = "Problem in Importing CSV Data";
}
}
}
}
?>



<!DOCTYPE html>
<html>

<head>
<script src="jquery-3.2.1.min.js"></script>

<style>
body {
    font-family: Arial;
    width: 550px;
}

.btn-submit {
    background: #333;
    border: #1d1d1d 1px solid;
    color: #f0f0f0;
    font-size: 0.9em;
    width: 100px;
    border-radius: 2px;
    cursor: pointer;
}

#response {
    padding: 10px;
    margin-bottom: 10px;
    border-radius: 2px;
    display: none;
}

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

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

div#response.display-block {
    display: block;
}
</style>
<script type="text/javascript">
$(document).ready(function() {
$("#cvsImport").on("submit", function () {

$("#response").attr("class", "");
$("#response").html("");
var fileType = ".csv";
var regex = new RegExp("([a-zA-Z0-9\s_\\.\-:])+(" + fileType + ")$");
if (!regex.test($("#file").val().toLowerCase())) {
$("#response").addClass("error");
$("#response").addClass("display-block");
$("#response").html("Invalid File. Upload : <b>" + fileType + "</b> Files.");
return false;
}
return true;
});
});
</script>
</head>

<body>
<h2>Import CSV file into Mysql using PHP</h2>

<div id="response"
class="<?php if(!empty($type)) { echo $type . " display-block"; } ?>">
<?php if(!empty($message)) { echo $message; } ?>
</div>

<form class="form-horizontal" action="" method="post"
name="cvsImport" id="cvsImport" enctype="multipart/form-data">

<label class="col-md-4 control-label">Choose CSV File</label> 
<input type="file" name="file" id="file" accept=".csv">
<button type="submit" id="submit" name="import" class="btn-submit">Import</button>

</form>

</body>

</html>

Tags: Php Java Python React Html JavaScript Angular NodeJs JQuery

Varun Singh

Thank you for reading this article, and if you have any problem, have a another better useful solution about this article, please write message in the comment section. We will be very happy to hear that.

If you like my tutorial, please follow us on facebook, Insta, LinkedIn and Twitter.
I need your support to continue.