Welcome folks today in this blog post we will be uploading csv file in browser using html5 form and importing the data into mysql table in php. All the full source code of the application is shown below.
Get Started
In order to get started you need to create the mysql table called users by using the below sql code
|
1 2 3 4 5 6 7 8 9 10 11 |
CREATE TABLE `users` ( `id` int(11) NOT NULL, `Name` varchar(255) NOT NULL, `Age` int(11) NOT NULL, `Country` varchar(255) NOT NULL ); ALTER TABLE `users` ADD PRIMARY KEY (`id`); ALTER TABLE `users` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT; |
Now if you execute this code inside the phpmyadmin you will see a table created as shown below
As you can see we have the four columns and ID is the primary key and it is auto-incremented and then we have the fields for storing the name,age and country of the user.
And now you need to create the index.php file inside the root directory of your project and copy paste the below code
index.php
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
<!DOCTYPE html> <html> <head> <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.1.0/jquery.min.js"></script> <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css" /> </head> <body> <h3 align="center">How to Import Data from CSV File to Mysql using PHP</h3><br /> <form method="post" enctype="multipart/form-data"> <div align="center"> <label>Select CSV File:</label> <input type="file" name="file" /> <br /> <input type="submit" name="submit" value="Import" class="btn btn-info" /> </div> </form> </body> </html> |
As you can see we have a simple bootstrap 5 form where we have the simple input field where we allow the users to upload csv files and then we have the button to submit the form. This form will submit on the same page
Now we will be writing the php code in the same file at the very top as shown below
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 |
<?php $connect = mysqli_connect("localhost", "root", "", "exceldb"); if(isset($_POST["submit"])) { if($_FILES['file']['name']) { $filename = explode(".", $_FILES['file']['name']); if($filename[1] == 'csv') { $handle = fopen($_FILES['file']['tmp_name'], "r"); while($data = fgetcsv($handle)) { $item1 = mysqli_real_escape_string($connect, $data[0]); $item2 = mysqli_real_escape_string($connect, $data[1]); $item3 = mysqli_real_escape_string($connect,$data[2]); $query = "INSERT into users(Name,Age,Country) values('$item1','$item2','$item3')"; echo $query; mysqli_query($connect, $query); } fclose($handle); echo "<script>alert('Import done');</script>"; } } } ?> <!DOCTYPE html> <html> <head> <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.1.0/jquery.min.js"></script> <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css" /> </head> <body> <h3 align="center">How to Import Data from CSV File to Mysql using PHP</h3><br /> <form method="post" enctype="multipart/form-data"> <div align="center"> <label>Select CSV File:</label> <input type="file" name="file" /> <br /> <input type="submit" name="submit" value="Import" class="btn btn-info" /> </div> </form> </body> </html> |
As you can see we are checking if the submit button was clicked by the user using the isset() method and inside it we are opening the csv file using the fopen() method and then we are reading the content of the file using the fgetcsv() method and then we are inserting the data line by line.
And for testing the app you can create a sample file.csv and copy paste the below data
|
1 2 3 4 |
Gautam,45,New Zealand John,56,India Sanjay,45,Mumbai David,34,England |
As you can see we are not providing the ID as this is the primary key of the table. Now if you open the index.php file inside the browser you will see the below output
