Hi
i am having 3rd party csv file. i am having php uploading script to upload the datas into my table.
while uploading datas into my mysql Table mean while it has to remove duplicate entries rows from CSV file.
Please help me
rpv_sen 59 Junior Poster
extemer -17 Junior Poster Banned
i had done this task a month ago.I was given was to import csv file to mysql DB.
Step 1
Easily and good approach is to save the csv file as xml doc as option is present to save it as xml doc.
Step 2
Then import the xml doc to mysql.
Here is code below
index1.php
<html>
<head>
<style type="text/css">
body{
font-family:"Lucida Grande", "Lucida Sans Unicode", Verdana, Arial, Helvetica, sans-serif;
font-size:12px;
}
p, h1, form, button{border:0; margin:0; padding:0;}
.spacer{clear:both; height:1px;}
/* ----------- My Form ----------- */
.myform{
margin:0 auto;
width:400px;
padding:14px;
}
/* ----------- stylized ----------- */
#stylized{
border:solid 2px #B77DDF2;
background:#ebf4bf;
}
#stylized h1 {
font-size:14px;
font-weight:bold;
margin-bottom:8px;
}
#stylized p{
font-size:11px;
color:#666666;
margin-bottom:20px;
border-bottom:solid 1px #b7ddf2;
padding-bottom:10px;
}
#stylized label{
display:block;
font-weight:bold;
text-align:right;
width:140px;
float:left;
}
#stylized .small{
color:#666666;
display:block;
font-size:11px;
font-weight:normal;
text-align:right;
width:140px;
}
#stylized input{
float:left;
font-size:12px;
padding:4px 2px;
border:solid 1px #aacfe4;
width:200px;
margin:2px 0 20px 10px;
}
#stylized button{
clear:both;
margin-left:150px;
width:125px;
height:31px;
background:#666666 url(img/button.png) no-repeat;
text-align:center;
line-height:31px;
color:#FFFFFF;
font-size:11px;
font-weight:bold;
}
</style>
</head>
<body>
<div id="stylized" class="myform">
<form enctype="multipart/form-data"
action="import2.php" method="post">
<input type="hidden" name="MAX_FILE_SIZE" value="2000000" />
<h1>Sign-up form</h1>
<p></p>
<label>File
<span class="small">Brows CSV file</span>
</label>
<input type="file" name="file" id="file" />
<button type="submit">Upload</button>
<div class="spacer"></div>
</form>
</div>
</body>
</html>Here is import file
import2.php
<?php
// error_reporting(0);
$data = array();
function add_person( $Name, $EmailAddress, $Company, $City, $Country, $Status ) //change here
{
global $data;
mysql_connect("localhost","root","") or die(mysql_error()); // Change here
mysql_select_db("n"); //Change here
$data []= array(
'Name' => $Name, // <---////////
'EmailAddress' => $EmailAddress, //
'Company' => $Company, // Changes here
'City' => $City, //
'Country' => $Country, //
'Status' => $Status // <----//////
);
}
if ( $_FILES['file']['tmp_name'] )
{
$dom = DOMDocument::load( $_FILES['file']['tmp_name'] );
$rows = $dom->getElementsByTagName( 'Row' );
$first_row = true;
foreach ($rows as $row)
{
if ( !$first_row )
{
$Name = ""; // <---////////
$EmailAddress = ""; //
$Company = ""; //
$City = ""; // Changes here
$Country = ""; //
$Status = ""; // <----//////
$index = 1;
$cells = $row->getElementsByTagName( 'Cell' );
foreach( $cells as $cell )
{
$ind = $cell->getAttribute( 'Index' );
if ( $ind != null ) $index = $ind;
if ( $index == 1 ) $Name = $cell->nodeValue;
if ( $index == 2 ) $EmailAddress = $cell->nodeValue;
if ( $index == 3 ) $Company = $cell->nodeValue;
if ( $index == 4 ) $City = $cell->nodeValue;
if ( $index == 5 ) $Country = $cell->nodeValue;
if ( $index ==6 ) $Status = $cell->nodeValue;
$index += 1;
}
add_person( $Name, $EmailAddress, $Company, $City, $Country, $Status );
}
$first_row = false;
}
}
?>
<html>
<body>
These records have been added to the database:
<table>
<?php foreach( $data as $row ) { ?>
<tr>
<td><?php ( $name1 = $row['Name'] ); //echo $name1 ?></td>
<td><?php ( $name2 = $row['EmailAddress'] ); //echo $name2?></td>
<td><?php ($name3 = $row['Company'] ); //echo $name3?></td>
<td><?php ($name4 = $row['City'] ); //echo $name4?></td> // Changes here
<td><?php ($name5 = $row['Country'] ); //echo $name5?></td>
<td><?php ($name6 = $row['Status'] ); //echo $name6?></td>
</tr>
<?php
$sql = mysql_query("INSERT INTO tblclients (Name,EmailAddress,Company,City,Country,Status) VALUES ('$name1','$name2','$name3','$name4','$name5','$name6')"); // Changes here
} ?>
</table>
</body>
</html>Hope this help
rpv_sen 59 Junior Poster
Hi
Thanks for your post and suggestion.
I will catch you after testing in my machine
rpv_sen 59 Junior Poster
HI
I have tried it, but it is not getting insert into DB
diafol
I didn't quite understand your duplicate entries problem:
1. Duplicate entries in the csv file itself
OR
2. Records in DB are duplicateed in the CSV being uploaded
Which one?
rpv_sen 59 Junior Poster
The CSV file has a duplicate entries.
extemer -17 Junior Poster Banned
mean it has dulipcate field name in csv can you post the csv here
diafol
Read in the CSV and explode it on "\n" to get an array of records, say in $lines.
Then use array_unique() to return all unique records into a new array.
loop using foreach...
Explode the array again on "," to get a multidimensional array.
Your data is now available as $array[$row][$column]
Both have a base starting on 0, with $array[0][0], being the first item.
Personally I'd build up a VALUES clause in the loop to use in a single SQL query as opposed to querying at every stage iteration in the loop.
rpv_sen 59 Junior Poster
Hi At present i am using the below script, But i am unable to remove the duplicate values.
<?php
//database connect info here
include('config.php');
//check for file upload
if(isset($_FILES['csv_file']) && is_uploaded_file($_FILES['csv_file']['tmp_name'])){
//upload directory
$upload_dir = "csv_dir/";
//create file name
$file_path = $upload_dir . $_FILES['csv_file']['name'];
//move uploaded file to upload dir
if (!move_uploaded_file($_FILES['csv_file']['tmp_name'], $file_path)) {
//error moving upload file
echo "Error moving file upload";
}
//open the csv file for reading
$handle = fopen($file_path, 'r');
//turn off autocommit and delete the product table
mysql_query("SET AUTOCOMMIT=0");
mysql_query("BEGIN");
while (($data = fgetcsv($handle, 1000, ',')) !== FALSE) {
//Access field data in $data array ex.
//Use data to insert into db
$sql = sprintf("INSERT into essl_att(essl_date,emp_code,emp_name,company,department,category,designation,grade,team,shift,in_time,out_time,duration,late_by,early_by,status,punch_records,overtime) values('$data[0]','$data[1]','$data[2]','$data[3]','$data[4]','$data[5]','$data[6]','$data[7]','$data[8]','$data[9]','$data[10]','$data[11]','$data[12]','$data[13]','$data[14]','$data[15]','$data[16]','$data[17]')");
mysql_query($sql) or (mysql_query("ROLLBACK") and die(mysql_error() . " - $sql"));
}
//echo $sql;
//commit the data to the database
mysql_query("COMMIT");
mysql_query("SET AUTOCOMMIT=1");
//delete csv file
unlink($file_path);
}
?>
<form action="<?php echo $_SERVER["PHP_SELF"];?>" method="post" enctype="multipart/form-data">
<input type="file" name="csv_file">
<input type="submit" name="csv_submit" value="Upload CSV File">
</form> diafol
Try this:
function mres($myarray){
return array_map("mysql_real_escape_string",$myarray);
}
$str = file_get_contents($filename); //the uploaded file
$lines = explode("\r\n",$str);
//$entries = array_shift($lines);//if you want to delete the first row (header info??)
$unique = array_unique($lines);//rename $lines to $entries if used the commented line above
foreach($unique as $row){
$bits = explode(",",$row);
$cleanbits = mres($bits);
$value_bits[] = "('{$cleanbits[0]}','{$cleanbits[1]}','{$cleanbits[6]}','{$cleanbits[2]}')";
// build up value list as you require from array items, leaving out quotes if an integer, etc.
}
$value_string = implode(",",$value_bits);
$q = mysql_query("INSERT into essl_att(essl_date,emp_code,emp_name,company,department,category,designation,grade,team,shift,in_time,out_time,duration,late_by,early_by,status,punch_records,overtime) values($value_string)");This could be done with fewer lines by reusing variables perhaps or combining functions, but I thought that this would be clearer.
The array_unique strips duplicate lines within the file.
It has the advantage of only running ONE query right at the end.
NOT TESTED!
//EDIT
OK, tested - works for me.
ddymacek 15 Posting Whiz
I've made some relevant changes to your code to try to extract unique records.
<?php
//database connect info here
include('config.php');
//check for file upload
if(isset($_FILES['csv_file']) && is_uploaded_file($_FILES['csv_file']['tmp_name'])){
//upload directory
$upload_dir = "csv_dir/";
//create file name
$file_path = $upload_dir . $_FILES['csv_file']['name'];
//move uploaded file to upload dir
if (!move_uploaded_file($_FILES['csv_file']['tmp_name'], $file_path)) {
//error moving upload file
echo "Error moving file upload";
// added die, I would change to exit or something, if this doesn't work, your file handle code will blow up below this.
die("Error moving file upload");
} else {
//echo "success contine to process file<br>"; (I actually tested this code)
}
//open the csv file for reading
$handle = fopen($file_path, 'r');
// sanitize you data. first read your whole file into an array since you used $data I'll use $lines as ardav suggested
$lines = array();
while(!feof($handle)) {
// push each line of the file into your array
array_push($lines,fgets($handle));
}
// lets see if we have anything in $lines should have one for every row in your file...
echo "line count = " . count($lines) . "<br>";
// php function to only have unique values in your array
$lines = array_unique($lines);
echo "lines have been uniqued, new line count = " . count($lines) . "<br>";
//turn off autocommit and delete the product table
//mysql_query("SET AUTOCOMMIT=0");
//mysql_query("BEGIN");
// we have already read the whole file its contents are now held in $lines, but we must process further...
// so since we a dealing with an array lets do a foreach statement.
//while (($data = fgetcsv($handle, 1000, ',')) !== FALSE) {
// again back to ardav's point about using foreach, it is such a great way to work with arrays.
foreach($lines as $line) {
// eplode each line on the commas
$data = explode(",", $line);
//Access field data in $data array ex.
//Use data to insert into db
$sql = sprintf("INSERT into essl_att(essl_date,emp_code,emp_name,company,department,category,designation,grade,team,shift,in_time,out_time,duration,late_by,early_by,status,punch_records,overtime) values('$data[0]','$data[1]','$data[2]','$data[3]','$data[4]','$data[5]','$data[6]','$data[7]','$data[8]','$data[9]','$data[10]','$data[11]','$data[12]','$data[13]','$data[14]','$data[15]','$data[16]','$data[17]')");
//echo "sql = " . $sql . "<br>";
mysql_query($sql) or (mysql_query("ROLLBACK") and die(mysql_error() . " - $sql"));
}
echo $sql;
//commit the data to the database
mysql_query("COMMIT");
mysql_query("SET AUTOCOMMIT=1");
delete csv file
unlink($file_path);
}
?>
<form action="<?php echo $_SERVER["PHP_SELF"];?>" method="post" enctype="multipart/form-data">
<input type="file" name="csv_file">
<input type="submit" name="csv_submit" value="Upload CSV File">
</form>I also tested the code and it should do what you want.
ddymacek 15 Posting Whiz
sorry ardav, I didn't see 'page 2' comment before posting. I used his original file and altered... anyway.
rpv_sen 59 Junior Poster
hi
Thanks for your post
diafol
Did either of us solve your problem?
Rodwin_1 0 Newbie Poster
Hi, is this already solved? im interested. thanks
Mathavan_1 0 Newbie Poster
Hai I having One Problem
Mathavan_1 0 Newbie Poster
diafol
happygeek 2,411 Most Valuable Poster Team Colleague Featured Poster
Mathavan_1 I suggest you repost your 'one problem' whatever that may be as a new post. You have posted as a somewhat puzzling reply to a five year old thread, it's highly unlikely anyone will be able to help you if they don't know what help you are after and don't see your request for it...
KheL_1 0 Newbie Poster
hi. anyone can help me?
i run this code and i got error:
**Warning: DOMDocument::load(): Start tag expected, '<' not found in file:/C:/xampp/tmp/phpF242.tmp, line: 1 in C:\xampp\htdocs\contoh\import.php on line 21
Fatal error: Call to a member function getElementsByTagName() on boolean in C:\xampp\htdocs\contoh\import.php on line 22**
diafol
Of course KheL_1 - see line 16, replace a missing ',' at the end of the first function parameter, then go shine up my crystal ball for me. You have posted a completely random issue to a dead thread. Furthermore, you make it so vague as to be impossible to help you. Programming is obviously beyond you, pick another hobby.
//EDIT
I know this is a waste of time - fly-by poster.
bhuvi_2 0 Newbie Poster
hi i want to know how to delete duplicate rows in imported csv files in phpmyadmin in php..below is my code to import csv file in php,i want to delete duplicated rows in my imported csv files which is in database of xampp(phpmyadmin)can anyone add thecode for it
<?php
$user = 'root';
$pass = '';
$db = 'testdb';
$db = new mysqli('localhost',$user,$pass,$db) or die("unable to connect");
$connect = mysqli_connect("localhost", "root", '', "testdb");
echo"great work!!!";
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>
?> Be a part of the DaniWeb community
We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.