Categories: CSVMySqlPHP

Import Data from CSV File to MySQL Using PHP

Do you want to import data from CSV file to MySql Database using PHP?

Probably every PHP developer needs to deal with CSV file import (we also called it as a comma separated data). So basically developer needs get the data from the CSV file and insert into the MySQL database.

In this tutorial I am going to help to import data easily from CSV file using a very basic PHP script

Let’s start with the basic example:

Sample CSV File to Import:

Here we are importing users from `users.csv` file, take a look at below file, which is having basic details of users like Name, Mobile Number and Email address.

of course this is just a demo, you can use your own file to import with any format or number of columns just make sure to focus on each column accordingly

let go to the next step:

Create MySQL Sample Database and required Table for this example:

We have our CSV file ready with record set, now we need to create MySQL database table to store the records, go ahead and create table using following SQL query:

CREATE TABLE  `users` (
`id` INT( 11 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`name` VARCHAR( 100 ) NOT NULL ,
`mobile` VARCHAR( 20 ) NOT NULL ,
`email` VARCHAR( 150 ) NOT NULL
) ENGINE = MYISAM ;
Users Table Structure

Create PHP MySQL Database connection File:

Create database connection file called `db_connection.php` to connect MySQL.

Use following code to and make sure to update connection variables with your server configuration

<?php
/*
* iTech Empires:  How to Import Data from CSV File to MySQL Using PHP Script
* Version: 1.0.0
* Page: DB Connection
*/
// Connection variables
$host = "localhost"; // MySQL host name eg. localhost
$user = "root"; // MySQL user. eg. root ( if your on local server)
$password = ""; // MySQL user password  (if password is not set for your root user then keep it empty )
$database = "database"; // MySQL Database name

// Connect to MySQL Database
$con = new mysqli($host, $user, $password, $database);

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

PHP Script to Import CSV File Data:

Create `import.php` file: this is the user interface where we are going to have feature to select and submit file to the server.

`import.php:`

<?php
/*
* iTech Empires: How to Import Data from CSV File to MySQL Using PHP Script
* Version: 1.0.0
* Page: Import.PHP
*/

// Database Connection
require 'db_connection.php';

$message = "";
if (isset($_POST['submit'])) {
$allowed = array('csv');
$filename = $_FILES['file']['name'];
$ext = pathinfo($filename, PATHINFO_EXTENSION);
if (!in_array($ext, $allowed)) {
// show error message
$message = 'Invalid file type, please use .CSV file!';
} else {

move_uploaded_file($_FILES["file"]["tmp_name"], "files/" . $_FILES['file']['name']);

$file = "files/" . $_FILES['file']['name'];

$query = <<<eof
LOAD DATA LOCAL INFILE '$file'
INTO TABLE users
FIELDS TERMINATED BY ','
LINES TERMINATED BY 'n'
IGNORE 1 LINES
(name,mobile,email)
eof;
if (!$result = mysqli_query($con, $query)) {
exit(mysqli_error($con));
}
$message = "CSV file successfully imported!";
}
}

// View records from the table
$users = '<table class="table table-bordered">
<tr>
<th>No</th>
<th>Name</th>
<th>Mobile</th>
<th>Email</th>
</tr>
';
$query = "SELECT * FROM users";
if (!$result = mysqli_query($con, $query)) {
exit(mysqli_error($con));
}
if (mysqli_num_rows($result) > 0) {
$number = 1;
while ($row = mysqli_fetch_assoc($result)) {
$users .= '<tr>
<td>' . $number . '</td>
<td>' . $row['name'] . '</td>
<td>' . $row['mobile'] . '</td>
<td>' . $row['email'] . '</td>
</tr>';
$number++;
}
} else {
$users .= '<tr>
<td colspan="4">Records not found!</td>
</tr>';
}
$users .= '</table>';
?>
<!doctype html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Import Data from CSV File to MySQL Tutorial</title>
<!-- Bootstrap CSS File -->
<link rel="stylesheet" type="text/css" href="bootstrap/css/bootstrap.min.css"/>
</head>
<body>
<div class="container">
<h2>
Tutorial: How to Import Data from CSV File to MySQL Using PHP
</h2>
<br><br>

<div class="row">
<div class="col-md-6 col-md-offset-0">
<form enctype="multipart/form-data" method="post" action="import.php">
<div class="form-group">
<label >

We are done, try to run your code or you can easily download tutorial code from following links. let me know if you find any difficulty running the code or getting any issues.

If you want to export data from MySQL to CSV file you learn from here - Export Data from MySQL to CSV using PHP

Yogesh Koli

Software engineer & Blogger lives in India, has 6+ years of experience working with the front-end and back-end web app development.

View Comments

  • is there any script to Upload csv file to DB and it has to check duplicate fields

Recent Posts

Complete guide of using Laravel 6 Eloquent Subquery Enhancements

Learn How to use laravel frameworks new improved feature called Eloquent Subquery and get example of using Eloquent Subqueries and…

4 months ago

3 Useful examples of using Array Map function in PHP – Best Practices

Learn how to use php array map function with easy and essential tutorial to modify arrays in php here I…

4 months ago

Working with PHP Array Filter Function – Best Practices

Learn how to use php array filter function with easy and essential tutorial to filter array in php here I…

4 months ago

How to add Access Modifiers with Constructor Parameters in TypeScript

Want to know how to refactor your Typescript class, Learn here utilising Typescript of the best and essential feature that…

4 months ago

What is Access Modifiers and how to use Access Modifiers in TypeScript ?

What is Access Modifiers in typescript, how to use Access Modifiers, when to use them, what are the advantage of…

5 months ago

Top 10 Super Useful Packages to Improve Laravel applications in 2019

This tutorial provide ultimate list of package those are top 10 on packagist and super useful to optimize your laravel…

5 months ago