Parsing Real-Time Salesforce.com Reports via Perl/CGI & HTML Tables

I recently ran into an issue where I needed to access salesforce.com reports for not only myself but others as well. If you are familiar with salesforce you know that users need a login to access the reports… If they don’t have one, well I could manually create one for them each time so they can view the report(s)… or I could come up with an alternative method.

What if it was possible to have a script go out to salesforce.com, grab the report I want via csv format, parse the csv, and convert it to an html table so any user could view the reports without having to login? That would be awesome! Well I took some time and wrote this up to do so.

After reading up on a great blog entry, Progmatic Access to Salesforce.com Reports, I felt pretty confident this could be achieved in a few steps.

  1. Find out the report ID’s for the reports I want by listing all of them via the xml document list (https://na1.salesforce.com/servlet/servlet.ReportList). The id of the report is contained within the <id> </id> tags.
  2. Authenticate to salesforce.com and hold the cookie so I could query reports (https://login.salesforce.com/?un=<username>&pw=<password>)
  3. Grab the requested report in csv format (https://na1.salesforce.com/00O30000000xxxx?export=1&enc=UTF-8&xf=csv)
    1. Red – report id
    2. Blue – exports report as csv
  4. Parse the CSV output (since we aren’t downloading the file, but rather the content), line-by-line and change each of the fields into html tables.
  5. There are an extra 7 or so lines at the end of the csv report which contain ‘generated by, report name, etc’, which I either parse out or use to display.

Not bad, 5 steps eh?

So, what we do is throw this script in our /cgi-bin/ directory and then query it with:

  • http://server.url.here/cgi-bin/grabsfdcreports.pl?id=00O30000000xxxx
    • 00O30000000xxxx is of course the report id that you grabbed from the xml reports list

You will notice it prints out a basic HTML table with real-time data from the report. Keep in mind if you have HUGE reports, this will take a long time to process, but its pretty efficient for smaller salesforce.com reports. Make sure you also check out the modules that are being used and install them.


#!/usr/bin/perl

# grabsfdcreports.pl
# authenticates to salesforce.com, grabs real time report based upon id parameter
# id's for reports can be grabbed here: https://na1.salesforce.com/servlet/servlet.ReportList

use WWW::Mechanize;
use HTTP::Cookies;
use POSIX;
use LWP::Simple;
use Text::CSV;
use CGI qw(param);

# we grab the id parameter in the url and assign to $id
my $id = param('id');

# set content type for page render
print "Content-type:text/html\r\n\r\n";

## new csv object
my $csv = Text::CSV->new();

# grab id report
$report = $id;
$username = 'sfdcusername@host.com';
$password = 'sfdcpassword';

## auth url for logging in
$authurl = "https://login.salesforce.com/?un=$username&pw=$password";

## start new www:mech, set cookies, and auth to the url
my $mech = WWW::Mechanize->new();
$mech->cookie_jar(HTTP::Cookies->new());
$mech->get($authurl);

## set the downloadurl to be the report
$downloadurl = "https://na1.salesforce.com/" . $report . "?export=1&enc=UTF-8&xf=csv\n";

## get the csv file, assign to $content
$mech->get($downloadurl);
$content = $mech->content();

## split the csv into an array, based upon newline
@arraylist = split('\n',$content);

## arraylength will hold the array length
$arraylength =$#arraylist;

## Keeping track of line count for the foreach statement
$countlines = 1;

# used to subtract the last 7 lines of the CSV which isn't needed
$cnt = (($arraylength + 1) - (7));

# this should hold the title
$title = (($arraylength + 1) - (5));
$arraylist[$title] =~ s/\"//g;

# generated by field
# parse out "'s
$generatedby = (($arraylength + 1) - (2));
$arraylist[$generatedby] =~ s/\"//g;

# setup page
print "<HTML><head>"; print "\n";
print "</head><body>"; print "\n";

## print title,generated by, parse "'s
## start going through the array
print "id value passed: $id<br>\n";
print "Report: $arraylist[$title]<br>\n";
print "$arraylist[$generatedby]<br>\n";
print "Total Records: $arraylength<br>\n";
# counter...
$counter = 0;

# print table setup
print '<table id="test1" border="1">' . "\n";

foreach my $lines (@arraylist) {
 if ($counter >= $cnt) {}
 else {
	if($csv->parse($lines))
		{
			## if the counter is at 0, we know the first line will be the table head
			if ($counter == 0) {
      	print q(<tr>);
      	print qq(<th>$_</th>) for($csv->fields());
      	print q(</tr>);
				print "\n";
	 		}
			## all other will be the table data
			else{
				print q(<tr>);
				print qq(<td>$_</td>) for($csv->fields());
				print q(</tr>);
				print "\n";
			}
  	}
 $counter++;
 }
}

## print the rest
print "</table>";
print "</body></HTML>";

4 comments

  1. I’m trying to achieve this same effect except via PHP, but it seems they’ve updated/changed their report URL servelet interface as well as the XML that’s dumped…I see no tags ANYWHERE!

    Is this stuff even documented? I’ve scraped and looked all over the place. Maybe I’m using the wrong search terms but I really thought I had good GoogleFu.. ugh.

    Anyway the main thing I want is email blast information: clicks, opens, hard bounces, soft bounces, etc but it seems all locked down or something..very aggravating.

    Thanks for the post anyway..

    • I meant to say I see no “id” tags..I actually used side carrots in my reply silly me.

      • Hey Brandon -

        I haven’t tried it in a bit, and I abandoned the project a while ago so I got no clue whats happening with it. I hope to revisit the project soon, but I can’t promise anything.

        That being said, I couldn’t find anything else except doing it this way to parse out report data. It appears they dont have that functionality within their APIs.

Leave a Reply

Scroll To Top
Descargar musica