How To Create Import CSV/Excel File To MySQL Database Using PHP

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

Good Day!!!

If you are looking for on How To Create Import CSV/Excel File To MySQL Database Using PHP then you are at the right place. In this article, we are going to learn on how to import data using CSV/Excel File to MySQL database using PHP Language.

Let's start with:

We are going to make our database.

Creating our Table

To create a database:

  1. Open the PHPMyAdmin
  2. Create a database and name it as "import_file".
  3. After creating a database name, click the SQL and kindly copy the code below.
  1. --
  2. -- Table structure for table `user`
  3. --
  4.  
  5. CREATE TABLE IF NOT EXISTS `user` (
  6. `user_id` int(11) NOT NULL AUTO_INCREMENT,
  7. `user_name` varchar(100) NOT NULL,
  8. `first_name` varchar(100) NOT NULL,
  9. `last_name` varchar(100) NOT NULL,
  10. `date_added` datetime NOT NULL,
  11. PRIMARY KEY (`user_id`)

We are going to make our database connection.

Database Connection

This PHP Script is our database. Copy and paste this then save it as "database.php".

  1. <?php
  2. mysql_select_db('import_file',mysql_connect('localhost','root',''))or die(mysql_error());
  3. ?>

We are going to make our form field.

Creating Form Field

This form field that the admin clicks the file button to import data into the database.

  1. <form action="import_query.php" method="post" name="upload_excel" enctype="multipart/form-data">
  2. <div>
  3. <label>Import CSV/Excel File:</label>
  4. <input type="file" multiple name="filename" id="filename">
  5. <button type="submit" id="submit" name="submit" data-loading-text="Loading...">Upload</button>
  6. </div>
  7. </form>

We are going to create CSV/Excel File in PHP query.

CSV/Excel File - PHP Script

This is the PHP codes for CSV/Excel File to import into MySQL database.

  1. <?php
  2. if (isset($_POST['submit']))
  3. {
  4. include('database.php');
  5.  
  6. //Import uploaded file to Database
  7. $handle = fopen($_FILES['filename']['tmp_name'], "r");
  8.  
  9. while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
  10. mysql_query("INSERT into user (user_name, first_name, last_name, date_added)
  11. values('$data[0]', '$data[1]', '$data[2]', NOW())");
  12.  
  13. }
  14.  
  15. fclose($handle);
  16. //print "Import done";
  17. echo "<script type='text/javascript'>alert('Successfully Imported a CSV File for User!');</script>";
  18. echo "<script>document.location='index.php'</script>";
  19. //view upload form
  20. }
  21. ?>

This table where you can view the data after importing in the database.

  1. <table border="1" cellspacing="5" cellpadding="5">
  2. <thead>
  3. <tr>
  4. <th>UserName</th>
  5. <th>FirstName</th>
  6. <th>LastName</th>
  7. <th>Date Added</th>
  8. </tr>
  9. </thead>
  10. <?php
  11. include ('database.php');
  12. $result= mysql_query("select * from user order by user_id ASC") or die (mysql_error());
  13. while ($row= mysql_fetch_array ($result) ){
  14. $id=$row['user_id'];
  15. ?>
  16.  
  17. <tbody>
  18. <tr>
  19. <td><?php echo $row['user_name']; ?></td>
  20. <td><?php echo $row['first_name']; ?></td>
  21. <td><?php echo $row['last_name']; ?></td>
  22. <td><?php echo date("M d, Y h:i:s a",strtotime($row['date_added'])); ?></td>
  23. </tr>
  24. </tbody>
  25.  
  26. <?php } ?>
  27. </table>

And, this is our style.

  1. <style type="text/css">
  2. body {
  3. width:700px;
  4. margin:auto;
  5. }
  6. label {
  7. color: blue;
  8. font-size: 18px;
  9. font-weight: bold;
  10. font-family: cursive;
  11. margin-right: 10px;
  12. }
  13. input[type="file"] {
  14. border: blue 1px solid;
  15. padding: 8px;
  16. color: blue;
  17. font-size: 15px;
  18. border-radius: 4px;
  19. margin-right: 10px;
  20. cursor:pointer;
  21. }
  22. button {
  23. font-size: 18px;
  24. border: blue 1px solid;
  25. font-weight: bold;
  26. padding: 8px;
  27. background: azure;
  28. color: blue;
  29. border-radius: 4px;
  30. cursor:pointer;
  31. }
  32. div {
  33. border: blue 1px solid;
  34. padding: 15px;
  35. text-align: center;
  36. border-radius: 4px;
  37. background: azure;
  38. }
  39. table {
  40. width: 100%;
  41. text-align: center;
  42. font-size: 18px;
  43. font-family: cursive;
  44. border: blue 1px solid;
  45. background: azure;
  46. }
  47. th {
  48. color:red;
  49. }
  50. td {
  51. color:blue;
  52. }
  53. </style>

This is the result:

This is the data to be imported in MySQL Database.

DataAfter import the data in the MySQL Database. This is the result.

Success

So, this is it, just follow the steps to have this CSV/Excel File Imported To MySQL Database or you can download the full source code below by clicking the "Download Code" button below.

Share us your thoughts and comments below. Thank you so much for dropping by and reading this tutorial post. For more updates, don’t hesitate and feel free to visit this website more often and please share this with your friends or email me at [email protected]. Practice Coding. Thank you very much.


Comments

Warning: mysqli_query() expects at least 2 parameters, 1 given in C:\xampp\htdocs\hrexpress\import_excel_date\index.php on line 91

Warning: mysqli_error() expects exactly 1 parameter, 0 given in C:\xampp\htdocs\hrexpress\import_excel_date\index.php on line 91

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.