How to Import CSV File into MySQL Database using PHP/MySQLi

Submitted by: 
Language: 
Visitors have accessed this post 1544 times.

Getting Started

I've used bootstrap to improve the design of presentation of this tutorial. This bootstrap is included in the downloadable of this tutorial but, if you want, you may download bootstrap using this link.

Creating our Database

Next, we create our MySQL database.

I've included a .sql file in the downloadable of this tutorial which is a mysql database file. All you have to do is import the said file. If you have no idea on how to do this, please refer to my tutorial, How import .sql file to restore MySQL database.

You should be able to create a database with tables named mydatabase.

Creating our Form

Next, we're going to create our upload form. Also, for you to check the uploaded data, I've included the table that contains the uploaded data as well.

Create a new php file and name it as index.php and paste the below codes.

  1. <!DOCTYPE html>
  2. <html>
  3. <head>
  4. <meta charset="utf-8">
  5. <title>Import CSV File into MySQL Database using PHP/MySQLi</title>
  6. <link rel="stylesheet" type="text/css" href="bootstrap/css/bootstrap.min.css">
  7. </head>
  8. <body>
  9. <div class="container">
  10. <h1 class="page-header text-center">Import CSV File into MySQL Database</h1>
  11. <div class="row">
  12. <div class="col-sm-3">
  13. <h3>Import File Form</h3>
  14. <form method="POST" action="import.php" enctype="multipart/form-data">
  15. <div class="form-group">
  16. <label for="file">File:</label>
  17. <input type="file" id="file" name="file">
  18. </div>
  19. <button type="submit" name="import" class="btn btn-primary btn-sm">Import</button>
  20. </form>
  21. <?php
  22. if(isset($_SESSION['message'])){
  23. ?>
  24. <div class="alert alert-info text-center" style="margin-top:20px;">
  25. <?php echo $_SESSION['message']; ?>
  26. </div>
  27. <?php
  28.  
  29. unset($_SESSION['message']);
  30. }
  31.  
  32. ?>
  33. </div>
  34. <div class="col-sm-9">
  35. <table class="table table-bordered table-striped">
  36. <thead>
  37. <th>UserID</th>
  38. <th>Firstname</th>
  39. <th>Lastname</th>
  40. <th>Address</th>
  41. </thead>
  42. <tbody>
  43. <?php
  44. //connection
  45. $conn = new mysqli('localhost', 'root', '', 'mydatabase');
  46.  
  47. $sql = "SELECT * FROM members";
  48. $query = $conn->query($sql);
  49.  
  50. while($row = $query->fetch_array()){
  51. ?>
  52. <tr>
  53. <td><?php echo $row['id']; ?></td>
  54. <td><?php echo $row['firstname']; ?></td>
  55. <td><?php echo $row['lastname']; ?></td>
  56. <td><?php echo $row['address']; ?></td>
  57. </tr>
  58. <?php
  59. }
  60.  
  61. ?>
  62. </tbody>
  63. </table>
  64. </div>
  65. </div>
  66. </div>
  67. </body>
  68. </html>

Creating our Import Script

Lastly, we create our upload/import data script.

Create a new file and name it as import.php and paste the below codes on the file.

  1. <?php
  2.  
  3. //connection
  4. $conn = new mysqli('localhost', 'root', '', 'mydatabase');
  5.  
  6. if(isset($_POST['import'])){
  7. //check if input file is empty
  8. if(!empty($_FILES['file']['name'])){
  9. $filename = $_FILES['file']['tmp_name'];
  10. $fileinfo = pathinfo($_FILES['file']['name']);
  11.  
  12. //check file extension
  13. if(strtolower($fileinfo['extension']) == 'csv'){
  14. //check if file contains data
  15. if($_FILES['file']['size'] > 0){
  16.  
  17. $file = fopen($filename, 'r');
  18.  
  19. while(($impData = fgetcsv($file, 1000, ',')) !== FALSE){
  20. $sql = "INSERT INTO members (firstname, lastname, address) VALUES ('".$impData[0]."', '".$impData[1]."', '".$impData[2]."')";
  21. $query = $conn->query($sql);
  22.  
  23. if($query){
  24. $_SESSION['message'] = "Data imported successfully";
  25. }
  26. else{
  27. $_SESSION['message'] = "Cannot import data. Something went wrong";
  28. }
  29. }
  30. header('location: index.php');
  31.  
  32. }
  33. else{
  34. $_SESSION['message'] = "File contains empty data";
  35. header('location: index.php');
  36. }
  37. }
  38. else{
  39. $_SESSION['message'] = "Please upload CSV files only";
  40. header('location: index.php');
  41. }
  42. }
  43. else{
  44. $_SESSION['message'] = "File empty";
  45. header('location: index.php');
  46. }
  47.  
  48. }
  49.  
  50. else{
  51. $_SESSION['message'] = "Please import a file first";
  52. header('location: index.php');
  53. }
  54.  
  55. ?>

That ends this tutorial.

P.S. If you have no .csv file to test, I've included a test.csv file in the downloadable on this tutorial that you can use.

Also, if you have an excel file instead of csv file, just save the file as a new .csv file.

Happy Coding :)


Comments

Hi i would like to know how can i change both lines that shows
$conn = new mysqli('localhost', 'root', '', 'mydatabase');

use one single database php file? Should i replace the above line with the one below instead? Thanks for your help

$conn = new mysqli 'database.php';

Add new comment

Filtered HTML

  • Web page addresses and e-mail addresses turn into links automatically.
  • You may insert videos with [video:URL]
  • Allowed HTML tags: <a> <em> <strong> <cite> <blockquote> <code> <ul> <ol> <li> <dl> <dt> <dd> <table> <tr> <td> <th> <img> <h1> <h2> <h3> <iframe> [video]
  • You can enable syntax highlighting of source code with the following tags: <code>, <blockcode>, <asp>, <c>, <cpp>, <csharp>, <css>, <html4strict>, <java>, <javascript>, <mysql>, <php>, <python>, <sql>, <vb>, <vbnet>. The supported tag styles are: <foo>, [foo].
  • Lines and paragraphs break automatically.

Plain text

  • No HTML tags allowed.
  • Lines and paragraphs break automatically.
CAPTCHA
This question is for testing whether or not you are a human visitor and to prevent automated spam submissions.