I have a perl script that I had someone create for me which converts csv files into a format that I can use. The perl script takes three arguments and I cannot for the life of me get KM to supply the appropriate arguments.
My KM macro will run this script for each file added to a specific folder. The perl scripts arguments are <template.csv> <datafile.csv> .
Two of the arguments are basically going to be static (template file will always be in same location and path of output file will always be same path).
I’ve tried passing $KM_VAR_File_Name as the datafile.csv Arg, but that doesn’t work. I’ve tried using the suggestions in this forum post (https://forum.keyboardmaestro.com/t/how-do-i-execute-shell-script-that-requires-arguments/3145/4) without success. I’ve tried using the script as file and as text, but I cannot get it to work. I could really use some help here if anyone know how I might be able to get this script to work.
Here’s the script:
#!/usr/bin/perl
use strict;
use Text::CSV;
my $num_args = $#ARGV + 1;
if ($num_args != 3) {
print "\nUsage: $0 <template.csv> <data.csv> <output directory>\n";
exit;
}
my $corresponding={
'*ContactName'=>'Client',
'Reference'=>'Project',
'*Description'=>'Description',
'*Quantity'=>'Hours',
'**Date' => 'Date'
};
my $template_file = $ARGV[0];
my $data_file = $ARGV[1];
my $output_directory = $ARGV[2];
`mkdir -p $output_directory` unless -d $output_directory;
open(my $template_fh, '<:encoding(utf8)', $template_file) or die "Could not open file '$template_file' $!";
open(my $data_fh, '<:encoding(utf8)', $data_file) or die "Could not open file '$template_file' $!";
my @template;
my @data;
my $csv = Text::CSV->new ({
binary => 1,
auto_diag => 1,
sep_char => ',' # not really needed as this is the default
});
my $header = <$template_fh>;
$header =~ s/[\r\n]+//;
$csv->parse($header);
my @header_template = $csv->fields() ;
while(my $line = <$template_fh>){
# chomp($line);
$line =~ s/[\r\n]+//;
push @template, $line;
}
$header = <$data_fh>;
$header =~ s/[\r\n]+//;
$csv->parse($header);
my @header_data = $csv->fields() ;
while(my $line = <$data_fh>){
# chomp($line);
$line =~ s/[\r\n]+//;
push @data, $line;
}
close($template_fh);
close($data_fh);
my @replacement_keys;
my $invoise_number_index;
my $contact_name_index;
my $date_index;
my $description_index;
push @header_template, "**Date";
foreach my $key (keys %{$corresponding}) {
for(my $i=0;$i<$#header_template+1;$i++){
$contact_name_index = $i if($header_template[$i]) eq '*ContactName';
$invoise_number_index = $i if($header_template[$i]) eq '*InvoiceNumber';
$date_index = $i if($header_template[$i]) eq '**Date';
$description_index = $i if($header_template[$i]) eq '*Description';
for(my $j=0;$j<$#header_data+1;$j++){
if($header_template[$i] eq $key && $header_data[$j] eq $corresponding->{$key}){
push @replacement_keys, [$i,$j];
next;
}
}
}
}
$csv->parse( $template[0].",0");
my @template_row = $csv->fields();
my $client_name = undef;
sub replace{
my ($a,$b ) =@_;
foreach my $key (@replacement_keys){
$a->[$key->[0]] = $b->[$key->[1]] if defined($a->[$key->[0]]);
}
$client_name = $a->[$contact_name_index] unless defined($client_name);
$a->[$description_index] = $a->[$description_index] . '-' . $a->[$date_index];
$a->[$invoise_number_index] = 0 if defined($a->[$contact_name_index]);
delete $a->[$date_index];
}
my @output = map {
$csv->parse($_);
my @x= $csv->fields();
my @a=@template_row;
replace(\@a, \@x);
\@a;
} @data;
delete $header_template[$date_index];
@output = map { $csv->combine(@{$_}); $csv->string(); } @output;
my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime(time);
#$client_name =~s /[^A-Za-z0-9\-\.]/_/g;
my $output_file = $client_name.".".($mon+1).".$mday.".($year+1900).".csv";
open(my $output_fh,">",$output_directory."/".$output_file);
$csv->combine(@header_template); # combine columns into a string
print $output_fh $csv->string() ."\n";
print $output_fh join("\n", @output);
close($output_fh);
Thanks!
