This is very interesting post, in which we have covered topic like Uploading CSV file data and then after before import into Mysql table we can edit CSV file data on web page by using jquery Ajax with PHP. Now what are benefits of this feature, because suppose we want to edit CSV file data then we can directly edit into CSV file and then after we can proceed for importing. But suppose you don't know there is some spelling error or any information is wrong in CSV file data and you have start upload CSV file for import data then you cannot stop importing process and wrong data will be directly import into Mysql table. But in this feature when you have upload CSV file for importing into Mysql table then on web page first it will display all CSV file data on web page in table format and you can edit any table column data before importing. So you after uploading file you can check all CSV file data on web page and if there is any wrong data is found then you can edit them and then after you can import into Database by using PHP with Ajax jQuery.
In Current Web Development, there are new and new innovation has been developed for reducing error. So, this feature also is one innovation for importing data through CSV file. Because here data has been verified by two times and if any wrong information has been then we can Live Edit that CSV file data before importing. We all know CSV file format is widely used for import and export of data from any web application. Then if you have build any enterprise level web application and in that you have import data from CSV file then this feature will reduce human work, because we can edit CSV file data after uploading CSV file and before importing into database. So, this feature will reduce importing of wrong data into Database. So, here we have make one more tutorial on Live CSV file data editing and then after importing in Mysql Database by using PHP with Ajax jQuery.
See Also
index.php
This is then main file of this tutorial, In this find HTML code and jQuery Ajax code. In HTML code we have make one form in which we have define on file tag for select CSV file and upload button for send CSV file to server. And in jQuery Ajax code you can see below we have write jQuery code on two button click event.
First button click event will send selected CSV file to fetch.php file. For send file data to server, here we have use new FormData() object. After success of Ajax request it will received data in JSON format, and that data will be converted into HTML table format and display on web page. With table it will also make import button with id import_data will also make for import data into Mysql table. In student name table column has attribute like class="student_name" and student phone table column has attribute like class="student_phone". So, this column data will be fetch from jQuery code, and this two column will be editable by using contenteditable attribute.
In second jQuery click event has write on Import button, so when we have click on import button then this code will execute. In this first it has fetch data from attribute class and store under local variable in array format. Then after that local variable value will be send to PHP script by using Ajax request. Ajax request will send request to import.php file for import data into Database. After successfully importing of data it will display success message on web page.
<!DOCTYPE html>
<html>
<head>
<title>CSV File Editing and Importing in PHP</title>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/2.2.0/jquery.min.js"></script>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css" />
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/js/bootstrap.min.js"></script>
<style>
.box
{
max-width:600px;
width:100%;
margin: 0 auto;;
}
</style>
</head>
<body>
<div class="container">
<br />
<h3 align="center">CSV File Editing and Importing in PHP</h3>
<br />
<form id="upload_csv" method="post" enctype="multipart/form-data">
<div class="col-md-3">
<br />
<label>Select CSV File</label>
</div>
<div class="col-md-4">
<input type="file" name="csv_file" id="csv_file" accept=".csv" style="margin-top:15px;" />
</div>
<div class="col-md-5">
<input type="submit" name="upload" id="upload" value="Upload" style="margin-top:10px;" class="btn btn-info" />
</div>
<div style="clear:both"></div>
</form>
<br />
<br />
<div id="csv_file_data"></div>
</div>
</body>
</html>
<script>
$(document).ready(function(){
$('#upload_csv').on('submit', function(event){
event.preventDefault();
$.ajax({
url:"fetch.php",
method:"POST",
data:new FormData(this),
dataType:'json',
contentType:false,
cache:false,
processData:false,
success:function(data)
{
var html = '<table class="table table-striped table-bordered">';
if(data.column)
{
html += '<tr>';
for(var count = 0; count < data.column.length; count++)
{
html += '<th>'+data.column[count]+'</th>';
}
html += '</tr>';
}
if(data.row_data)
{
for(var count = 0; count < data.row_data.length; count++)
{
html += '<tr>';
html += '<td class="student_name" contenteditable>'+data.row_data[count].student_name+'</td>';
html += '<td class="student_phone" contenteditable>'+data.row_data[count].student_phone+'</td></tr>';
}
}
html += '<table>';
html += '<div align="center"><button type="button" id="import_data" class="btn btn-success">Import</button></div>';
$('#csv_file_data').html(html);
$('#upload_csv')[0].reset();
}
})
});
$(document).on('click', '#import_data', function(){
var student_name = [];
var student_phone = [];
$('.student_name').each(function(){
student_name.push($(this).text());
});
$('.student_phone').each(function(){
student_phone.push($(this).text());
});
$.ajax({
url:"import.php",
method:"post",
data:{student_name:student_name, student_phone:student_phone},
success:function(data)
{
$('#csv_file_data').html('<div class="alert alert-success">Data Imported Successfully</div>');
}
})
});
});
</script>
fetch.php
This PHP script has received Ajax request for fetch selected CSV file data from Ajax. Here first it has open CSV file by using fopen() function, then after it has read first line which will be table column of CSV file by using fgetcsv() method and store under local variable. After this for read all CSV file data it has use while loop with fgetcsv() method and it has read CSV file data and store data under local variable in array format. And lastly for send response to Ajax request in json format, it has use json_encode() method.
<?php
//fetch.php
if(!empty($_FILES['csv_file']['name']))
{
$file_data = fopen($_FILES['csv_file']['tmp_name'], 'r');
$column = fgetcsv($file_data);
while($row = fgetcsv($file_data))
{
$row_data[] = array(
'student_name' => $row[0],
'student_phone' => $row[1]
);
}
$output = array(
'column' => $column,
'row_data' => $row_data
);
echo json_encode($output);
}
?>
import.php
This script has received Ajax request for insert data into mysql table. First it has verify that there is any ajax has been received for not by using isset() function. If it has received data then it will make database connection and then after it has store array of data store under local variable. For generate multiple insert data query it has loop and after generating multiple insert query it has execute all query at the same time. So, this way it will insert or import multiple data at the same time.
<?php
//import.php
if(isset($_POST["student_name"]))
{
$connect = new PDO("mysql:host=localhost;dbname=testing", "root", "");
$student_name = $_POST["student_name"];
$student_phone = $_POST["student_phone"];
for($count = 0; $count < count($student_name); $count++)
{
$query .= "
INSERT INTO tbl_student(student_name, student_phone)
VALUES ('".$student_name[$count]."', '".$student_phone[$count]."');
";
}
$statement = $connect->prepare($query);
$statement->execute();
}
?>
Database
--
-- Database: `testing`
--
-- --------------------------------------------------------
--
-- Table structure for table `tbl_student`
--
CREATE TABLE `tbl_student` (
`student_id` int(11) NOT NULL,
`student_name` varchar(250) NOT NULL,
`student_phone` varchar(20) NOT NULL,
`image` varchar(255) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
--
-- Indexes for table `tbl_student`
--
ALTER TABLE `tbl_student`
ADD PRIMARY KEY (`student_id`);
--
-- AUTO_INCREMENT for dumped tables
--
--
-- AUTO_INCREMENT for table `tbl_student`
--
ALTER TABLE `tbl_student`
MODIFY `student_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1;
Hi Dear Webslesson , Thank You For Your Learning's
ReplyDeletePlease Teach Inline Multiple Upload File's Via PHP Ajax Mysql , So Thanks
nice one
ReplyDeleteThese codes are note working for localhost
ReplyDeleteThere is nothing worng with these scripts except the face that you might include(import.php) at the Index.php file. This way you can Import the data after fetching them. Another think you might not figure out is a (".") before (=) at the $query (import.php). Thnk you for these scripts. Good Job
ReplyDeleteGood day! Can you please make a tutorial that will show a validation error in excel file. A validation that will tell what column or row has an error. thanks
ReplyDeleteIt does not insert in database
ReplyDeleteNice tutorial. How can i include delete button on each row to delete record if i not want to import it? Thanks :)
ReplyDeleteThe demo cant perform the data alteration on the database
ReplyDeleteThis comment has been removed by the author.
ReplyDeletehow we use it on localhost, because this not work on localhost. Thank you
ReplyDeletework for me in localhost
Deletethis is work, if you edit little code from this website
ReplyDelete