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

Recommended Answers

All 21 Replies

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

Hi

Thanks for your post and suggestion.

I will catch you after testing in my machine

HI

I have tried it, but it is not getting insert into DB

have you check your csv before convert it to xml fields name because fields name should be same as field name in the DB.if the field name are different csv file than it not catches the field name located in DB and insert data into it

Member Avatar for 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?

The CSV file has a duplicate entries.

mean it has dulipcate field name in csv can you post the csv here

Member Avatar for 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.

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>
Member Avatar for 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.

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.

sorry ardav, I didn't see 'page 2' comment before posting. I used his original file and altered... anyway.

hi

Thanks for your post

Member Avatar for diafol

Did either of us solve your problem?

Hi, is this already solved? im interested. thanks

Hai I having One Problem

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...

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**

Member Avatar for 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.

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.