473,322 Members | 1,540 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

Duplicate Records in DB when importing from CSV File

Hello,

The code below is used to grab each row from a CSV file (except the
first row) and insert it into a database, as long as it's not already
in the DB.

If I echo each row's data, it appears only once on the page. But, for
some reason it inserts each row into the database 3 times. The code
that echos the data, and the DB insertion code all appear within the
same for loop.

The database insertion code (below) works perfectly in another script.
It's just copied/pasted into the for loop.

Any ideas would be greatly appreciated! :-)

$row = 0;
$handle = fopen ("some-file.csv","r");
while ($data = fgetcsv ($handle, 1000, ",")) {
if($row == 0){
// set the varible array
$num = count ($data);
$csvNames = array();
for ($c=0; $c < $num; $c++) {
$csvNames[$c] = $data[$c];
eval("$" . $data[$c] . " = array();");
}
$row = 1;
}else{
$num = count ($data);
$row++;
for ($c=0; $c < $num; $c++) {
$buildEval = "$" . $csvNames[$c] . " = \"" . $data[$c] .
"\";";
eval($buildEval);
echo $buildEval . "<br>"; // this properly displays only one
row's data from the CSV file, then displays the next row in the CSV
file when it loops again.
// BUT FOR SOME REASON, THE DB CODE BELOW INSERTS EACH ROW FROM THE
CSV FILE 3 TIMES.

// Set variables with current date and time to be inserted into DB
later
$appDate = date('Y-m-d');
$appTime = date('H:i:s');

// Prepare the phone numbers for easy input into the database further
down this page.
$fHomePhoneCombined = $fHomePhone1 . "-" . $fHomePhone2 . "-" .
$fHomePhone3;
$fBusinessPhoneCombined = $fBusinessPhone1 . "-" . $fBusinessPhone2 .
"-" . $fBusinessPhone3;

// Connect to the database
$db = mysql_connect ("localhost", "username", "password");
mysql_select_db ("mydatabase");

// Check to see if the record is new,
$newRecord = 1;
$queryNewRecord = mysql_query("SELECT * FROM cust_test WHERE
first_name = '$fFirstName' AND last_name = '$fLastName' AND email =
'$fEmail'");
if (mysql_num_rows($queryNewRecord) > 0){
$newRecord = 0;
}

// If it's NOT a new record, insert the contents into cust_test_bad DB
// I'll insert this code later. It's not important for this test.
// Write the information to the customers table, if it's a new record.

if ($newRecord == 1){
$query = "INSERT INTO cust_test SET
application_date = '$appDate',
application_time = '$appTime',
first_name = '$fFirstName',
last_name = '$fLastName',
email = '$fEmail',
address = '$fAddress',
state = '$fState',
zip = '$fZip',
home_phone = '$fHomePhoneCombined',
best_time_to_call = '$fBestTime',
lead_buyer = '$lbuy'";
$result = mysql_query($query) or die('Failed because:
'.mysql_error());
}


} // end of for ($c=0; $c < $num; $c++)
}
}
fclose ($handle);
Jul 17 '05 #1
2 5892
Eric Linders wrote:
Hello,

The code below is used to grab each row from a CSV file (except the
first row) and insert it into a database, as long as it's not already
in the DB.

If I echo each row's data, it appears only once on the page. But, for
some reason it inserts each row into the database 3 times. The code
that echos the data, and the DB insertion code all appear within the
same for loop.

The database insertion code (below) works perfectly in another script.
It's just copied/pasted into the for loop.

Any ideas would be greatly appreciated! :-)

$row = 0;
$handle = fopen ("some-file.csv","r");
while ($data = fgetcsv ($handle, 1000, ",")) {
if($row == 0){
// set the varible array
$num = count ($data);
$csvNames = array();
for ($c=0; $c < $num; $c++) {
$csvNames[$c] = $data[$c];
eval("$" . $data[$c] . " = array();");
}
$row = 1;
}else{
$num = count ($data);
$row++;
for ($c=0; $c < $num; $c++) {
$buildEval = "$" . $csvNames[$c] . " = \"" . $data[$c] .
"\";";
eval($buildEval);
echo $buildEval . "<br>"; // this properly displays only one
row's data from the CSV file, then displays the next row in the CSV
file when it loops again.
// BUT FOR SOME REASON, THE DB CODE BELOW INSERTS EACH ROW FROM THE
CSV FILE 3 TIMES.

// Set variables with current date and time to be inserted into DB
later
$appDate = date('Y-m-d');
$appTime = date('H:i:s');

// Prepare the phone numbers for easy input into the database further
down this page.
$fHomePhoneCombined = $fHomePhone1 . "-" . $fHomePhone2 . "-" .
$fHomePhone3;
$fBusinessPhoneCombined = $fBusinessPhone1 . "-" . $fBusinessPhone2 .
"-" . $fBusinessPhone3;

// Connect to the database
$db = mysql_connect ("localhost", "username", "password");
mysql_select_db ("mydatabase");

// Check to see if the record is new,
$newRecord = 1;
$queryNewRecord = mysql_query("SELECT * FROM cust_test WHERE
first_name = '$fFirstName' AND last_name = '$fLastName' AND email =
'$fEmail'");
if (mysql_num_rows($queryNewRecord) > 0){
$newRecord = 0;
}

// If it's NOT a new record, insert the contents into cust_test_bad DB
// I'll insert this code later. It's not important for this test.
// Write the information to the customers table, if it's a new record.

if ($newRecord == 1){
$query = "INSERT INTO cust_test SET
application_date = '$appDate',
application_time = '$appTime',
first_name = '$fFirstName',
last_name = '$fLastName',
email = '$fEmail',
address = '$fAddress',
state = '$fState',
zip = '$fZip',
home_phone = '$fHomePhoneCombined',
best_time_to_call = '$fBestTime',
lead_buyer = '$lbuy'";
$result = mysql_query($query) or die('Failed because:
'.mysql_error());
}


} // end of for ($c=0; $c < $num; $c++)
}
}
fclose ($handle);

I got so hacked off with this that I made the import a two phase task.
First I inserted into a temp table the same shape, then I inserted into
the real one using a select distinct where not exists () from the temp
table.

Also had the advantage that the data in the live tables was out of step
for about a second, which is ok when you're driving a website ( well, I
reckon anyway ).

Steve.
Jul 17 '05 #2
Steve <Th*****@Aint.valid> wrote in message
<SNIP>

fclose ($handle);
I got so hacked off with this that I made the import a two phase task.
First I inserted into a temp table the same shape, then I inserted into
the real one using a select distinct where not exists () from the temp
table.

Also had the advantage that the data in the live tables was out of step
for about a second, which is ok when you're driving a website ( well, I
reckon anyway ).

Steve.

You would think that my check of code that checks if it's a new record
would accomplish the task -- at least it does in my other page where
this code is found. So strange! I just do NOT get why it's happening.
Jul 17 '05 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: JStrummer | last post by:
I just put together a PHP mailing list sign-up page that posts to a mySQL DB. The PHP script prevents INSERTs when the email address is already located in the database. Problem: I need to import...
2
by: ms | last post by:
Access 2000: I am trying to delete duplicate records imported to a staging table leaving one of the duplicates to be imported into the live table. A unique record is based on a composite key of 3...
4
by: KT | last post by:
Is there any one click solution that would do the trick? I would like to create a button, so the person who maintains the database can perform clean up work to delete duplicate records which...
0
by: David Garamond | last post by:
postgresql 7.4.0, redhat 7.3 (under vmware 4.0 on win2k) Windows crashed and some of the files on Redhat got corrupted, including some files in /var/lib/pgsql/data/pg_xlog/. When I tried to start...
6
by: sara | last post by:
I have a procedure to automate bringing several Excel files into our Access tables, on a daily basis. The problem is that if the user has a problem, and tries to run the import again (maybe 3...
11
by: kaisersose1995 | last post by:
Hi, I've got an import procedure working, using a standard import specification to import a .csv file into a temporary table. The problem i'm having is that i have 4 different sets of borrower...
9
by: Tom_F | last post by:
To comp.databases.ms-access -- I just discovered, to my more than mild dismay, that some tables in my Microsoft Access 2003 database have duplicate numbers in the "AutoNumber" field. (Field...
5
by: Brett Barry: Go Get Geek! | last post by:
Hello, Can someone please paste VBA code and how I would go about doing this?: I have a customer table with over 6000 duplicates in the field called "Customer". While the customer names may...
3
by: smugcool | last post by:
HI, I am having a database in access. Where user imports various data which is in excel format. I want them to stop importing any duplicate record. If any duplicate record is been trying to import...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.