ccallahanwise has asked for the wisdom of the Perl Monks concerning the following question:

I am able to read an excel spreadsheet using perl. I save the read data to an .csv file. My question is, in the excel spreadsheet some columns have ##### where the column data is larger than the column display. Well in my .csv file I still have the #####'s. Help. Thanks. My code is as follows:
#!/usr/bin/perl
use warnings;
use strict;
use Win32::OLE qw(in with);
use Win32::OLE::Const 'Microsoft Excel';
$Win32::OLE::Warn = 3;
my $ofilename = ("> excel_works.csv");
open (OFILE, $ofilename);
my $excel = Win32::OLE->GetActiveObject ('Excel.Application') || Win32::OLE->new('Excel.Application', 'Quit');
$excel->{'Visible'} = 0;
my $workbook = $excel->Workbooks->Open("c:\\cm\\cm_perl\\excel\\test.xls");
my $worksheet = $workbook->Worksheets(1);

my $row = 1;
while ($worksheet->Cells($row, 1)->{'Text'})
{
print OFILE
(
$worksheet->Cells($row, 1)->{'Text'}.",".
$worksheet->Cells($row, 2)->{'Text'}.",".
$worksheet->Cells($row, 3)->{'Text'}.",".
$worksheet->Cells($row, 4)->{'Text'}.",".
$worksheet->Cells($row, 5)->{'Text'}.",".
$worksheet->Cells($row, 6)->{'Text'}.",".
$worksheet->Cells($row, 7)->{'Text'}.",".
$worksheet->Cells($row, 8)->{'Text'}.",".
$worksheet->Cells($row, 9)->{'Text'}."\n"
);
$row++;
}#end while

$workbook->Close;
close (OFILE);
print "end of program Excel Works.\n";
#
  • Comment on Perl reading excel spreadsheet getting ##### for few columns

Replies are listed 'Best First'.
Re: Perl reading excel spreadsheet getting ##### for few columns
by Nkuvu (Priest) on May 20, 2004 at 21:42 UTC
    Change it to Cells($whatever, $whatever)->{'Value'} and you'll get the real values instead of the displayed text.
Re: Perl reading excel spreadsheet getting ##### for few columns
by bobn (Chaplain) on May 21, 2004 at 00:42 UTC
    I never had much luck with the OLE approach. When I wanted to parse a bunch of spreadsheets, I ended up going to CPAN and found: Spreadsheet::ParseExcel::Simple and that did the trick nicely. Expecially because you can use it anywhere the spreadseet files hapen to be - doesn't have to be on Windoze.

    --Bob Niederman, http://bob-n.com

    All code given here is UNTESTED unless otherwise stated.

Re: Perl reading excel spreadsheet getting ##### for few columns
by EdwardG (Vicar) on May 20, 2004 at 21:41 UTC

    Can we assume you are using something other than Excel to view your CSV file?

     

      we are manually saving the xls as a csv file and appending an Oracle Sql Loader control card to the front for process. I'd like to remove the manual piece and replace with a perl program.
      I am really starting to enjoy the freedom perl has given me from all these manual processes.
      I truly appreciate all the help and support!!!
Re: Perl reading excel spreadsheet getting ##### for few columns
by Anonymous Monk on May 21, 2004 at 18:24 UTC
    Use 'Value' in place of 'Text' plus
    add use Win32::OLE::Variant;