On the web, we have seen lots of articles or tutorial on How to import CSV file data into Mysql Database by using PHP script. But there are none of the tutorials has define how can we define particular CSV file column data at the time of importing file, and that selected column data has been imported into Mysql table by using PHP script with Ajax jQuery. For solve the problem of CSV file Column Mapping here we have make this tutorial in which we have step by step describe the process of CSV file column mapping in PHP using Ajax jQuery.
This tutorial has divided in following different part.
- Make Upload Form for Select CSV File
- Convert CSV File into HTML Table
- Write jQuery Code for Column Selection
- Import Select Column Data
Before we have going to step, first we must have to see our database structure. So below you can find this tutorial Mysql table definition.
--
-- Database: `testing`
--
-- --------------------------------------------------------
--
-- Table structure for table `csv_file`
--
CREATE TABLE `csv_file` (
`id` int(11) NOT NULL,
`first_name` varchar(255) NOT NULL,
`last_name` varchar(255) NOT NULL,
`email` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Indexes for table `csv_file`
--
ALTER TABLE `csv_file`
ADD PRIMARY KEY (`id`);
--
-- AUTO_INCREMENT for dumped tables
--
--
-- AUTO_INCREMENT for table `csv_file`
--
ALTER TABLE `csv_file`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1;
COMMIT;
Here in this Mysql table there is only three column in which we want to import CSV file data. Now below we have to CSV file structure.
Company Name,First Name,Last Name,Address,City,State,ZIP/PIN CODE,Country,Phone Number,Email
Prestiga-Biz,Barbara,Motes,3029 Harrison Street,Oakland,CA,94612,USA,415-481-0998,barbarapmotes@armyspy.com
McDade's,Paul,Minter,1376 Orphan Road,Long Lake,WI,54542,USA,715-967-2332,paulcminter@rhyta.com
Penn Fruit,Robin,Morton,4609 Eastland Avenue,Jackson,MS,39211,USA,601-348-3248,robinamorton@armyspy.com
Frank's Nursery & Crafts,Alvin,Sarvis,83 Star Trek Drive,Pensacola,FL,32501,USA,850-356-1961,AlvinMSarvis@armyspy.com
Media Play,Daniel,Retana,1114 Bel Meadow Drive,Arrowhead,CA ,92352,USA,909-336-8296,DanielSRetana@teleworm.us
Cut Rite,Eva,Tallent,4138 Shadowmar Drive,Metairie,LA,70006,USA,504-779-1200,EvaTTallent@dayrep.com
Matrix Design,Charles,Astorga,410 Westwood Avenue,Garden City,NY ,11530,USA,516-805-0092,CharlesAAstorga@armyspy.com
Century House,Jane,Lim,2305 Reppert Coal Road,Southfield,MI,48075,USA,586-970-6260,JanePLim@armyspy.com
Life's Gold,Tom,Garcia,518 Seth Street,Gustine,TX,76455,USA,325-667-9370,TomKGarcia@armyspy.com
Two Pesos,Aaron,Lovejoy,2448 Settlers Lane,New York,NY,10007,USA,917-826-2371,AaronRLovejoy@armyspy.com
Smitty's Marketplace,Susan,Reyes,4402 Stuart Street,Sewickley,PA,15143,USA,724-417-4584,SusanDReyes@armyspy.com
Waves Music,Susan,Jackson,1196 Archwood Avenue,Cheyenne,WY,82003,USA,307-871-1265,SusanRJackson@rhyta.com
Realty Depot,Mary,Shepard,2632 Scott Street,Poughkeepsie,NY,12601,USA,845-471-7357,MaryEShepard@armyspy.com
Martin's,Sadie,Hill,148 Peaceful Lane,Warrensville Heights,OH,44128,USA,216-390-5580,SadieAHill@dayrep.com
Wickes Furniture,Stephen,Flynn,4378 Jenna Lane,Ames,IA,50010,USA,515-233-9027,StephenJFlynn@teleworm.us
Larry's Markets,Beth,Holloway,3915 Essex Court,South Burlington,VT,5403,USA,802-272-7414,BethHHolloway@rhyta.com
Handy Dan,Mildred,Hoyt,2626 Johnstown Road,Cary,IL,60013,USA,847-462-6919,MildredAHoyt@armyspy.com
Laura Ashley Mother & Child,Karen,Walker,3659 Shingleton Road,Portage,MI,49002,USA,269-327-9698,KarenBWalker@rhyta.com
Harmony House,Lucille,Wilson,644 Villa Drive,South Bend,IN,46601,USA,574-303-6598,LucilleZWilson@jourrapide.com
Suadela Investment,Jimmie,Cornwell,2533 Melody Lane,Lively,VA,22503,USA,804-462-5048,JimmieMCornwell@jourrapide.com
American Appliance,Marion,Davis,2905 Denver Avenue,City Of Commerce,CA,90040,USA,951-314-7204,MarionVDavis@armyspy.com
The Warner Brothers Store,Sonia,Johnson,1510 Bel Meadow Drive,Los Angeles,CA,90017,USA,909-269-6784,SoniaCJohnson@jourrapide.com
Gart Sports,Brandy,Voyles,2538 Broadcast Drive,Washington,VA,20011,USA,703-956-0582,BrandyRVoyles@rhyta.com
Irving's Sporting Goods,Deborah,Gossard,2655 Ashton Lane,Austin,TX,78756,USA,512-465-9149,DeborahGGossard@rhyta.com
Yardbirds Home Center,Dawn,Guerrero,753 Saints Alley,Tampa,FL,33614,USA,813-787-0466,DawnAGuerrero@teleworm.us
Multicerv,Priscilla,Holloway,4957 Custer Street,Smethport,PA,16749,USA,814-887-3339,PriscillaRHolloway@armyspy.com
Colonial Stores,Jay,Crowder,4281 Ryder Avenue,Bellevue,WA,98004,USA,425-384-6261,JayECrowder@dayrep.com
Balanced Fortune,Ashley,Hemphill,2042 Olen Thomas Drive,Rosston,TX,76263,USA,940-768-7053,AshleyJHemphill@jourrapide.com
Lee Wards,Debra,Harrington,532 Seltice Way,Kellogg,ID,83837,USA,208-783-0049,DebraFHarrington@armyspy.com
Garden Guru,Anna,Brooks,1332 Brown Street,San Francisco,CA,94124,USA,925-999-9078,AnnaEBrooks@rhyta.com
So, Above we can see that there is 10 column in CSV file. Now we have to make feature like when we have upload CSV file file then we can define three column and only three column data must be import into above Mysql table. So below you can find the solution of CSV file column mapping in PHP script using Ajax jQuery.
1 - Make Upload Form for Select CSV File
For make CSV file column mapping script. So first we have to make One upload form for select CSV file from our local computer. After creating HTML form for upload csv file. Then after we have to write jquery code and in that code we have make Ajax call which will send CSV file to server script. Below you can find the source code of this steps.
index.php
<!DOCTYPE html>
<html>
<head>
<title>CSV Column Mapping in PHP</title>
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<script src="http://code.jquery.com/jquery.js"></script>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css">
<style>
.table tbody tr th
{
min-width: 200px;
}
.table tbody tr td
{
min-width: 200px;
}
</style>
</head>
<body>
<div class="container">
<br />
<br />
<h1 align="center">CSV Column Mapping in PHP</h1>
<br />
<div id="message"></div>
<div class="panel panel-default">
<div class="panel-heading">
<h3 class="panel-title">Select CSV File</h3>
</div>
<div class="panel-body">
<div class="row" id="upload_area">
<form method="post" id="upload_form" enctype="multipart/form-data">
<div class="col-md-6" align="right">Select File</div>
<div class="col-md-6">
<input type="file" name="file" id="csv_file" />
</div>
<br /><br /><br />
<div class="col-md-12" align="center">
<input type="submit" name="upload_file" id="upload_file" class="btn btn-primary" value="Upload" />
</div>
</form>
</div>
<div class="table-responsive" id="process_area">
</div>
</div>
</div>
</div>
</body>
</html>
<script>
$(document).ready(function(){
$('#upload_form').on('submit', function(event){
event.preventDefault();
$.ajax({
url:"upload.php",
method:"POST",
data:new FormData(this),
dataType:'json',
contentType:false,
cache:false,
processData:false,
success:function(data)
{
if(data.error != '')
{
$('#message').html('<div class="alert alert-danger">'+data.error+'</div>');
}
else
{
$('#process_area').html(data.output);
$('#upload_area').css('display', 'none');
}
}
});
});
});
</script>
2 - Convert CSV File into HTML Table
In second steps, First we have to check user has select file or not. If User has select file then we have to check selected file is .csv file or not if selected file is CSV file then we have to fetch data from CSV file. From this first line of csv file, we have to count total number of column of CSV file and based on that column we have to make select box for column selection for all column in html table format. After this all csv file data has been store in SESSION variable for future use of import data and top five row of CSV file has been convert into HTML table. After this we have to make import button and append that button code in HTML table code and send whole data to Ajax request in json string format.
upload.php
<?php
//upload.php
session_start();
$error = '';
$html = '';
if($_FILES['file']['name'] != '')
{
$file_array = explode(".", $_FILES['file']['name']);
$extension = end($file_array);
if($extension == 'csv')
{
$file_data = fopen($_FILES['file']['tmp_name'], 'r');
$file_header = fgetcsv($file_data);
$html .= '<table class="table table-bordered"><tr>';
for($count = 0; $count < count($file_header); $count++)
{
$html .= '
<th>
<select name="set_column_data" class="form-control set_column_data" data-column_number="'.$count.'">
<option value="">Set Count Data</option>
<option value="first_name">First Name</option>
<option value="last_name">Last Name</option>
<option value="email">Email</option>
</select>
</th>
';
}
$html .= '</tr>';
$limit = 0;
while(($row = fgetcsv($file_data)) !== FALSE)
{
$limit++;
if($limit < 6)
{
$html .= '<tr>';
for($count = 0; $count < count($row); $count++)
{
$html .= '<td>'.$row[$count].'</td>';
}
$html .= '</tr>';
}
$temp_data[] = $row;
}
$_SESSION['file_data'] = $temp_data;
$html .= '
</table>
<br />
<div align="right">
<button type="button" name="import" id="import" class="btn btn-success" disabled>Import</button>
</div>
<br />
';
}
else
{
$error = 'Only <b>.csv</b> file allowed';
}
}
else
{
$error = 'Please Select CSV File';
}
$output = array(
'error' => $error,
'output' => $html
);
echo json_encode($output);
?>
3 - Write jQuery Code for Column Selection
Once selected CSV file data has been uploaded and after this convert that file data into html table format and display on web page. Now in third step we have to write jquery code for validate column selection or mapping. So, we have to write jquery script, firts it will check user must have to select at least three column this is because in database there is three column in which we want to import data. If user select less than or greater than three column then import button will be disabled, but if user select three different column then import button will enable. jQuery will also check user must define define unique column as per Mysql table, if user select two column from html table with same Mysql table then also import button will disable. All unique column select box option we have to store in variable, and this variable value we will be used for define column number at the time of importing of data. So this validation will check in this part of jQuery script.
index.php
<!DOCTYPE html>
<html>
<head>
<title>CSV Column Mapping in PHP</title>
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<script src="http://code.jquery.com/jquery.js"></script>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css">
<style>
.table tbody tr th
{
min-width: 200px;
}
.table tbody tr td
{
min-width: 200px;
}
</style>
</head>
<body>
<div class="container">
<br />
<br />
<h1 align="center">CSV Column Mapping in PHP</h1>
<br />
<div id="message"></div>
<div class="panel panel-default">
<div class="panel-heading">
<h3 class="panel-title">Select CSV File</h3>
</div>
<div class="panel-body">
<div class="row" id="upload_area">
<form method="post" id="upload_form" enctype="multipart/form-data">
<div class="col-md-6" align="right">Select File</div>
<div class="col-md-6">
<input type="file" name="file" id="csv_file" />
</div>
<br /><br /><br />
<div class="col-md-12" align="center">
<input type="submit" name="upload_file" id="upload_file" class="btn btn-primary" value="Upload" />
</div>
</form>
</div>
<div class="table-responsive" id="process_area">
</div>
</div>
</div>
</div>
</body>
</html>
<script>
$(document).ready(function(){
$('#upload_form').on('submit', function(event){
event.preventDefault();
$.ajax({
url:"upload.php",
method:"POST",
data:new FormData(this),
dataType:'json',
contentType:false,
cache:false,
processData:false,
success:function(data)
{
if(data.error != '')
{
$('#message').html('<div class="alert alert-danger">'+data.error+'</div>');
}
else
{
$('#process_area').html(data.output);
$('#upload_area').css('display', 'none');
}
}
});
});
var total_selection = 0;
var first_name = 0;
var last_name = 0;
var email = 0;
var column_data = [];
$(document).on('change', '.set_column_data', function(){
var column_name = $(this).val();
var column_number = $(this).data('column_number');
if(column_name in column_data)
{
alert('You have already define '+column_name+ ' column');
$(this).val('');
return false;
}
if(column_name != '')
{
column_data[column_name] = column_number;
}
else
{
const entries = Object.entries(column_data);
for(const [key, value] of entries)
{
if(value == column_number)
{
delete column_data[key];
}
}
}
total_selection = Object.keys(column_data).length;
if(total_selection == 3)
{
$('#import').attr('disabled', false);
first_name = column_data.first_name;
last_name = column_data.last_name;
email = column_data.email;
}
else
{
$('#import').attr('disabled', 'disabled');
}
});
});
</script>
4 - Import Select Column Data
This is last part of CSV file column mapping tutorial and in this part we have to import selected CSV file column data into Mysql table. In third part, we have write jQuery script and define three column with unique option. Now in this part, we have to write jQuery script in which we have to make Ajax call which will send local variable in which we have store column number which has been get in third part. Based on this variable column number at PHP script will import only that column data into Mysql table. At PHP script CSV file data will be get from SESSION variable in which we have store CSV file data in second steps.
index.php
<!DOCTYPE html>
<html>
<head>
<title>CSV Column Mapping in PHP</title>
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<script src="http://code.jquery.com/jquery.js"></script>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css">
<style>
.table tbody tr th
{
min-width: 200px;
}
.table tbody tr td
{
min-width: 200px;
}
</style>
</head>
<body>
<div class="container">
<br />
<br />
<h1 align="center">CSV Column Mapping in PHP</h1>
<br />
<div id="message"></div>
<div class="panel panel-default">
<div class="panel-heading">
<h3 class="panel-title">Select CSV File</h3>
</div>
<div class="panel-body">
<div class="row" id="upload_area">
<form method="post" id="upload_form" enctype="multipart/form-data">
<div class="col-md-6" align="right">Select File</div>
<div class="col-md-6">
<input type="file" name="file" id="csv_file" />
</div>
<br /><br /><br />
<div class="col-md-12" align="center">
<input type="submit" name="upload_file" id="upload_file" class="btn btn-primary" value="Upload" />
</div>
</form>
</div>
<div class="table-responsive" id="process_area">
</div>
</div>
</div>
</div>
</body>
</html>
<script>
$(document).ready(function(){
$('#upload_form').on('submit', function(event){
event.preventDefault();
$.ajax({
url:"upload.php",
method:"POST",
data:new FormData(this),
dataType:'json',
contentType:false,
cache:false,
processData:false,
success:function(data)
{
if(data.error != '')
{
$('#message').html('<div class="alert alert-danger">'+data.error+'</div>');
}
else
{
$('#process_area').html(data.output);
$('#upload_area').css('display', 'none');
}
}
});
});
var total_selection = 0;
var first_name = 0;
var last_name = 0;
var email = 0;
var column_data = [];
$(document).on('change', '.set_column_data', function(){
var column_name = $(this).val();
var column_number = $(this).data('column_number');
if(column_name in column_data)
{
alert('You have already define '+column_name+ ' column');
$(this).val('');
return false;
}
if(column_name != '')
{
column_data[column_name] = column_number;
}
else
{
const entries = Object.entries(column_data);
for(const [key, value] of entries)
{
if(value == column_number)
{
delete column_data[key];
}
}
}
total_selection = Object.keys(column_data).length;
if(total_selection == 3)
{
$('#import').attr('disabled', false);
first_name = column_data.first_name;
last_name = column_data.last_name;
email = column_data.email;
}
else
{
$('#import').attr('disabled', 'disabled');
}
});
$(document).on('click', '#import', function(event){
event.preventDefault();
$.ajax({
url:"import.php",
method:"POST",
data:{first_name:first_name, last_name:last_name, email:email},
beforeSend:function(){
$('#import').attr('disabled', 'disabled');
$('#import').text('Importing...');
},
success:function(data)
{
$('#import').attr('disabled', false);
$('#import').text('Import');
$('#process_area').css('display', 'none');
$('#upload_area').css('display', 'block');
$('#upload_form')[0].reset();
$('#message').html("<div class='alert alert-success'>"+data+"</div>");
}
})
});
});
</script>
import.php
<?php
//import.php
if(isset($_POST["first_name"]))
{
$connect = new PDO("mysql:host=localhost; dbname=testing", "root", "");
session_start();
$file_data = $_SESSION['file_data'];
unset($_SESSION['file_data']);
foreach($file_data as $row)
{
$data[] = '("'.$row[$_POST["first_name"]].'", "'.$row[$_POST["last_name"]].'", "'.$row[$_POST["email"]].'")';
}
if(isset($data))
{
$query = "
INSERT INTO csv_file
(first_name, last_name, email)
VALUES ".implode(",", $data)."
";
$statement = $connect->prepare($query);
if($statement->execute())
{
echo 'Data Imported Successfully';
}
}
}
?>
So, In this tutorial, we have step by step sees how can we map CSV file column or define CSV file column and then after we have import or insert that define or selected column data into Mysql table by using PHP with Ajax jQuery. IF you have find any simple way for map csv file column in PHP then you are welcome and Let us know in the comments section.
great job
ReplyDeleteplease always put a download link also at the end
ReplyDeleteHow i use "Do Not Import" function?
ReplyDeletecould that work with excel importing or only with CSV ?
ReplyDeleteThanks for sharing this tutorial however there is an error in the code. After mapping a column and then changing it to a different one, still keeps the previous mapped reference. As a result, your app does not allow another field to be mapped to it.
ReplyDelete