Skip to main content

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


In this tutorial im going to show you how to create n application that can be used to import CSV/Excel file using PHP. To start with this project 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 named “db.php” that will hold our database connection and here’s the following code:

  1. <?php
  2. $conn=mysql_connect("localhost","root","") or die("Could not connect");
  3. mysql_select_db("studentdb",$conn) 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 access the “excel” folder from our web directory. And this index.php will load all the list of subject if the subject table is not empty as well as this page will allow the user to import the CSV/Excel file and upload the data to MySQL Database. and it will look like as shown below.
i1

And here’s the code for “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 = mysql_query($SQLSELECT, $conn);
  81. while($row = mysql_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 named “import.php” that will used to process the data from CSV/Excel to MySQL Database. and 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 = mysql_query( $sql, $conn );
  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. mysql_close($conn);
  42.  
  43.  
  44.  
  45. }
  46. }
  47. ?>

After this, you can try it with your own, and I have attached with this sourcecode the example csv/excel file used for this tutorial. 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.

Note: Due to the size or complexity of this submission, the author has submitted it as a .zip file to shorten your download time. After downloading it, you will need a program like Winzip to decompress it.

Virus note: All files are scanned once-a-day by SourceCodester.com for viruses, but new viruses come out every day, so no prevention program can catch 100% of them.

FOR YOUR OWN SAFETY, PLEASE:

1. Re-scan downloaded files using your personal virus checker before using it.
2. NEVER, EVER run compiled files (.exe's, .ocx's, .dll's etc.)--only run source code.

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

CAPTCHA
This question is for testing whether or not you are a human visitor and to prevent automated spam submissions.