CSV Headaches

[rseigel]

I'm having trouble using my csv file to update my inventory.

 

Here's how I get the CSV from a remote server:

 

copy('http://www.mysupplie...0012538&fmt=csv' 'GeneratedList.csv');

 

Here's how the CSV looks when I open it in Notepad++.

 

"Item Number","Available Inventory","Retail Price","Each Price"
"03657","456","12.95","2.77"
"101","779","23.95","3.28"
"10212","42","144.00","48.00"
"103","783","26.95","3.65"
"10312","19","180.00","63.16"
"105","1182","23.95","2.90"
"107","171","25.95","3.70"
"118","2888","25.95","3.69"
"119","166","24.95","3.48"
"12512","25","240.00","84.20"
"12612","17","216.00","75.80"
"127","0","24.95","3.24"
"128","8614","23.95","3.35"
"129","5366","23.95","3.28"
"130","844","30.95","5.48"
"131","40","19.95","2.90"
"132","1985","23.95","3.27"
"133","0","19.95","2.86"
"134","2","24.95","3.60"
"135","372","23.95","3.99"
"136","1420","25.95","3.79"
"137","3294","24.95","3.47"
etc.....

 

I've steped back to just try to parse and print it to the screen to see what happens with this code:

 

$row = 1;
if (($handle = fopen("GeneratedList.csv", "r")) !== FALSE) {
while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
 $num = count($data);
 echo "<p> $num fields in line $row: <br /></p>\n";
 $row++;
 for ($c=0; $c < $num; $c++) {
	 echo $data[$c] . "<br />\n";
 }
}
fclose($handle);
}

 

and I get this output:

 

105 fields in line 1:


Item Number
Available Inventory
Retail Price
Each Price "03657"
456
12.95
2.77 "101"
779
23.95
3.28 "10212"
42
144.00
48.00 "103"
783
26.95
3.65 "10312"
19
180.00
63.16 "105"
1182
23.95
2.90 "107"
171
25.95
3.70 "118"
2878
25.95
3.69 "119"
166
24.95
3.48 "12512"
25
240.00
84.20 "12612"
17
216.00
75.80 "127"
0
24.95
3.24 "128"
8614
23.95
3.35 "129"
5366
23.95
3.28 "130"
844
30.95
5.48 "131"
40
19.95
2.90 "132"
1985
23.95
3.27 "133"
0
19.95
2.86 "134"
2
24.95
3.60 "135"
372
23.95
3.99 "136"
1420
25.95
3.79 "137"
3294
24.95
3.47 "138"
0
28.95
4.86 "141"
41
22.95
3.90 "142"
1187
24.95
3.40 "142707"
10
269.95
59.99 "142708"
13
493.95
109.76 "142711"
1
674.95
150.00 "142718"
3
787.95
175.00 "142725"
3
265.95
59.00 "143"
383
26.95
3.69 "144"
416
22.95
3.99 "145"
295
26.95
3.84 "146"
1475
6962 fields in line 2:
25.95
3.80 "148"
1232
22.95
3.89 "150"

 

It looks like somewhere along the line it's not recognizing the end of each line. It opens as expected in Calc and gives me 4 fields per line.

 

If I manually nuke the header and upload it then it works fine. This is where I get confused.

 

Any ideas or inspiration much appreciated.

 

Ron

[PFMaBiSmAd]

If I manually nuke the header and upload it then it works fine.

 

Editing and saving the file probably converts the new-lines to something that php can detect by default.

 

See the following note in the fgetcsv documentation -

Note

: If PHP is not properly recognizing the line endings when reading files either on or created by a Macintosh computer, enabling the auto_detect_line_endings run-time configuration option may help resolve the problem.

Edited January 22, 2013 by PFMaBiSmAd

[rseigel]

Wooooohooooo!

 

I just added:

 

ini_set('auto_detect_line_endings', true);
 $contents = file('GeneratedList.csv');

 

to the top and the output is now perfect:

 

4 fields in line 1:

Item Number
Available Inventory
Retail Price
Each Price
4 fields in line 2:

03657
456
12.95
2.77
4 fields in line 3:
101
778
23.95
3.28

 

Thanks a million for the kick in the right direction.

[rseigel]

Crap!

 

When I go back to my regular code:

 

ini_set('auto_detect_line_endings', true);
 $contents = file('GeneratedList.csv');
$handle = fopen("GeneratedList.csv", "r+");
$header = 'true';
while (($data = fgetcsv($handle, 100000, ",", '"')) !== FALSE) {
if ($header = 'true') {
 $header = 'false';
 break;
}
   $num = count($data);
   for ($c=0; $c < $num; $c++) {
    if ($c = 1) {
  $supplier_reference = $data[($c-1)];
 }
    if ($c = 2) {
  $quantity = $data[($c-1)];
  mysql_query("UPDATE stock_available, product SET stock_available.quantity = $quantity WHERE stock_available.id_product = product.id_product AND product.supplier_reference = '$supplier_reference'")
   or die(mysql_error());
  mysql_query("UPDATE stock_available, product_attribute SET stock_available.quantity = $quantity WHERE stock_available.id_product_attribute = product_attribute.id_product_attribute AND product_attribute.supplier_reference = '$supplier_reference'")
   or die(mysql_error());
    }
 if ($c = 3) {
 }
 if ($c = 4) {
 }
   }
}
fclose($handle);

 

