PHP Import CSV Into MYSQL Database (Simple Example)

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 – Let us walk through an example in this guide, read on!

 

 

TABLE OF CONTENTS

 

 

EXAMPLE CODE DOWNLOAD

Source code on GitHub Gist

Just click on “download zip” or do a git clone. I have released it under the MIT license, so feel free to build on top of it or use it in your own project.

 

SORRY FOR THE ADS...

But someone has to pay the bills, and sponsors are paying for it. I insist on not turning Code Boxx into a "paid scripts" business, and I don't "block people with Adblock". Every little bit of support helps.

Buy Me A Coffee Code Boxx eBooks

 

 

 

TUTORIAL VIDEO

 

STEP 1) DUMMY DATABASE & CSV

1A) USERS TABLE

CREATE TABLE `users` (
  `user_id` bigint(20) NOT NULL,
  `user_email` varchar(255) NOT NULL,
  `user_name` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

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` bigint(20) NOT NULL AUTO_INCREMENT;

For this example, we will be working with this dummy users table. Very straightforward with only 3 fields –

• user_id ID, primary key.
• user_email The user’s email.
• user_name The user’s name.

 

1B) 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>

Next, we have a simple HTML file upload form that only accepts CSV files.

 

STEP 3) PHP IMPORT CSV INTO MYSQL

<?php
// (A) CONNECT TO DATABASE - CHANGE SETTINGS TO YOUR OWN!
$dbHost = "localhost";
$dbName = "test";
$dbChar = "utf8mb4";
$dbUser = "root";
$dbPass = "";
$pdo = new PDO(
  "mysql:host=".$dbHost.";dbname=".$dbName.";charset=".$dbChar,
  $dbUser, $dbPass, [
  PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
  PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
]);
 
// (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) {
  // print_r($row);
  $stmt = $pdo->prepare("INSERT INTO `users` (`user_email`, `user_name`) VALUES (?,?)");
  $stmt->execute([$row[0], $row[1]]);
}
fclose($fh);
echo "DONE.";

Finally, the PHP script to import the uploaded CSV file.

1. Connect to the database. Remember to change the settings to your own.
2. Try to open the uploaded CSV file.
3. Read and import the CSV file into the database row-by-row.

The end.

 

 

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[0], FILTER_VALIDATE_EMAIL)) {
    echo "INVALID EMAIL FORMAT - " . $row[1];
    continue;
  }
}

The above example will work well enough, but always expect things to go wrong. Here are a few common “possible errors” and tips on handling them:

• PHP scripts will timeout when importing a large CSV file, a simple “fix” is to set a longer timeout with set_time_limit(SECONDS). The advanced way is to run the import script in the background – See the links below.
• Users will sometimes mess up the CSV data. For example, by swapping the email/name columns and leaving empty rows. As above, check every cell before you import them. Make sure it is a valid email, not an empty cell, etc…
• Then, there is also the problem of impatient users who will reload and import again – Ending up with duplicate entries. Impose your own unique keys in the database to prevent duplicates.

 

 

 

LINKS & REFERENCES

• File Open – PHP
• File Get CSV – PHP
• Set Time Limit – PHP
• PDO (Database Object) – PHP
• How to Run PHP Scripts In The Background – Code Boxx