[ic] Froogle script

[Scott Miller]

We recently created a simple Perl script to automatically upload our client's products to Froogle 
(http://froogle.google.com). We have it scheduled to run once per day. If you use HTML in your 
product descriptions, you might need to tweak the "cleantxt" function to strip out the HTML 
characters. Otherwise, you should just need to change the variables at the beginning of the 
script to match your settings.

If you are new to Froogle, definitely check it out. Its been worth the effort and should only 
improve as more shoppers start to use it. Once you have everything up and running and Froogle has 
approved your file (which took over a week for us), you will then be able to view all of your 
products by doing a Froogle search like "store:your_store_id". This will list all of your 
products which is very useful to verify your upload is working properly. For example, 
http://froogle.google.com/froogle?q=store:mydogsbakery shows our working upload.

Hopefully, someone else finds this script useful!

-- 
Scott Miller
Twin Cities Solutions, Inc.
10800 Lyndale Ave S, Suite #191
Bloomington, MN 55420
http://www.twincs.com
smiller at twincs.com



#!/usr/bin/perl

use DBI;
use Net::FTP;

# CHANGE THESE VARIABLES 
# ======================
# Froogle FTP Information
my $ftpserver = 'hedwig.google.com';
my $ftpuser = 'your_ftp_user';
my $ftppassword = 'your_ftp_password';

# MySQL database Information
my $dbname = "your_db";
my $dbhost = "your_db_host";
my $dbuser = "your_db_user";
my $dbpassword = "your_db_password";

# The destination file for the Froogle file
# NOTE: The filename must be the same as the filename Froogle expects for your upload
my $outfile = "/your_local_directory/your_filename.txt";

# URL for your store
my $product_url_prefix = "http://www.yourdomain.com/yourstore.cgi/";

# Suffix for your product URL's. Set mv_pc to an affiliate id if you want to track froogle traffic
my $product_url_suffix = ".html?mv_pc=00015";

# URL for your product images
my $image_url_prefix = "http://www.yourdomain.com/yourstore/images/items/";

# DUMP THE PRODUCTS TO THE FROOGLE FILE 
# =====================================

# Connect to DB
my $dbh = DBI->connect("DBI:mysql:database=$dbname;host=$dbhost",
	$dbuser,$dbpassword,
	{RaiseError => 1, AutoCommit => 1});

# Get all products
my $sth = $dbh->prepare("select * from products order by sku");
$sth->execute();

# Create the new outfile
open S, ">$outfile";

# Print header with column names (tab delimited)
print S "product_url\tname\tdescription\timage_url\tcategory\tprice\n";

while (my $ref = $sth->fetchrow_hashref()) {
	print S $product_url_prefix.$ref->{'sku'}.$product_url_suffix."\t";
	print S cleantxt($ref->{'description'})."\t";
	print S cleantxt($ref->{'comment'})."\t";
	print S $image_url_prefix.$ref->{'image'}."\t";
	print S cleantxt($ref->{'prod_group'}." > ".$ref->{'category'})."\t";
	print S $ref->{'price'};
	print S "\n";
}

# Close DB connection
$sth->finish();
$dbh->disconnect();

# Close outfile
close S;

# FTP THE FILE TO FROOGLE
# =======================
my $ftp = Net::FTP->new($ftpserver,Debug=>0)
	or die "Cannot connect to $ftpserver\n$@";

$ftp->login($ftpuser,$ftppassword)
	or die "Cannot login as $ftpuser\n",$ftp->message;

$ftp->put($outfile)
	or die "Cannot transfer file $outfile\n", $ftp->message;

$ftp->quit;


# ROUTINE TO REMOVE UNWANTED CHARACTERS FROM THE TEXT
# ===================================================
sub cleantxt {
	my $txt = shift;

	# Remove any CR and LF characters
	$txt =~ s/\r\n/ /g;
	$txt =~ s/\n/ /g;

	# Remove tab characters
	$txt =~ s/\t/ /g;

	return $txt;
}