Skip to main content

How to Import Excel/CSV file to MySQL Database using PHP

Screenshot
Body

In this tutorial im going to show you how to create n application that can be used to Import CSV/Excel file using PHP. We will create a simple PHP web application that has a form for our import feature and table to display our imported data to the database.

*Upgrade Info

The tutorial source code was originally written in PHP MySQL and I have updated it into PHP MySQLi.

To start with this project, please make sure that you have already installed a local web server that can run PHP script and MySQL Database such as "XAMPP" and "WAMP". Then also make sure that the "Apache" and "MySQL" is started (for XAMPP/WAMP, open the XAMPP/WAMP Control Panel and start the Apache and MySQL).

Now, we will create a Database in your PHPMyAdmin named “studentdb” then execute this SQL query to create a new table called "subject".

  1.  
  2. CREATE TABLE IF NOT EXISTS `subject` (
  3. `SUBJ_ID` INT(11) NOT NULL AUTO_INCREMENT,
  4. `SUBJ_CODE` VARCHAR(30) NOT NULL,
  5. `SUBJ_DESCRIPTION` VARCHAR(255) NOT NULL,
  6. `UNIT` INT(2) NOT NULL,
  7. `PRE_REQUISITE` VARCHAR(30) NOT NULL DEFAULT 'None',
  8. `COURSE_ID` INT(11) NOT NULL,
  9. `AY` VARCHAR(30) NOT NULL,
  10. `SEMESTER` VARCHAR(20) NOT NULL,
  11. PRIMARY KEY (`SUBJ_ID`)
  12. ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=500 ;

Then inside you htdocs or document root folder create a new folder and name it as “excel”. Next, we need to create a PHP file naming “db.php” that will hold our database connection and here’s the following code:

  1. <?php
  2. $conn=mysqli_connect("localhost","root","") or die("Could not connect");
  3. mysqli_select_db($conn,"studentdb") or die("could not connect database");
  4. ?>

Next, create another PHP file named “index.php”,and this PHP file will be the first page that will load to our browser when we will access/browse the “excel” folder from our web directory. And this file will load all the list of subjects if the subject table is not empty well as this page will allow the user to import the CSV/Excel file and upload the data to MySQL Database.
The page Interface will be look like the image below.

i1

And here’s the code for the “index.php” file:

  1. <!DOCTYPE html>
  2. <?php
  3. include 'db.php';
  4.  
  5. ?>
  6. <html lang="en">
  7. <head>
  8. <meta charset="utf-8">
  9. <title>Import Excel To MySQL Database Using PHP </title>
  10. <meta name="viewport" content="width=device-width, initial-scale=1.0">
  11. <meta name="description" content="Import Excel File To MySQL Database Using php">
  12.  
  13. <link rel="stylesheet" href="css/bootstrap.min.css">
  14. <link rel="stylesheet" href="css/bootstrap-responsive.min.css">
  15. <link rel="stylesheet" href="css/bootstrap-custom.css">
  16.  
  17.  
  18. </head>
  19. <body>
  20.  
  21. <!-- Navbar
  22. ================================================== -->
  23.  
  24. <div class="navbar navbar-inverse navbar-fixed-top">
  25. <div class="navbar-inner">
  26. <div class="container">
  27. <a class="btn btn-navbar" data-toggle="collapse" data-target=".nav-collapse">
  28. <span class="icon-bar"></span>
  29. <span class="icon-bar"></span>
  30. <span class="icon-bar"></span>
  31. </a>
  32. <a class="brand" href="#">Import Excel To MySQL Database Using PHP</a>
  33.  
  34. </div>
  35. </div>
  36. </div>
  37.  
  38. <div id="wrap">
  39. <div class="container">
  40. <div class="row">
  41. <div class="span3 hidden-phone"></div>
  42. <div class="span6" id="form-login">
  43. <form class="form-horizontal well" action="import.php" method="post" name="upload_excel" enctype="multipart/form-data">
  44. <fieldset>
  45. <legend>Import CSV/Excel file</legend>
  46. <div class="control-group">
  47. <div class="control-label">
  48. <label>CSV/Excel File:</label>
  49. </div>
  50. <div class="controls">
  51. <input type="file" name="file" id="file" class="input-large">
  52. </div>
  53. </div>
  54.  
  55. <div class="control-group">
  56. <div class="controls">
  57. <button type="submit" id="submit" name="Import" class="btn btn-primary button-loading" data-loading-text="Loading...">Upload</button>
  58. </div>
  59. </div>
  60. </fieldset>
  61. </form>
  62. </div>
  63. <div class="span3 hidden-phone"></div>
  64. </div>
  65.  
  66. <table class="table table-bordered">
  67. <thead>
  68. <tr>
  69. <th>ID</th>
  70. <th>Subject</th>
  71. <th>Description</th>
  72. <th>Unit</th>
  73. <th>Semester</th>
  74.  
  75.  
  76. </tr>
  77. </thead>
  78. <?php
  79. $SQLSELECT = "SELECT * FROM subject ";
  80. $result_set = mysqli_query($conn, $SQLSELECT);
  81. while($row = mysqli_fetch_array($result_set))
  82. {
  83. ?>
  84.  
  85. <tr>
  86. <td><?php echo $row['SUBJ_ID']; ?></td>
  87. <td><?php echo $row['SUBJ_CODE']; ?></td>
  88. <td><?php echo $row['SUBJ_DESCRIPTION']; ?></td>
  89. <td><?php echo $row['UNIT']; ?></td>
  90. <td><?php echo $row['SEMESTER']; ?></td>
  91.  
  92.  
  93. </tr>
  94. <?php
  95. }
  96. ?>
  97. </table>
  98. </div>
  99.  
  100. </div>
  101.  
  102. </body>
  103. </html>

Next, we’re going to create another PHP file naming “import.php” that we will use to process the data from CSV/Excel to MySQL Database. Here’s the following code:

  1. <?php
  2. include 'db.php';
  3. if(isset($_POST["Import"])){
  4.  
  5.  
  6. echo $filename=$_FILES["file"]["tmp_name"];
  7.  
  8.  
  9. if($_FILES["file"]["size"] > 0)
  10. {
  11.  
  12. $file = fopen($filename, "r");
  13. while (($emapData = fgetcsv($file, 10000, ",")) !== FALSE)
  14. {
  15.  
  16. //It wiil insert a row to our subject table from our csv file`
  17. $sql = "INSERT into subject (`SUBJ_CODE`, `SUBJ_DESCRIPTION`, `UNIT`, `PRE_REQUISITE`,COURSE_ID, `AY`, `SEMESTER`)
  18. values('$emapData[1]','$emapData[2]','$emapData[3]','$emapData[4]','$emapData[5]','$emapData[6]','$emapData[7]')";
  19. //we are using mysql_query function. it returns a resource on true else False on error
  20. $result = mysqli_query( $conn, $sql );
  21. if(! $result )
  22. {
  23. echo "<script type=\"text/javascript\">
  24. alert(\"Invalid File:Please Upload CSV File.\");
  25. window.location = \"index.php\"
  26. </script>";
  27.  
  28. }
  29.  
  30. }
  31. fclose($file);
  32. //throws a message if data successfully imported to mysql database from excel file
  33. echo "<script type=\"text/javascript\">
  34. alert(\"CSV File has been successfully Imported.\");
  35. window.location = \"index.php\"
  36. </script>";
  37.  
  38.  
  39.  
  40. //close of connection
  41. mysqli_close($conn);
  42.  
  43.  
  44.  
  45. }
  46. }
  47. ?>

After this, you can try it on your own, and I have attached with this sourcecode the example CSV/Excel file used for this tutorial, or you can also create a new CSV file to Import using below the below column orders.

ID Subject Code Subject Description Unit Pre Requisite Course ID AY (Academic Year) Semester

DEMO

That's it! That would be the end of our tutorial on Importing CSV/Excel File data to MySQL Database using PHP. By the way in this application I am using the Twitter bootstrap framework, you can download and use it like in this application.

If you want to see some new Source Code or Application and Tutorials Just click here.

Happy Coding :)

Comments

Submitted byFuran666 (not verified)on Tue, 04/22/2014 - 20:37

I adjusted the file to my needs (db connection, tables etc). It worked at first, yet it only uploaded a small part of my CSV. Now when I upload, it states that it doesn't recognize any CSV file (while i'm uploading one unedited). Any idea's?
Submitted byDavid C (not verified)on Sat, 10/04/2014 - 09:57

In reply to by Furan666 (not verified)

hello, you might solve the problem? I have the same problem and I could not fix it. Thank you
Submitted byanonymus (not verified)on Sat, 05/31/2014 - 13:51

wow...thank you so much..I think it will really help me in my project
Submitted bykatrine (not verified)on Fri, 07/04/2014 - 15:34

Do you have some tricks on how to customize importing csv file to database? Thanks,
Submitted byeduardo (not verified)on Mon, 07/21/2014 - 05:03

in the same code, how can we put an update function?
Submitted byMatthew (not verified)on Wed, 08/20/2014 - 01:23

thank you guys, hope this is the best article I have ever seen, and it is easy to understand,
Submitted byRushikesh (not verified)on Fri, 09/05/2014 - 13:57

Thanks nice article but i want to upload Excel file instead of csv/Excel plz help.....
Submitted byThanh (not verified)on Tue, 09/23/2014 - 15:23

i have problem my language when i improt execl with my language Vietnamese .it corrupted font. can you hel me about it ?
Submitted byNhuttrieu (not verified)on Mon, 11/10/2014 - 18:04

Thank you very much, that was all I needed for my project. Wishing you much success!
Submitted byViChai_Chai (not verified)on Thu, 12/11/2014 - 11:05

Thank you Joken, Do you have some tricks on how to customize importing text file (TXT) to database??
Submitted byRafael Rodríguez (not verified)on Thu, 12/18/2014 - 19:05

Thanks Joken. But I have the csv in diferent a diferent style. I have in first row all the values for first column, and in the second row the values of the second colum. id1 id2 id3 id4 user1 user2 user3 user4 How I can cross this values? Thank you in advance
Submitted byK SURYAPRAKASH (not verified)on Sun, 03/01/2015 - 18:09

Helloo! Thank you sir for your indirect help! I woud like to skip first row from my CSV file before uploading it to mysql. Help me please.... Thanks SURYAPRAKASH K
Submitted bynasarullah (not verified)on Fri, 04/17/2015 - 18:06

Thanks, its works properly.
Submitted bykuldeepon Sat, 05/02/2015 - 14:49

Error in excel file .xls and .xlsx. Display data in �Note��������������� this way.....
Submitted byVinodh Kumar (not verified)on Sun, 06/28/2015 - 15:04

It's really very simple and cool stuff. saved much of time, big thanks buddy
Submitted byPedro Cezar (not verified)on Sat, 07/11/2015 - 02:57

Muito obrigado deu tudo certo!
Submitted bygood (not verified)on Sun, 02/05/2017 - 01:24

shows invalid file on a live server
Submitted bymax lange123 (not verified)on Tue, 02/20/2018 - 16:20

it cannot upload csv file, it always says "Invalid File:Please Upload CSV File." what can i do pls help me.
Submitted byMilena (not verified)on Mon, 09/24/2018 - 10:37

please help us resolve the issues it does not read the excel file format .xlsx
Submitted byPrashant Gupta22 (not verified)on Tue, 10/16/2018 - 15:19

You need to update the code... new PHP supports mysqli instead of mysql
Submitted byRajesh S Iyer (not verified)on Mon, 07/08/2019 - 21:46

Am not able to upload big csv of 50000 lines, only 7000 to 8000 lines gets imported in mysql. pls help...

Add new comment