You have to set up a scheduled report which send the data via email, then this script checks the e-mail and saves the attached CSV file. It has been working well for me.
#!/usr/bin/perl -w
#
# v1.0 February 2009-August 14th 2009.
#
# The purpose of this script is to save attachments from emails to an FTP server.
#
# The targeted attachments are plain text comma separated value (CSV) files. They
# are generated by (mango). The subject of the message must match
# a format specified in the script. The messages are removed from the POP3 server.
#
# The script removes some columns and does some quoting and escaping on the data
# to make more robust CSV files (ie can handle a , in the data as well as ")
#
# The script reformats the dates as per the requested format.
#
# The CSV files are saved locally in localDataDir as well as FTP'd to 2 servers.
# An error email is generated if there aren't the correct number and intervals
# of data. 15 minute data is expected.
################################################################################
use strict;
use Net::FTP;
use Mail::POP3Client;
use MIME::Parser;
use Time::Local;
use MIME::Lite;
use Text::CSV_XS;
# define login credentials for POP3 server to retrieve email from
my $pop3Username = '';
my $pop3Password = "";
my $pop3Server = "";
# first FTP server to send CSV files to
my $ftpServer = "";
my $ftpPassword = "";
my $ftpUsername = "";
my $ftpDir = "Data";
# second FTP server to send files to
my $ftpServer2 = "";
my $ftpPassword2 = "";
my $ftpUsername2 = "";
my $ftpDir2 = "";
# a writeable directory in which to store temporary files
my $temp_dir = "/usr/home/foo";
# a writeable directory in which to store a copy of the data that is FTPd
my $localDataDir = "/usr/home/foo";
################################################################################
################################################################################
################################################################################
my @csvFile;
my $fh;
my @tempFiles;
my @outFiles;
my @wiers;
my $time = timelocal( localtime() );
my $error = 0;
my $error_text = "";
print "Content-type: text/html\n\n";
&log("Running Email to FTP script\n");
my $pop = new Mail::POP3Client(
USER => $pop3Username,
PASSWORD => $pop3Password,
HOST => $pop3Server
);
my $message = $pop->Message();
if ( !$pop->Alive() ) {
$error = 1;
&log("Error connecting to POP server $pop3Server\n");
&exitLog( $message . "\n" );
}
my $emails = $pop->Count();
&log("Have $emails emails to process\n");
for ( my $i = 1 ; $i <= $emails ; $i++ ) {
foreach ( my $header = $pop->Head($i) ) {
chomp($header);
$header =~ /Subject:\s+(.*)/;
my $subject = $1;
chomp($subject);
$subject =~ s/(\r|\n)//g;
if ( # $header =~ /From:(.*)scada/ &&
$header =~ /Subject: (.*) Published FTP Data/ )
{
my $wier = $1;
$wier =~ s/^\s+//g;
$wier =~ s/\/s+$//g;
chomp($wier);
my $tempFileName = $temp_dir . $time . "-" . $i;
open( $fh, ">$tempFileName" )
or &errorlog("Can't open temp file $tempFileName for saving pop3 mail message body: $!\n ");
$pop->RetrieveToFile( $fh, $i );
close $fh;
push( @tempFiles, $tempFileName );
push( @wiers, "$wier" );
&log("Matched email $i subject $subject. Saved message for $wier\n");
# delete message from server since we have saved it to temp file
$pop->Delete($i);
}
else {
&log( "leaving non matching message " . $subject . "\n" );
}
}
}
$pop->Close();
# ok now we have retrieved file which is a multipart mime mess.
foreach my $tempFile (@tempFiles) {
my $wier = shift @wiers;
if ( $wier !~ /\w+/ ) {
$wier = "defaultSource";
$error = 1;
&log("ERROR: unable to parse report name from subject. Using default\n" );
}
my $mp = new MIME::Parser;
$mp->tmp_to_core(1);
$mp->output_to_core(1);
my $fh2;
open( $fh2, "$tempFile" )
or &errorLog("Couldn't open saved email message $tempFile , $!\n");
$mp->output_dir($temp_dir);
my $entity = $mp->parse($fh2);
#$entity->dump_skeleton();
foreach my $part ( $entity->parts ) {
my $head = $part->head;
my $type = $head->mime_type;
my $filename = $head->recommended_filename;
if ( $type eq "text/csv" ) {
if ( !$part->bodyhandle ) {
&errorLog("ERROR: unable to get MIME bodyhandle for CSV content-part\n");
next;
}
if ( $filename =~ /Events\.csv$/ ) {
&log("Skipping events file $filename\n");
next;
}
@csvFile = $part->bodyhandle->as_lines;
&log( "processing file \"$filename\", has "
. ( $#csvFile + 1)
. " lines.\n" );
# now have an array of arrays(which are the lines in the csv file)
# want to leave some columns out, which have the heading "annotation"
my @keepFields;
if (defined($csvFile[0])) {
# this is the first line in the csv file which is the heading.
my @fields = split( ",", $csvFile[0] );
my $fieldNo = 0;
foreach my $field (@fields) {
if ($field !~ /Annotation/) {
#this is a column we want to keep
push(@keepFields, $fieldNo);
#&log("keeping column $fieldNo $field\n");
}
$fieldNo++;
}
}
#my $csv = Text::CSV_XS->new (); # create a new object
my $csv = Text::CSV_XS->new ({allow_loose_quotes => 1, escape_char => "\\"}); # create a new object
my @newCsvFile;
my $lastMinute="";
my $thisMinute="";
my $lastInterval="";
my $lineNo = 0;
foreach my $line (@csvFile) {
$line =~ s/(\r|\n)//g; # strip new lines
my $status = $csv->parse($line); # parse a CSV string into fields
my @fields = $csv->fields(); # get the parsed fields
# files come with wrong dateformat. change it to as requested.
# comes as "yyyy/MM/dd HH:mm:ss"
# change to "yyyyMMdd HHmmss"
# just parse out / and :
if (!defined($fields[0])) {
&errorLog("undefined date field. csv parse status was $status. skipping rest of file.");
&errorLog("data: parsed $fields[0] from $line\n");
last; # skip the rest of this file.
}
my $newdate = $fields[0];
if ($lineNo > 0 && $filename !~ /UserComments\.csv$/ ) {
# on rows other than the header, make sure that this interval is 15 minutes ahead of the last one
$newdate =~ /\d+\/\d+\/\d+\s+\d+:(\d+):\d+/;
$thisMinute = $1;
my $dateOK = 0;
if ($lastMinute ne "" ) {
# not the first time through the loop.
if ($lastMinute eq "00" && $thisMinute eq "15") {
$dateOK = 1;
}
if ($lastMinute eq "15" && $thisMinute eq "30") {
$dateOK = 1;
}
if ($lastMinute eq "30" && $thisMinute eq "45") {
$dateOK = 1;
}
if ($lastMinute eq "45" && $thisMinute eq "00") {
$dateOK =1;
}
if ($dateOK != 1) {
&missingInterval($lastInterval, $newdate, $lineNo);
}
}
$lastMinute = "$thisMinute";
$lastInterval = "$newdate";
}
$newdate =~ s/\///g;
$newdate =~ s/://g;
$fields[0] = $newdate;
my @columns;
push(@columns, $fields[0]);
for (my $x = 1; $x <= $#keepFields; $x++) {
push(@columns, $fields[$keepFields[$x]]);
}
$status = $csv->combine (@columns); # combine columns into a string
my $csvLine = $csv->string (); # get the combined string
push(@newCsvFile, $csvLine . "\n");
$lineNo++;
}
if ( defined($newCsvFile[1]) ) {
my @fields = split( ",", $newCsvFile[1] );
my $firstFileDate = $fields[0];
#&log("Have $firstFileDate as date from $newCsvFile[1]\n");
# remove leading spaces just in case, then remove all numbers (the time) after a space
$firstFileDate =~ s/\"//g;
$firstFileDate =~ s/^\s+//g;
$firstFileDate =~ s/ \d+//g;
&validate(@newCsvFile);
my $outFileName;
if ( $filename =~ /UserComments\.csv$/ ) {
#this is a user comments file
$outFileName = $wier . "-UserComments-$firstFileDate.csv";
}
else {
# it isn't an events file as we skipped those, so it must be wier data
$outFileName = $wier . "-" . $firstFileDate . ".csv";
if ( $#newCsvFile != 96 ) {
&errorLog("$outFileName had $#newCsvFile rows instead of 96.\n");
}
}
$outFileName = $localDataDir . $outFileName;
my $ofh;
open( $ofh, ">" . $outFileName )
or &exitLog("Couln't open $localDataDir/$outFileName for writing: $!\n" );
print $ofh @newCsvFile;
close $ofh;
push( @outFiles, $outFileName );
&log("wrote csv file $outFileName successfully.\n");
}
else {
if ( $filename !~ /UserComments\.csv$/ ) {
&errorLog("skipping csv file with less than 2 lines ($#newCsvFile): $filename\n");
}
} # end if more than 1 lines in CSV file
} # end if type was csv
} # end for mime part
unlink($tempFile);
} # next tempfile (email message)
if (@outFiles > 0) {
if ($error == 0) {
# ok now ftp the files away.
my $ftp = Net::FTP->new( $ftpServer, Debug => 0 )
or &exitLog("Cannot connect to $ftpServer: $@");
$ftp->login( $ftpUsername, $ftpPassword )
or &exitLog( "Cannot login to FTP " . $ftp->message );
$ftp->cwd($ftpDir);
foreach my $outFile (@outFiles) {
my $remoteFileSize = $ftp->size($outFile);
if ( defined($remoteFileSize) ) {
if ( $remoteFileSize > 0 ) {
# file exists, let's assume we are replacing it and delete the old one
$ftp->delete($outFile);
&log("file exists on FTP, deleting the old $outFile\n");
}
}
if ( $ftp->put("$outFile") ) {
&log("uploaded $outFile to $ftpServer.\n");
}
}
$ftp->quit();
}
# do for mirror regardless of error.
# ok now ftp the files away.
my $ftp2 = Net::FTP->new( $ftpServer2, Debug => 0 )
or &exitLog("Cannot connect to $ftpServer2: $@");
$ftp2->login( $ftpUsername2, $ftpPassword2 )
or &exitLog( "Cannot login to FTP " . $ftp2->message );
$ftp2->cwd($ftpDir2);
foreach my $outFile (@outFiles) {
my $remoteFileSize = $ftp2->size($outFile);
if ( defined($remoteFileSize) ) {
if ( $remoteFileSize > 0 ) {
# file exists, let's assume we are replacing it and delete the old one
$ftp2->delete($outFile);
&log("file exists on FTP, deleting the old $outFile\n");
}
}
if ( $ftp2->put("$outFile") ) {
&log("uploaded $outFile to $ftpServer2.\n");
}
}
$ftp2->quit();
}
if ($error) { &exitLog("Script completed but with errors. Data NOT uploaded FTP2.\n"); }
sub log {
my $logText = shift;
my ( $sec, $min, $hour, $mday, $mon, $year, $wday, $yday, $isdst ) = localtime(time);
$year += 1900;
$mon++;
if ( $sec < 10) { $sec = "0" . $sec; }
if ( $min < 10) { $min = "0" . $min; }
if ( $hour < 10) { $hour = "0" . $hour; }
if ( $mday < 10) { $mday = "0" . $mday; }
if ( $mon < 10) { $mon = "0" . $mon; }
my $logTime = $year . "-" . $mon . "-$mday $hour:$min:$sec ";
# open(FH, ">>" . $submissionPath . "submissionLog.txt") or warn "couldn't open logfile in $submissionPath: $!\n" ;#
print $logTime . $logText ;
$error_text .= $logTime . $logText ;
# close FH;
}
sub errorLog {
my $errText = shift;
# set global error flag.
$error = 1;
&log("ERROR: " . $errText);
}
sub exitLog {
my $reason = shift;
&log($reason);
# send an email!
MIME::Lite->send(
'smtp', $pop3Server,
Timeout => 60,
AuthUser => $pop3Username,
AuthPass => $pop3Password
);
my $msg = MIME::Lite->new(
From => 'foo@bar.com',
To => 'foo@bar.com',
# Cc =>'foo@bar.com',
Subject => ' data FTP script error',
Data =>
"This email is automatically generated by the script which uploads the data to the FTP server daily. It is only meant to be sent if there was a problem. You may have to manually upload the data!\n\n\n$error_text"
);
$msg->send();
print $error_text;
exit;
}
sub missingInterval {
my $lastInterval = shift;
my $thisInterval = shift;
my $lineNo = shift;
&log("Missing or incorrect interval at line # $lineNo : $lastInterval, $thisInterval\n");
$error =1;
}