0

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.

  • 1
    Rename the file before importing it into excel. The tab is simply the name of the file when it is imported from CSV. – DopeGhoti Sep 19 '18 at 18:30
  • The back end is so messed up not sure its an option... thanks though :-) – SSDdude Sep 19 '18 at 18:38
  • So, you can't change anything on the backend that generates the file, and you can't change the file once it's on the Windows machine. Then I believe this boils down to a social dilemma where you would need to convince your customer that the table name is unimportant. – Kusalananda Sep 19 '18 at 18:39
0

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);
  • Thanks all... but, Glenn you ARE THE MAN!!!! Perl saves the day! Thanks! – SSDdude Sep 21 '18 at 20:51
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.