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 ;
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 for="file">Select .CSV file to Import</label>
<input name="file" type="file" class="form-control">
</div>
<div class="form-group">
<?php echo $message; ?>
</div>
<div class="form-group">
<input type="submit" name="submit" class="btn btn-primary" value="Submit"/>
</div>
</form>
<div class="form-group">
<?php echo $users; ?>
</div>
</div>
</div>
</div>
</body>
</html>
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
is there any script to Upload csv file to DB and it has to check duplicate fields
Warning: move_uploaded_file(files/users.csv): Failed to open stream: No such file or directory in C:xampphtdocsimportimport.php on line 21
Warning: move_uploaded_file(): Unable to move “C:xampptmpphp3B80.tmp” to “files/users.csv” in C:xampphtdocsimportimport.php on line 21
Warning: mysqli_query(): LOAD DATA LOCAL INFILE forbidden in C:xampphtdocsimportimport.php on line 33
LOAD DATA LOCAL INFILE is forbidden, check mysqli.allow_local_infile
Hello
thanks for the tutorials. I appreciate it.
I used both scripts for import csv and export csv.
Export works nice.
Import is stoped by error:
“The used command is not allowed with this MariaDB version”
It seems there is an security issue importing files from any places to the DB.
Since i am running on an shared hosting, i cant go and edit the mariadb my.cnf file and just open up the door for all websites. Is there an runtime option to allow the import temporarily during runtime?
Regards
Mark