PHP script to export for Facebook data feed CSV

[ludwig]

Hello,

I created a PHP script to export my catalog on a ecommerce website on a CSV file and use it to feed Facebook catalog. But Facebook catalog can't read the columns:

But if I open the csv file on Excel, do nothing, just Save it and use it again on Facebook, then Facebook accept it, and all data are transferred !!

This is the PHP script:

define('FEEDNAME', 'catalog_products.csv');
$OutFile = "feeds/" . FEEDNAME;

$output = "id,title,description,availability,condition,price,link,image_link,brand,google_product_category,fb_product_category,quantity_to_sell_on_facebook,sale_price,sale_price_effective_date,item_group_id,gender,color,size,age_group,material,pattern,shipping,shipping_weight,style";
$attributesColumns = array();

$output .= "\n";
	   
$output .= $row->id . ",";
$output .= $productTitle . ",";
$output .= 'Visit our website for more info.' . ",";
$output .= $availability . ",";
$output .= 'New' . ",";
$output .= $row->price . ",";
$output .= $row->image_url . ",";      
$output .= $row->brand . ",";    
$output .= $row->catName . ",";
$output .= $row->catName . ",";
$output .= $row->quantity . ",";
$output .= ",";
$output .= ",";
$output .= ",";
$output .= ",";
$output .= ",";
$output .= ",";
$output .= ",";
$output .= ",";
$output .= ",";
$output .= ",";
$output .= ",";
$output .= ",";

$output .= " \n";

$fp = fopen( $OutFile , "a" );
$fout = fwrite( $fp , $output );
fclose( $fp );

Maybe I should add some headers to the export file ? I'm not sure how to handle this

Any help please ?

Cheers :

[requinix]

Don't write CSV lines yourself. Use fputcsv.

[Barand]

2 hours ago, ludwig said:

Maybe I should add some headers to the export file ? I'm not sure how to handle this

Isn't that what the third line of your code is doing?

[gizmola]

The other obvious problem with your code is that it appears to be based on a database query result set, but you didn't include that in your code, nor is there a "foreach" loop over the result set building up the csv file with the results.  So while you may or may not need the csv header row, your code as presented has no hope of actually working, without the availability of the data in a $row variable that is fetched from the result set in a loop.  

Another issue you need to be aware of is that strings should always be delimited with double quotes around them.  So you need to be cognizant of which columns are strings, and also you need to make sure that any embedded double quotes are escaped.

So for example, these lines are improper:

$output .= $productTitle . ",";
$output .= 'Visit our website for more info.' . ",";

That needs to be this:

$output .= $productTitle . ",";
$output .= '"Visit our website for more info."' . ",";

These problems are already solved for you in the function requinix linked you to.

[ludwig]

@gizmola Thank you and thanks for all for the help.

I minimized my script to this:

<?php

define( 'FEEDNAME', 'catalog_products.csv' ); 

$OutFile = "feeds/" . FEEDNAME;
$destination_file = FEEDNAME;
$source_file = $OutFile;

if ( file_exists( $OutFile ) ) {
    unlink( $OutFile );
}

$output = "id,title,description,availability,condition,price,link,image_link,brand,google_product_category,fb_product_category,quantity_to_sell_on_facebook,sale_price,sale_price_effective_date,item_group_id,gender,color,size,age_group,material,pattern,shipping,shipping_weight,style";

$output .= "\n";

$output .= "Test1,Test2,Test3,Test4,Test5,Test6,Test7,Test8,Test9,Test10,Test11,Test12,Test13,Test14,Test15,Test16,Test17,Test18,Test19,Test20,Test21,Test22,Test23,Test24";

$output .= " \n";
    
$fp = fopen( $OutFile, "a" );
$fout = fwrite( $fp, $output );
fclose( $fp );

echo "<a href=\"../" . $OutFile . "\" target=\"_blank\">" . $destination_file . "</a><br>\n\n";

chmod( $OutFile, 0777 );
?>

Still getting errors when used with Facebook.. what confuse me is if I open the file with Excel "Do nothing" just save it and upload it again, the file is accepted by Facebook Feed.

