Welcome to a quick tutorial on how to import CSV files into an MYSQL database in PHP. Want to allow administrators to upload their own set of data into the database? Yes, it is actually pretty easy to do in PHP.
To import a CSV file into the database with PHP:
- We need to have a properly structured database table and CSV file.
- Optionally, an HTML file upload form.
- A PHP script to read the CSV file and import it into the database.
That covers the basic steps, let us walk through an actual example in this guide, read on!
ⓘ I have included a zip file with all the example source code at the start of this tutorial, so you don’t have to copy-paste everything… Or if you just want to dive straight in.
QUICK SLIDES
TABLE OF CONTENTS
DOWNLOAD & NOTES
First, here is the download link to the example source code as promised.
QUICK NOTES
- Create a test database and import
1a-users.sql
. - Change the database settings in
3-import.php
to your own. - Access
2-upload.html
in the browser and upload1b-users.csv
.
EXAMPLE CODE DOWNLOAD
Click here to download the source code, I have released it under the MIT license, so feel free to build on top of it or use it in your own project.
IMPORT CSV INTO DATABASE
All right, let us now get into the example of how to import a CSV file in PHP and MySQL.
STEP 1) DUMMY USERS TABLE & CSV
USERS TABLE
CREATE TABLE `users` (
`user_id` int(11) NOT NULL,
`user_email` varchar(255) NOT NULL,
`user_name` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
ALTER TABLE `users`
ADD PRIMARY KEY (`user_id`),
ADD UNIQUE KEY `user_email` (`user_email`),
ADD KEY `user_name` (`user_name`);
ALTER TABLE `users`
MODIFY `user_id` int(11) NOT NULL AUTO_INCREMENT;
For this example, we will be using a dummy users table. Pretty straightforward with only 3 fields –
user_id
ID, primary key.user_email
The user’s email.user_name
The user’s name.
USERS CSV
jane@doe.com | Jane Doe |
joe@doe.com | Joe Doe |
john@doe.com | John Doe |
That’s right… Just a whole bunch of user emails and names.
STEP 2) CSV FILE UPLOAD
<form action="3-import.php" method="post" enctype="multipart/form-data">
<input type="file" name="upcsv" accept=".csv" required/>
<input type="submit" value="Upload"/>
</form>
This is nothing but a very simple HTML file upload form – It only accepts CSV files. Actually, this is optional if you are getting the CSV from somewhere else – An automated script to fetch a CSV file from another server, the user has to manually copy to a secured drive, etc…
STEP 3) PHP IMPORT CSV
<?php
// (A) CONNECT TO DATABASE - CHANGE SETTINGS TO YOUR OWN!
$dbHost = "localhost";
$dbName = "test";
$dbChar = "utf8";
$dbUser = "root";
$dbPass = "";
try {
$pdo = new PDO(
"mysql:host=".$dbHost.";dbname=".$dbName.";charset=".$dbChar,
$dbUser, $dbPass, [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]
);
} catch (Exception $ex) { exit($ex->getMessage()); }
// (B) READ UPLOADED CSV
$fh = fopen($_FILES["upcsv"]["tmp_name"], "r");
if ($fh === false) { exit("Failed to open uploaded CSV file"); }
// (C) IMPORT ROW BY ROW
while (($row = fgetcsv($fh)) !== false) {
try {
// print_r($row);
$stmt = $pdo->prepare("INSERT INTO `users` (`user_name`, `user_email`) VALUES (?,?)");
$stmt->execute([$row[0], $row[1]]);
} catch (Exception $ex) { echo $ex->getmessage(); }
}
fclose($fh);
echo "DONE.";
Not going to explain this line-by-line, but it should be straightforward enough:
- Connect to the database. Remember to change the settings to your own.
- Try to open the uploaded CSV file.
- Finally, read and import the CSV file into the database row-by-row.
That’s all, a basic import is really that simple.
EXTRA) RECOMMENDATIONS & TIPS
<?php
// (A) SET TIME LIMIT (SECONDS)
set_time_limit(300);
// (B) DATA CHECK
while (($row = fgetcsv($fh)) !== false) {
if (!filter_var($row[1], FILTER_VALIDATE_EMAIL)) {
echo "INVALID EMAIL FORMAT - " . $row[1];
continue;
}
}
The above example will work well enough, but you can expect things to go wrong. A few common “errors”:
- The script will timeout on importing large datasets. We can “fix” this problem by setting the timeout with
set_time_limit(300)
. - The annoying “user error”, messing up the columns. For example, swapping the email and name columns… Simply add your own checks on the data while importing each-and-every row.
- Then, there is also the problem with impatient users. They couldn’t wait for the script to end, reload and import again, ending up with duplicate entries. Impose your own unique keys in the database to prevent duplicates.
USEFUL BITS & LINKS
That’s all for this tutorial, and here is a small section on some extras and links that may be useful to you.
LINKS & REFERENCES
- File Open – PHP
- File Get CSV – PHP
- Set Time Limit – PHP
- PDO (Database Object) – PHP
TUTORIAL VIDEO
INFOGRAPHIC CHEAT SHEET

THE END
Thank you for reading, and we have come to the end of this guide. I hope that it has helped you with your project, and if you want to share anything with this guide, please feel free to comment below. Good luck and happy coding!
Nice…
Very helpful …