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

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

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.


Comments

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

You need to update the code... new PHP supports mysqli instead of mysql

please help us resolve the issues it does not read the excel file format .xlsx

it cannot upload csv file, it always says "Invalid File:Please Upload CSV File." what can i do pls help me.

Sir when i try to upload file this messege come again and again

shows invalid file on a live server

Muito obrigado deu tudo certo!

It's really very simple and cool stuff. saved much of time, big thanks buddy

Error in excel file .xls and .xlsx. Display
data in �Note��������������� this way.....

Thanks, its works properly.

Kept it nice and simple - very neat!

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

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

Thank you Joken, Do you have some tricks on how to customize importing text file (TXT) to database??

Thank you very much, that was all I needed for my project. Wishing you much success!

thank you for your wonderful knowledge..it helps me a lot

i have problem my language when i improt execl with my language Vietnamese .it corrupted font. can you hel me about it ?

Thanks nice article but i want to upload Excel file instead of csv/Excel plz help.....

thank you guys, hope this is the best article I have ever seen, and it is easy to understand,

in the same code, how can we put an update function?

Do you have some tricks on how to customize importing csv file to database?

Thanks,

wow...thank you so much..I think it will really help me in my project

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?

hello, you might solve the problem? I have the same problem and I could not fix it.

Thank you

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.