# converts X-plane xgs landing speed plugin text-file log to xlsx format
# appends to existing data after backup
use v5.28.0; # implicit strict/warnings + say
use IO::All;
use DateTime;
use Date::Parse;
use Data::Printer; # use_prototypes => 0;
use FindBin qw($Bin);
use Spreadsheet::Read;
use Spreadsheet::XLSX; # for above to parse XSLX files
use Encoding::FixLatin qw/fix_latin/;
use lib '/home/raj/perl-lib';
use Local::WriteExcel;
#===============================================================================
my $src_file = '/data/X-Plane 11/Output/xgs_landing.log';
die "no such file $src_file\n" unless -e $src_file;
#my $io = new IO::File; #open( $io, '<', $src_file ) || die $!;
my $src = io($src_file);
# count lines in src file:
my @lines = $src->chomp->slurp;
say 'log-file lines: ' . scalar @lines; # exit
# xlsx file - output so we can use worksheet_name in Local::WriteExcel
#-------------------------------------------------------------------------------
my $xlfile = 'xgs_landing.xlsx';
die "no such file $xlfile\n" unless -e $xlfile;
# read contents of existing spreadsheet [1] for inclusion in log data:
my $sheet = ReadData($xlfile)->[1];
# rows() not exported so call as fully qualified method:
my @rows = Spreadsheet::Read::rows($sheet); # p @rows; # AoA
# get datetime of final entry:
my $last_entry = do {
my $date = join ' ', @{ $rows[-1] }[0,1]; # say $date;
my $epoch = str2time($date); # p $epoch;
DateTime->from_epoch(epoch => $epoch);
}; # p $last_entry; exit;
# remove 1st line (headers):
shift @rows; # p @rows; exit;
#-------------------------------------------------------------------------------
{ # backup existing xlsx file:
my $id = DateTime->now->datetime;
$id =~ s/://g; # remove from time component
io($xlfile) > io( sprintf 'backups/%s.xlsx', $id );
}
# create new xlsx file:
my $xl = Local::WriteExcel->new( filename => $xlfile ); # p $xl->xl_object; exit;
# headers:
my @vars = qw( date time type reg icao fpm pitch g_force runway thr_height
td_distance lateral rating );
# required order for headers and xlsx file cols:
my @order = (0..4, 8..11, 6, 7, 5, 12); # say "@vars[@order]"; exit;
$xl->write_bold_row([ @vars[@order] ]);
# write existing data back:
$xl->write_row($_) for @rows;
#-------------------------------------------------------------------------------
# 11/17/21 12:10:06 A321 C-GTLU KBOS -0.981 m/s -193 fpm 3.7� pitch 1.496 G, Threshold 32,
# Above: 69 ft / 21 m, Distance: 1745 ft / 532 m, from CL: -2 ft / -1 m / 0.5�, Firm landing
# ([ \S]+) to avoid acquiring strange line-ending char _x000D_ introduced by fix_latin():
my $re = q!(\d{2}:\d{2}:\d{2}) (\w+) (\w\-\w+) (\w{4}) \-\d.\d+ m/s \-(\d+) !
. q!fpm (-?\d+\.\d+)� pitch (\d+\.\d+) G, Threshold (\d{2}[LCR]?)\, Above: !
. q!(\d+) ft / \d+ m, Distance: (\d+) ft / \d+ m, from CL: -?(\d+) ft / !
. q!-?\d+ m / (-?\d+\.\d+)�, ([ \S]+)!;
LOGLINE:
for ( @lines ) { # warn $_; # next;
next LOGLINE if /Not on a runway/; # probably not serious landing attempt
# replace invalid characters (degree-sign):
my $ref = _escape($_); # p $ref;
# get a datetime from American-style date:
my $dt = _parse_date($ref); # say $date_time;
# skip unless log entry more recent than xlsx file last entry:
next LOGLINE unless $dt->epoch > $last_entry->epoch; # warn 'here';
my @vals = $ref =~ m!$re!; # p @vals;
say $ref and next LOGLINE unless @vals; # p @vals; # warn $_ for @vals;
# add date to start of array so we can use @order:
unshift @vals, $dt->ymd;
$xl->write_row( [ @vals[@order] ]);
say "adding $dt data";
}
# save output:
$xl->save();
#===============================================================================
sub _escape { # substitute incompatible chars:
my $str = shift; # p fix_latin($str);
# takes mixed encoding input and produces UTF-8 output (fixes degree sign):
fix_latin($str);
}
sub _parse_date {
my $str = shift; # warn $str;
my ($date_time) = $str =~ m!(\d{2,4}/\d{2}/\d{2} \d{2}:\d{2}:\d{2})!; # p $date_time;
my $epoch = str2time($date_time); # p $epoch; ' Date::Parse
my $dt = DateTime->from_epoch(epoch => $epoch); # p $date_time;
return $dt;
}