Rename a csv tab from UNIX bash script

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.

Answer

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

Leave a Reply

Your email address will not be published. Required fields are marked *