Please Note This forum exists for community support for the Mango product family and the Radix IoT Platform. Although Radix IoT employees participate in this forum from time to time, there is no guarantee of a response to anything posted here, nor can Radix IoT, LLC guarantee the accuracy of any information expressed or conveyed. Specific project questions from customers with active support contracts are asked to send requests to support@radixiot.com.

Radix IoT Website Mango 3 Documentation Website Mango 4 Documentation Website

  • 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.


  • @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.


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