How can I check for a file size and add that result in an Excel spreadsheet in Perl?

Question

Currently I monitoring a particular file with a simple shell one-liner:

filesize=$(ls -lah somefile |  awk '{print $5}')

I'm aware that Perl has some nice modules to deal with Excel files so the idea is to, let's say, run that check daily, perhaps with cron, and write the result on a spreadsheet for further statistical use.

Answer 1

You can use the -s operator to obtain the size of a file and the Spreadsheet::ParseExcel and Spreadsheet::WriteExcel modules to produce an updated spreadsheet with the information. Spreadsheet::ParseExcel::SaveParser lets you easily combine the two, in case you want to update an existing file with new information. If you are on Windows, you may want to automate Excel itself instead, probably with the aid of Win32::OLE.

Answer 2

The module you should be using is Spreadsheet::WriteExcel.

Answer 3

You can check the size of the file using the -s operator.

use strict;
use warnings;

use File::Slurp qw(read_file write_file);
use Spreadsheet::ParseExcel;
use Spreadsheet::ParseExcel::SaveParser;
use Spreadsheet::WriteExcel;

my $file       = 'path_to_file';
my $size_file  = 'path_to_file_keeping_the_size';
my $excel_file = 'path_to_excel_file.xls';

my $current_size = -s $file;
my $old_size = 0;
if (-e $size_file) {
   $old_size = read_file($size_file);
}

if ($old_size new;
        my $excel = $parser->Parse($excel_file);
        my $row = 1;
        $row++ while $excel->{Worksheet}[0]->{Cells}[$row][0];
        $excel->AddCell(0, $row, 0, scalar(localtime));
        $excel->AddCell(0, $row, 1, $current_size);

        my $workbook = $excel->SaveAs($excel_file);
        $workbook->close;

    } else {
        my $workbook  = Spreadsheet::WriteExcel->new($excel_file);
        my $worksheet = $workbook->add_worksheet();
        $worksheet->write(0, 0, 'Date');
        $worksheet->write(0, 1, 'Size');

        $worksheet->write(1, 0, scalar(localtime));
        $worksheet->write(1, 1, $current_size);
        $workbook->close;
    }
}

write_file($size_file, $current_size);

A simple way to write Excel files would be using Spreadsheet::Write. but if you need to update an existing Excel file you should look into Spreadsheet::ParseExcel.

Answer 4

You can also skip the hassle of writing .xls format files and use a more generic (but sufficiently Excel-friendly) format such as CSV:

#!/bin/bash
date=`date +%Y/%m/%d:%H:%M:%S`
size=$(ls -lah somefile |  awk '{print $5}')
echo "$date,$size"

Then, in your crontab:

0 0 * * * /path/to/script.sh >/data/sizelog.csv

Then you import that .csv file into Excel just like any other spreadsheet.

Answer 5

Perl also has the very nice (and very fast) Text::CSV_XS which allows you to easily make Excel-friendly CSV files, which may be a better solution than creating proper XLS files.

For example (over-commented for instructional value):

#!/usr/bin/perl
package main;
use strict; use warnings; # always!

use Text::CSV_XS;
use IO::File;

# set up the CSV file
my $csv = Text::CSV_XS->new( {eol=>"\r\n"} );
my $io  = IO::File->new( 'report.csv', '>')
  or die "Cannot create report.csv: $!\n";

# for each file specified on command line
for my $file (@ARGV) {
 unless ( -f $file ) {
  # file doesn't exist
  warn "$file doesn't exist, skipping\n";
  next;
 }

 # get its size
 my $size = -s $file;

 # write the filename and size to a row in CSV
 $csv->print( $io, [ $file, $size ] );
}

$io->close; # make sure CSV file is flushed and closed