Hi fireind in this article we are going to disscuss about How to import CSV Excel to MySQL Database using PHP. if you are a web developer then definitely you might have faced it. many times you need to import data from a CSV Comma Separeted Value file and insert into MySQL database For Example consider a case when you have many records in a excel file and you need to import this file into you MySQL database then you can not insert each and every record manually as it will take too much time. in this post we are creatin a source code which help of you can import CSV Excel File into MySQL Database Easily. Now lets Start follow the Some Steps Carefully.
How To Import CSV Excel to MySQL Database using PHP
1-Creating Database
- Open Phpmyadmin in your Browser
- Click on Database Tab Display on Top side
- Give the Database name “csv”.
- After Creating Database Open it.
- Click on SQL Tab on Top area
- Copy the Below Source Code and paste it.
- Then Click on Go.
-- phpMyAdmin SQL Dump -- version 4.2.7.1 -- http://www.phpmyadmin.net -- -- Host: 127.0.0.1 -- Generation Time: May 01, 2016 at 08:55 AM -- Server version: 5.6.20 -- PHP Version: 5.5.15 SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO"; SET time_zone = "+00:00"; /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8 */; -- -- Database: `csv` -- -- -------------------------------------------------------- -- -- Table structure for table `data` -- CREATE TABLE IF NOT EXISTS `data` ( `user_id` int(20) NOT NULL, `user_name` varchar(150) NOT NULL, `first_name` varchar(150) NOT NULL, `last_name` varchar(150) NOT NULL, `date_added` datetime NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=11 ; -- -- Dumping data for table `data` -- INSERT INTO `data` (`user_id`, `user_name`, `first_name`, `last_name`, `date_added`) VALUES (5, 'Priyanshu Raj', 'Priyanshu', 'Raj', '2016-05-01 12:21:48'), (6, 'Lucky Kumar', 'Lucky ', 'Kumar', '2016-05-01 12:21:48'), (7, 'Mohit Tiwari', 'Mohit', 'Tiwari', '2016-05-01 12:21:48'), (8, 'Abhi Singh', 'Abhi ', 'Singh', '2016-05-01 12:21:48'), (9, 'Deepak Raj', 'Deepak', 'Raj', '2016-05-01 12:21:48'), (10, 'Amit ', 'Amit', 'Kumar', '2016-05-01 12:21:48'); -- -- Indexes for dumped tables -- -- -- Indexes for table `data` -- ALTER TABLE `data` ADD PRIMARY KEY (`user_id`); -- -- AUTO_INCREMENT for dumped tables -- -- -- AUTO_INCREMENT for table `data` -- ALTER TABLE `data` MODIFY `user_id` int(20) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=11; /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
2- Import DB File
After Downloading the source code extract it in your root folder.
- Open Phpmyadmin in your Browser
- Click on Database Tab Display on Top side
- Give the Database name “csv”.
- After Creating Database Open it.
- Click on Import Tab on Top area
- You can Find Db file in Downloaded source code Select it.
- Then Click on Go.
3- Creating Database Connection
After import Database File then next step is creating database connection using php copy the below code and save it is as “database.php”.
<?php
mysql_select_db('csv',mysql_connect('localhost','root',''))or die(mysql_error());
?>
4 – Creating a Form where choose File
in this step we are creating a form and input field type file to choose csv file upload and creating a submit button.
<form action="" method="post" enctype="multipart/form-data"> <div> <label>CSV File:</label> <input type="file" multiple name="filename" id="filename"><br /><br /> <button type="submit" id="submit" name="submit" data-loading-text="Loading...">Upload</button> </div> </form>
5 – Styling the form using CSS
in this step give some style to form using css and make more attractive. follow these step given below.
<style type="text/css">
form{
border: 2px red dotted;
width:400px;
height:120px;
background:skyblue;
border-radius: 4px;
padding:5px;
}
h1 {
color: white;
font-weight: bold;
border: 2px red dotted;
font-family:Algerian;
width:400px;
height:50px;
background:black;
border-radius:4px;
padding:5px;
}
label {
color: red;
font-size: 20px;
font-weight: bold;
margin-right: 10px;
}
input[type="file"] {
border: 2px red dotted;
padding: 8px;
color: red;
font-size: 15px;
border-radius: 4px;
margin-right: 10px;
cursor:pointer;
}
button {
font-size: 18px;
border: 2px red solid;
font-weight: bold;
padding: 8px;
background: black;
color: white;
border-radius: 4px;
cursor:pointer;
}
</style>
6 – Finally Validate the Form using PHP to send data into Database
Next step is validating the form server side using PHP. in this step we are validating the form using PHP server side to import CSV File..
<?php
if (isset($_POST['submit']))
{
include('database.php');
//Import uploaded file to Database
$handle = fopen($_FILES['filename']['tmp_name'], "r");
while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
mysql_query("INSERT into data (user_name, first_name, last_name, date_added)
values('$data[0]', '$data[1]', '$data[2]', NOW())");
}
fclose($handle);
echo "<script type='text/javascript'>alert('Successfully Imported a CSV File!');</script>";
echo "<script>document.location='index.php'</script>";
}
?>
7 – Display All Imported Data on Page From database using PHP
in this step we are going to display inserted data into database on page using php.
<?php
include ('database.php');
$result= mysql_query("select * from data order by user_id ASC") or die (mysql_error());
while ($row= mysql_fetch_array ($result) ){
$id=$row['user_id'];
?>
<tbody style="background:skyblue; color:black">
<tr>
<td><?php echo $row['user_name']; ?></td>
<td><?php echo $row['first_name']; ?></td>
<td><?php echo $row['last_name']; ?></td>
<td><?php echo date("M d, Y h:i:s a",strtotime($row['date_added'])); ?></td>
</tr>
</tbody>
<?php } ?>
If you facing any type of problem with this source code then you can Download the Complete source code in zip Formate by clicking the below button Download Now otherwise you can send Comment.