It doesn't update the inventory as expected.

 

Any ideas?

 

Thanks,

 

Ron

[rseigel]

It's almost like mySQL is not recognizing the quantity as a number and isn't updating it.

 

::::

[rseigel]

The plot thickens.

 

$handle = fopen("GeneratedList.csv", "r+");
ini_set('auto_detect_line_endings', true);
 $contents = file('GeneratedList.csv');
$header = 'true';
while (($data = fgetcsv($handle, 100000, ",")) !== FALSE) {
if ($header = 'true') {
 $header = 'false';
 break;
}
   $num = count($data);
   for ($c=0; $c < $num; $c++) {
    if ($c = 1) {
  $supplier_reference = $data[($c-1)];
  echo $supplier_reference;
 }
    if ($c = 2) {
  $quantity = $data[($c-1)];
  echo $quantity;
  mysql_query("UPDATE stock_available, product SET stock_available.quantity = $quantity WHERE stock_available.id_product = product.id_product AND product.supplier_reference = '$supplier_reference'")
   or die(mysql_error());
  mysql_query("UPDATE stock_available, product_attribute SET stock_available.quantity = $quantity WHERE stock_available.id_product_attribute = product_attribute.id_product_attribute AND product_attribute.supplier_reference = '$supplier_reference'")
   or die(mysql_error());
    }
 if ($c = 3) {
 }
 if ($c = 4) {
 }
   }
}
fclose($handle);

 

I added echos to see what the values were and I get nothing being output.

 

Did I do something silly in the code or is this still a csv issue?

[rseigel]

Ok, I've narrowed it down.

 

If I download the csv manually and chop the header off and upload it works.

 

If I download the csv manually and leave the header as is it doesn't.

 

Here's the code I'm using on a clean csv file saved with calc with the header line intact.

 

$handle = fopen("GeneratedList.csv", "r+");
//ini_set('auto_detect_line_endings', true);
//  $contents = file('GeneratedList.csv');
$header = 'true';
while (($data = fgetcsv($handle, 100000, ",")) !== FALSE) {
   if ($header = 'true') {
       $header = 'false';
       break;
   }
   $num = count($data);
   for ($c=0; $c < $num; $c++) {
    if ($c = 1) {
           $supplier_reference = $data[($c-1)];
       }
    if ($c = 2) {
           $quantity = $data[($c-1)];
           mysql_query("UPDATE stock_available, product SET stock_available.quantity = $quantity WHERE stock_available.id_product = product.id_product AND product.supplier_reference = '$supplier_reference'")
               or die(mysql_error());
           mysql_query("UPDATE stock_available, product_attribute SET stock_available.quantity = $quantity WHERE stock_available.id_product_attribute = product_attribute.id_product_attribute AND product_attribute.supplier_reference = '$supplier_reference'")
               or die(mysql_error());
    }
       if ($c = 3) {
       }
       if ($c = 4) {
       }
   }
}
fclose($handle);

 

Sooooo...all I can figure out is that either the code to skip the header isn't right or for some reason the csv file is corrupted until I remove the header manually.

 

Help.

 

Thanks,

 

Ron

[salathe]

In all of your if(…) statements, you are using = (the assignment operator) where you should be using == (a comparison operator).

 

As for getting the header out of the way, you can do an initial fgetcsv() (and ignore the result, if you like) before the main loop that reads over the data.

Edited January 22, 2013 by salathe

[rseigel]

Nice! We're getting there.

 

This code:

 

$handle = fopen("GeneratedList.csv", "r+");
//ini_set('auto_detect_line_endings', true);
//  $contents = file('GeneratedList.csv');
fgetcsv($handle, 100000, ",");
while (($data = fgetcsv($handle, 100000, ",")) !== FALSE) {
   $num = count($data);
   for ($c=0; $c < $num; $c++) {
 if ($c == 1) {
  $supplier_reference = $data[($c-1)];
 }
    if ($c == 2) {
  $quantity = $data[($c-1)];
  mysql_query("UPDATE stock_available, product SET stock_available.quantity = $quantity WHERE stock_available.id_product = product.id_product AND product.supplier_reference = '$supplier_reference'")
   or die(mysql_error());
  mysql_query("UPDATE stock_available, product_attribute SET stock_available.quantity = $quantity WHERE stock_available.id_product_attribute = product_attribute.id_product_attribute AND product_attribute.supplier_reference = '$supplier_reference'")
   or die(mysql_error());
    }
 if ($c == 3) {
 }
 if ($c == 4) {
 }
   }
}
fclose($handle);

 

works perfectly on the clean csv file with headers that I saved with calc. Sometimes the simplest solutions are the best.

 

Now when I try to get the file dynamically with the following code:

 

 

