PHP CSV upload - extra lines

[jeeves245]

Hey guys,

 

I have this script to upload a CSV file into the database. The CSV file is saved from an Excel spreadsheet file manually (the script doesn't do this). But for some reason when adding the CSV file values to the DB, it creates a couple of extra rows each time (possibly because Excel is saving blank lines or something).

 

Is there any way to make the script ignore any blank spaces/lines in the CSV file when uploading it?

 

<?php

$target_path = "./uploads/";
$target_path = $target_path . basename( $_FILES['uploadedfile']['name']); 

$con = mysql_connect("localhost","user","pass");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("db", $con);

$file_handle = fopen("./uploads/" .  basename( $_FILES['uploadedfile']['name'])."", "rb");

while (!feof($file_handle) ) {

$sql="INSERT INTO smail (deliveryDate, psNumber, numItems,
volume, customerName, address1, address2, address3)
VALUES
('$datestore','$parts[2]','$parts[3]','$parts[7]','$parts[1]','$parts[4]','$parts[5]','$parts[6]')";

mysql_query($sql);

}

fclose($file_handle);


?>

 

Thanks in advance.

[asmith]

The first or second row field values are all blank? I mean it insert 2 rows, one with values and one making all fields blank?

[jeeves245]

The first or second row field values are all blank? I mean it insert 2 rows, one with values and one making all fields blank?

 

Sorry not too sure I get what you mean?

In the CSV file when I open it in notepad++ the cursor always starts on a new line at the bottom of the page. When I press backspace and get rid of that line, it uploads fine. So I need a way to make PHP ignore that line completely.

[asmith]

Do this modify:

 

<?
while (!feof($file_handle) ) {

foreach ($parts as $theField)
     if ($theField == '')
          continue;

$sql="INSERT INTO smail (deliveryDate, psNumber, numItems,
volume, customerName, address1, address2, address3)
VALUES
('$datestore','$parts[2]','$parts[3]','$parts[7]','$parts[1]','$parts[4]','$parts[5]','$parts[6]')";

mysql_query($sql);

}
?>

 

Notcie as the foreach part, If goes through each of your $parts variable and if one of them was totally empty if will ignore that loop and goes to next.

 

if there will be times that one of the field CAN be empty and you still wanna record other values, You can modify the foreach part to set conditions on how to set it for the ignore.

[jeeves245]

Do this modify:

 

<?
while (!feof($file_handle) ) {

foreach ($parts as $theField)
     if ($theField == '')
          continue;

$sql="INSERT INTO smail (deliveryDate, psNumber, numItems,
volume, customerName, address1, address2, address3)
VALUES
('$datestore','$parts[2]','$parts[3]','$parts[7]','$parts[1]','$parts[4]','$parts[5]','$parts[6]')";

mysql_query($sql);

}
?>

 

Notcie as the foreach part, If goes through each of your $parts variable and if one of them was totally empty if will ignore that loop and goes to next.

 

if there will be times that one of the field CAN be empty and you still wanna record other values, You can modify the foreach part to set conditions on how to set it for the ignore.

 

A lot of my $parts values will be empty. Is there a way to modify the condition so it only ignores it when an entire line is empty?

[asmith]

Yes:

 

<?php
while (!feof($file_handle) ) {

$fieldNumbers = 0;
foreach ($parts as $theField)
     if ($theField == '')
          $fieldNumbers++;

if ($fieldNumbers == count($parts))
     continue;

$sql="INSERT INTO smail (deliveryDate, psNumber, numItems,
volume, customerName, address1, address2, address3)
VALUES
('$datestore','$parts[2]','$parts[3]','$parts[7]','$parts[1]','$parts[4]','$parts[5]','$parts[6]')";

mysql_query($sql);

}
?>

 

I set a counter to count how many fields are empty. The counter is $fieldNumbers

After counting them, I checked to see if the number of empty fields ($fieldNumbers) is equal to the number of fields (count($parts)), If they were equal then it will skip the current loop. (continue;)

[jeeves245]

Thanks for that

 

Although I just thought of a better way I think...

 

What if I take $parts[2] that should ALWAYS have a value in it, and make the for loop like

 

for ($parts)

    if ($parts[2] != 0)

          continue;

 

So if $parts[2] does NOT equal 0 (i.e. it DOES have a value), continue. Would that work?

[asmith]

Don't get the continue; meaning wrong ^^

 

The continue;  SKIPS the current loop, not continuing it.

and you dont' need for loop anymore since you are pointing to an specific key of the $parts.

 

So you need to do this:

if ($parts[2] == 0)

      continue;

[jeeves245]

That works great, thank you.

 

Do you have any idea why it might be adding the last line of the CSV file into the database twice?

[asmith]

You mean in the future? You are wondering if it would add a row twice again in the future? Didn't get you ^^ didn't you just solve your problem ? XD

[jeeves245]

You mean in the future? You are wondering if it would add a row twice again in the future? Didn't get you ^^ didn't you just solve your problem ? XD

 

Yes, solved the problem. But I just ran the script again and a new problem turned up. It is adding the last line of the CSV file into the database twice for some reason. I was just wondering if you might have any idea why

[asmith]

Is the second row still an empty one?

[jeeves245]

Is the second row still an empty one?

 

No, it takes the last non-empty row of the CSV file and saves it to the database twice.

[asmith]

I can give you a way on how to avoid this. But I'd rather not, Because since I haven't seen the file myself I don't know what's going on for sure.

 

Are you sure of the file content?

[jeeves245]

I can give you a way on how to avoid this. But I'd rather not, Because since I haven't seen the file myself I don't know what's going on for sure.

 

Are you sure of the file content?

 

I'm positive of the file content. I'll give you an example of the file. Let's assume that this is the file (i've censored out address and customer details for privacy reasons).

 

16/07/2009,customername,170880,1,address1,"address2, address3",,0.10

16/07/2009,customername,170936,1,address1,"address2, address3",,0.91

16/07/2009,customername,170942,1,address1,address2,address3,0.09

 

So if I uploaded this using the script I posted above, the bottom line is put into the database twice.