In this post We will show you how to import data from CSV File to and Insert into Mysql Database table using PHP Script. In this post you can find how to fetch data from uploaded CSV and then after Insert Data into Mysql table. If you want to insert large amount of data which is store under CSV file and you want insert into to Mysql database then at that time this type of concept you can use. For this get data from CSV file data, We have use simple fgetcsv() function for fetch all data from CSV file and after that We have run insert query for insert data into table.
First what is CSV?, It is comma-separated values file that stores large amount of the tabular data in simple plain text format. Mostly, CSV type file is utilize to import to or export data from the table. So we can easily large amount of exchange data from Mysql database server because it takes less space than other tabular format data like Excel. After Exchange of data we can use that data according to our requirement. In most of the enterprise level application data has been import and export in CSV file format because we can fastly exchange data from database server. So In this post we have discuss topic like How to import data from CSV file by using PHP Script.
Source Code
<?php
$connect = mysqli_connect("localhost", "root", "", "testing");
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]);
$query = "INSERT into excel(excel_name, excel_email) values('$item1','$item2')";
mysqli_query($connect, $query);
}
fclose($handle);
echo "<script>alert('Import done');</script>";
}
}
}
?>
<!DOCTYPE html>
<html>
<head>
<title>Webslesson Tutorial</title>
<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>
Database
--
-- Database: `testing`
--
-- --------------------------------------------------------
--
-- Table structure for table `excel`
--
CREATE TABLE IF NOT EXISTS `excel` (
`excel_id` int(11) NOT NULL AUTO_INCREMENT,
`excel_name` varchar(250) NOT NULL,
`excel_email` varchar(250) NOT NULL,
PRIMARY KEY (`excel_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
--
-- Dumping data for table `excel`
--
Yessssss keep making more
ReplyDeleteThank you!!!!!
ReplyDeleteThank you, very usefull
ReplyDeleteThanks brother. Your site is so resourceful
ReplyDeleteThanks!
ReplyDeleteI use
$connect = new mysqli("localhost", "root", "", "testing", 3307);
to make it work.
Furthermore, make sure that Excel uses commas instead of semicolons as separator.
Nice piece of code. Thanks much. This could do what PHPExcel and PHPSpreadsheet couldn't do. Using a TSV instead of aCSV will help load huge files in the database without causing any memory problems. Thanks much for the code. It was great.
ReplyDeleteThanks
ReplyDeletei really impressed
thanks again for this coading
how import numerical
ReplyDeletevalue
Hi there
ReplyDeleteI'm getting this error : Undefined offset: 1 on line 14. Any idea what might be ?
thanks
You know your work 👌
ReplyDeleteI have an existing file and I want to update it? Please help on this. Thank you...
ReplyDeleteHi I'm getting this error. Can you please help me? Thank you very much.
ReplyDeleteNotice: Undefined index: file in C:\xampp\htdocs\SAMPLE\sample_upload.php on line 5
Great read. Thanks. Excited to see more.
ReplyDeleteSorry I have tried it but didn't work, I received error message of "Note: undefined index file at line 4 in php section"
ReplyDeleteBravo! Well done. It works, compared to several others on the web which don't
ReplyDelete