Downloading Data Points

[Victorino]

I'm trying to figure out how download a csv file of some data points with curl or wget to be presented on a different system. The customViewExample.jsp only provides four examples and I can't figure out how to retrieve historical data.

A csv file can be manually downloaded from the watch list or reports but I need to automate the download process.

Any help would be greatly appreciated.

[craig]

@Victorino said:

I'm trying to figure out how download a csv file of some data points with curl or wget to be presented on a different system. The customViewExample.jsp only provides four examples and I can't figure out how to retrieve historical data.

A csv file can be manually downloaded from the watch list or reports but I need to automate the download process.

Any help would be greatly appreciated.

I have a script which can save the .csv file attached to an e-mail. then you can set up a report to be e-mailed and then retrieve the data using POP3.

[craig]

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;
}