copy('http://www.mysupplier.com/utilities/cgen.lasso?an8=10012538&fmt=csv', 'GeneratedList.csv');
//

$handle = fopen("GeneratedList.csv", "r+");
ini_set('auto_detect_line_endings', true);
 $contents = file('GeneratedList.csv');
fgetcsv($handle, 100000, ",");
while (($data = fgetcsv($handle, 100000, ",")) !== FALSE) {
   $num = count($data);
   for ($c=0; $c < $num; $c++) {
       if ($c == 1) {
           $supplier_reference = $data[($c-1)];
       }
    if ($c == 2) {
           $quantity = $data[($c-1)];
           mysql_query("UPDATE stock_available, product SET stock_available.quantity = $quantity WHERE stock_available.id_product = product.id_product AND product.supplier_reference = '$supplier_reference'")
               or die(mysql_error());
           mysql_query("UPDATE stock_available, product_attribute SET stock_available.quantity = $quantity WHERE stock_available.id_product_attribute = product_attribute.id_product_attribute AND product_attribute.supplier_reference = '$supplier_reference'")
               or die(mysql_error());
    }
       if ($c == 3) {
       }
       if ($c == 4) {
       }
   }
}
fclose($handle);

 

Something I've noticed.

 

When I grab the file dynamically and open it with Notepad++ without touching it with calc I get:

 

"Item Number","Available Inventory","Retail Price","Each Price"
"03657","451","12.95","2.77"
"101","778","23.95","3.28"
"10212","42","144.00","48.00"
"103","783","26.95","3.65"
"10312","19","180.00","63.16"
"105","1182","23.95","2.90"
"107","171","25.95","3.70"
"118","2878","25.95","3.69"
"119","166","24.95","3.48"
"12512","25","240.00","84.20"
"12612","17","216.00","75.80"
"127","0","24.95","3.24"
"128","8614","23.95","3.35"
"129","5366","23.95","3.28"
"130","844","30.95","5.48"
"131","40","19.95","2.90"
"132","1985","23.95","3.27"

 

If I open it with calc and save it as a csv first and then open it with Notepad++ I get:

 

Item Number,Available Inventory,Retail Price,Each Price
3657,451,12.95,2.77
101,778,23.95,3.28
10212,42,144,48
103,783,26.95,3.65
10312,19,180,63.16
105,1182,23.95,2.9
107,171,25.95,3.7
118,2878,25.95,3.69
119,166,24.95,3.48
12512,25,240,84.2
12612,17,216,75.8
127,0,24.95,3.24
128,8614,23.95,3.35

 

Could it be as simple as stripping the quotes from around the values before processing the values.

 

I'm thinking I could add something to these lines:

 

$supplier_reference = $data[($c-1)];

and

$quantity = $data[($c-1)];

 

to strip the quotes.

 

Any thoughts on how I could do that?

 

Thanks,

 

Ron

[salathe]

Having the quotes in the file is fine, that should not be anything to worry about.

 

It might help if we can see the original file, without being manually saved or altered.  Could you run the following on the CSV file as-downloaded (without being opened in Notepad++, calc or anything; you could use the URL of the file if you like) and let us know what it outputs?

 

echo base64_encode(file_get_contents('GeneratedList.csv'));

[rseigel]

Here's a link to the file:

 

http://a1webshopping.com/GeneratedList.csv

[Jessica]

Yeah I hid it because it takes up 4/5ths of the entire page. That's annoying. Salathe can still see it, I have no idea what he wants to do with it. 

[salathe]

When I grab the file dynamically and open it with Notepad++ without touching it with calc I get <snip>

 

If I open it with calc and save it as a csv first and then open it with Notepad++ I get <snip>

 

Could it be as simple as stripping the quotes from around the values before processing the values.

 

It looks like it is something even simpler than stripping the quotes from around the values.  But first a brief interlude. 

 

Your file (when originally downloaded from the other website) has line endings marked by the Carriage Return character (CR).  This is perfectly acceptable, but used far less commonly than its two more popular siblings Carriage Return + Line Feed (CRLF) and Line Feed (LF).  

 

Opening the file in Calc and saving it, changes the line endings from CR to whatever the OS you're running on uses (Calc used LF on my Mac, and CRLF on Windows). PHP likes files that use line endings that it expects (i.e, LF on *nix, CRLF on Windows). Removing the quotes also happened, but has no bearing on why you were seeing issues.

 

Now for what to do. Turn the following lines:

 

$handle = fopen("GeneratedList.csv", "r+");
ini_set('auto_detect_line_endings', true);

 

into these lines:

 

ini_set('auto_detect_line_endings', true);
$handle = fopen("GeneratedList.csv", "r+");

 

See what I did there? Setting that INI option below the fopen() call is too late for it to become used when reading from the file handle.

 

P.S. I should have paid attention earlier to where you were setting the INI option.

[rseigel]

We have a winner!!!!!

 

Thanks a million salathe. That works perfectly.

 

My inventory updating (for this supplier at least) is finished.

 

Now I can just pop it in a cron and life is good.

 

Cheers,

 

Ron