Could be something related to the mode parameter "a" in fopen( $OutFile, "a" ); ?

Edited January 26, 2023 by ludwig

[ludwig]

If I change the mode paratere to r+, Facebook give me this error : 

URL does not link to supported file

Make sure the URL that you provide links to a CSV, TSV or XML (RSS/ATOM) file and begins with http, https, ftp or sftp.

[ludwig]

Also I tried this code :

define('FEEDNAME', 'catalog_products.csv');

$OutFile = "feeds/" . FEEDNAME;
$destination_file = FEEDNAME;
$source_file = $OutFile;

if ( file_exists( $OutFile ) ) {
   unlink( $OutFile );
}

$list = array (
array("id","title","description","availability","condition","price","link","image_link","brand","google_product_category","fb_product_category","quantity_to_sell_on_facebook","sale_price","sale_price_effective_date","item_group_id","gender","color","size","age_group","material","pattern","shipping","shipping_weight","style"),
    
array("Test1","Test2","Test3","Test4","Test5","Test6","Test7","Test8","Test9","Test10","Test11","Test12","Test13","Test14","Test15","Test16","Test17","Test18","Test19","Test20","Test21","Test22","Test23","Test24")
);

$file = fopen($OutFile,"w");

foreach ($list as $line) {
  fputcsv($file, $line);
}
fclose($file);

Same errors, and also if I save the file with Excel, the magic happen, Facebook like it 😕

[Barand]

Have you tried comparing your file with one you get from Excel to see if there are differences that would account for its acceptance?

[ludwig]

4 minutes ago, Barand said:

Have you tried comparing your file with one you get from Excel to see if there are differences that would account for its acceptance?

Yes, looks the same, also I opened both on notepad to compare.

I will attached both files, before and after:

https://www.watercenter.me/feeds/catalog_products_before.csv (Not working)

https://www.watercenter.me/feeds/catalog_products_after.csv (Working)

Cheers!

[Barand]

These are the differences I can see

1. Your "after" file has a comma at the end of line 1, the "before" one doesn't.
   
    
2. Your "after" file has a newline character at the end of the final line (extra blank line at end of file); "before" doesn't.
   
    
3. "After" prices not rounded to 2 dec; "before" ones are. (123.50 becomes 123.5 etc)
   

[ludwig]

30 minutes ago, Barand said:

These are the differences I can see

1. Your "after" file has a comma at the end of line 1, the "before" one doesn't.
   
    
2. Your "after" file has a newline character at the end of the final line (extra blank line at end of file); "before" doesn't.
   
    
3. "After" prices not rounded to 2 dec; "before" ones are. (123.50 becomes 123.5 etc)
   

Many thanks for this visibility, what software are you using to compare ?

[Barand]

My IDE is Nusphere PhpEd Pro. I used its "Show file differences" facility.

[ludwig]

I added a comma at the end of header line and a break line at the end of file, my code is similar to this now:

<?php

define( 'FEEDNAME', 'catalog_products.csv' ); 

$OutFile = "feeds/" . FEEDNAME;
$destination_file = FEEDNAME;
$source_file = $OutFile;

if ( file_exists( $OutFile ) ) {
    unlink( $OutFile );
}

$output = "id,title,description,availability,condition,price,link,image_link,brand,google_product_category,fb_product_category,quantity_to_sell_on_facebook,sale_price,sale_price_effective_date,item_group_id,gender,color,size,age_group,material,pattern,shipping,shipping_weight,style,";

$output .= "\n";

$output .= "Test1,Test2,Test3,Test4,Test5,Test6,Test7,Test8,Test9,Test10,Test11,Test12,Test13,Test14,Test15,Test16,Test17,Test18,Test19,Test20,Test21,Test22,Test23,Test24";
$output .= " \n";
$output .= " \n";
    
$fp = fopen( $OutFile, "a" );
$fout = fwrite( $fp, $output );
fclose( $fp );

echo "<a href=\"../" . $OutFile . "\" target=\"_blank\">" . $destination_file . "</a><br>\n\n";

chmod( $OutFile, 0777 );
?>

Thanks @Barand Facebook now initiated the process without errors