The Spreadsheet::ParseExcel::Simple next_row() method subclasses the Spreadsheet::ParseExcel Cell->Value() method to retrieve data from an Excel row.

If a cell is blank then Spreadsheet::ParseExcel::Cell::Value returns an undef which Spreadsheet::ParseExcel::Simple::next_row turns into a empty string for convenience.

So far so good. Where I think the problem arises is between what you perceive as a blank cell and what Excel perceives as a blank cell.

Excel differentiates between an empty cell and a blank cell. An empty cell is a cell which doesn't contain data whilst a blank cell is a cell which doesn't contain data but does contain formatting. Excel stores blank cells but ignores empty cells.

To add to the confusion a cell that was formatted at some stage but now has its formatting removed may still have some internal flags set that aren't visible to the user. This would result in blank cell instead of an empty cell. You can remove these using Edit->Clear->All in Excel.

As such all of your s/// and chomp code is to no avail. Probably the best thing to do is just remove any empty strings from the end of the array returned by next_row():

    @hsbcdataread = $sheets[1]->next_row(); 
    pop @hsbcdataread while @hsbcdataread and $hsbcdataread[-1] eq "";
[download]

--
John.