I have CSV file that is generated from a bash script: UNIX_REPORT.sh. When you open CSV the file on the windGrr side with excel, the tab (or sheet) in the lower left corner is named UNIX_REPORT.sh.
I have an adamant customer wanting this tab/sheet to be renamed to something other than the script name. I cannot change the script at all on the UNIX side and this report is generated way too often to attempt Win side manipulation.
Perl has modules for reading CSV and writing XLSX, so you can do this on the unix side.
You'd run this like: perl csv2xlsx.pl file.csv "this is the tab name" and it would create file.xlsx that you can send to the client.
#!perl
use strict;
use warnings;
use autodie;
use Text::CSV;
use Excel::Writer::XLSX;
# input validation left as an exercise
my $f_csv = shift @ARGV;
(my $f_xlsx = $f_csv) =~ s/\.csv$/.xlsx/;
my $worksheet_name = shift @ARGV;
# read the CSV data
my $csv = Text::CSV->new({binary => 1});
open my $fh, "<:encoding(utf8)", $f_csv;
my @data;
while (my $row = $csv->getline($fh)) {
push @data, $row;
}
$csv->eof or $csv->error_diag();
close $fh;
# write the xlsx
my $workbook = Excel::Writer::XLSX->new($f_xlsx);
my $worksheet = $workbook->add_worksheet($worksheet_name);
for (my $row = 0; $row < scalar @data; $row++) {
$worksheet->write_row($row, 0, $data[$row]);
}
$workbook->close();
Poking around cpan a little more, this is a bit simpler and the generated Excel file is prettier.
#!perl
use strict;
use warnings;
use autodie;
use Text::CSV;
use Spreadsheet::GenerateXLSX qw/generate_xlsx/;
# input validation left as an exercise
my ($f_csv, $worksheet_name) = @ARGV;
(my $f_xlsx = $f_csv) =~ s/.csv$/.xlsx/;
# read the CSV data
my $csv = Text::CSV->new({binary => 1});
open my $fh, "<:encoding(utf8)", $f_csv;
my $data = $csv->getline_all($fh);
$csv->eof or $csv->error_diag();
close $fh;
# write the xlsx
generate_xlsx($f_xlsx, $worksheet_name => $data);
Far too often is not an argument against, but for manipulation. And in your circumstances this has to be on Windows, I gather. The key is though to make the manipulation automatic. You'd have to show an exemplary script output, so that I might suggest what could be done about this. In abstract terms, I'd have a Windows script written with some available tool that'd extract some sensible data making a handsome filename and then rename the file. Passing the newly named file on to Excel would be another useful trick, though possibly optional.
If the name can be unrelated to the file's content, making it up by any Windows script or app based on time, date and, if needed, some ordinal number, should be as easy as it sounds.
