Converting the text output to excel via perl.

[Sana]

I have the following output which I want to convert into excel...Any
pointers how to achieve it via Spreadsheet::WriteExcel;

I am a beginer so pardon my lack of knowledge in perl.

lpar_name minmem desmem maxmem minpr despr maxpr
commuq21 2048 8192 12288 0.1 1.0 2.0
commuq20 2048 8192 12288 0.1 1.0 2.0
commuq23 2048 16384 40960 0.1 2.0 4.0
commud18 3072 40960 61440 0.2 2.0 5.0
commup03 2048 24576 30720 0.1 3.0 4.0
commup02 10240 32768 32768 1.0 4.0 8.0
commup01 10240 32768 32768 1.0 4.0 8.0

Regards
Sana

 

[Grant]

I have the following output which I want to convert into excel...Any
pointers how to achieve it via Spreadsheet::WriteExcel;

I am a beginer so pardon my lack of knowledge in perl.

lpar_name minmem desmem maxmem minpr despr maxpr
commuq21 2048 8192 12288 0.1 1.0 2.0
commuq20 2048 8192 12288 0.1 1.0 2.0
commuq23 2048 16384 40960 0.1 2.0 4.0
commud18 3072 40960 61440 0.2 2.0 5.0
commup03 2048 24576 30720 0.1 3.0 4.0
commup02 10240 32768 32768 1.0 4.0 8.0
commup01 10240 32768 32768 1.0 4.0 8.0

Crikey mate, I used to just export to a <tab> delimited file and excel
knows how to read them direct

Grant.

 

[Mladen Gogala]

Crikey mate,

Obviously, that darned stingray hasn't done its job....

I used to just export to a <tab> delimited file and excel
knows how to read them direct

There is, of course, Text::CSV module on CPAN...
http://search.cpan.org/~makamaka/Text-CSV-1.08/lib/Text/CSV.pm

 

[Sherm Pendley]

I have the following output which I want to convert into excel...Any
pointers how to achieve it via Spreadsheet::WriteExcel;

I am a beginer so pardon my lack of knowledge in perl.

lpar_name minmem desmem maxmem minpr despr maxpr
commuq21 2048 8192 12288 0.1 1.0 2.0
commuq20 2048 8192 12288 0.1 1.0 2.0
commuq23 2048 16384 40960 0.1 2.0 4.0
commud18 3072 40960 61440 0.2 2.0 5.0
commup03 2048 24576 30720 0.1 3.0 4.0
commup02 10240 32768 32768 1.0 4.0 8.0
commup01 10240 32768 32768 1.0 4.0 8.0

It looks like you're already more than 99% of the way there - just put
tabs between the columns instead of padding them with spaces, and
you're all set. Excel can import tab-delimited data with no sweat, so
why leave yourself with the responsibility of keeping up with whatever
changes MS makes to the .xls format?

sherm--

 

[A. Sinan Unur]

I have the following output which I want to convert into excel...Any
pointers how to achieve it via Spreadsheet::WriteExcel;

I am a beginer so pardon my lack of knowledge in perl.

s/perl/Perl/

perl is the binary, Perl is the language.

Do you know any Perl? Can you put something together using the
documentation for the module Spreadsheet::WriteExcel?

The documentation is extremely clear and well written. Examples abound.
Just the synopsis is enough to solve this problem if you can program in
any language.

So, please read the posting guidelines for this group, then come up with
your best effort at implementing a solution. You will be much more
likely to get good help if you are willing to do that.

To provide some incentive for you to learn how to fish, here is a
template:

#!/usr/bin/perl

use strict;
use warnings;

use Spreadsheet::WriteExcel;

my $book = # add code to create a new workbook
my $sheet = # add code to add a new sheet to $book

while ( my $line = <DATA> ) {
$line =~ s/^\s+//;
$line =~ s/\s+$//;
last unless length $line;

my $row = [ split /\s+/, $line ];
# Add code to write a new row to $sheet
# the special variable $. would be useful
# here. See perldoc perlvar.
}

# add code to close $book

__DATA__
lpar_name minmem desmem maxmem minpr despr maxpr
commuq21 2048 8192 12288 0.1 1.0 2.0
commuq20 2048 8192 12288 0.1 1.0 2.0
commuq23 2048 16384 40960 0.1 2.0 4.0
commud18 3072 40960 61440 0.2 2.0 5.0
commup03 2048 24576 30720 0.1 3.0 4.0
commup02 10240 32768 32768 1.0 4.0 8.0
commup01 10240 32768 32768 1.0 4.0 8.0



--
A. Sinan Unur <[email protected]>
(remove .invalid and reverse each component for email address)

comp.lang.perl.misc guidelines on the WWW:
http://www.rehabitation.com/clpmisc/

 

[John W. Krahn]

To provide some incentive for you to learn how to fish, here is a
template:

#!/usr/bin/perl

use strict;
use warnings;

use Spreadsheet::WriteExcel;

my $book = # add code to create a new workbook
my $sheet = # add code to add a new sheet to $book

while ( my $line = <DATA> ) {
$line =~ s/^\s+//;
$line =~ s/\s+$//;
last unless length $line;

my $row = [ split /\s+/, $line ];

Or more simply:

while ( my $line = <DATA> ) {
last unless $line =~ /\S/;

my $row = [ split ' ', $line ];

# Add code to write a new row to $sheet
# the special variable $. would be useful
# here. See perldoc perlvar.
}

John

 

[A. Sinan Unur]

....

while ( my $line = <DATA> ) {
$line =~ s/^\s+//;
$line =~ s/\s+$//;
last unless length $line;

my $row = [ split /\s+/, $line ];

Or more simply:

while ( my $line = <DATA> ) {
last unless $line =~ /\S/;

Well, my intent was to clear any leading or trailing spaces in the first
and last fields, respectively. It is 'just in case' thing because I have
run into that problem a few times in the past.

my $row = [ split ' ', $line ];

Someone up-thread had mentioned tab-separated values. My editor did not
show any tabs, just multiple spaces. I split on \s+ to avoid any
problems just in case the OP's data contained a combination of tabs and
spaces.

Sinan

--
A. Sinan Unur <[email protected]>
(remove .invalid and reverse each component for email address)

comp.lang.perl.misc guidelines on the WWW:
http://www.rehabitation.com/clpmisc/

 

[Ben Morrow]

my $row = [ split ' ', $line ];

Someone up-thread had mentioned tab-separated values. My editor did not
show any tabs, just multiple spaces. I split on \s+ to avoid any
problems just in case the OP's data contained a combination of tabs and
spaces.

I think you need to go reread perldoc -f split, particularly the
paragraph

As a special case, specifying a PATTERN of space (' ') ...



Ben

 

[A. Sinan Unur]

my $row = [ split ' ', $line ];

Someone up-thread had mentioned tab-separated values. My editor did
not show any tabs, just multiple spaces. I split on \s+ to avoid any
problems just in case the OP's data contained a combination of tabs
and spaces.

I think you need to go reread perldoc -f split, particularly the
paragraph

As a special case, specifying a PATTERN of space (' ') ...

Aaaaargh!

I forgot.

Thank you for catching that.

Sinan

--
A. Sinan Unur <[email protected]>
(remove .invalid and reverse each component for email address)

comp.lang.perl.misc guidelines on the WWW:
http://www.rehabitation.com/clpmisc/

 

[cartercc]

I have the following output  which I want to convert into excel...Any
pointers how to achieve it via Spreadsheet::WriteExcel;

My job requires me to export big globs of data to Excel and similar
formats, 'big' being defined as 100 columns across and 7,000 rows
deep. I don't use any modules but do it natively. Here's how, assuming
that your data can be gotten at using @ar:

open OUTFILE, ">data.csv";
print OUTFILE "HEAD1,HEAD2,HEAD3,HEAD4,etc\n";
while (<DATA>)
{
print OUTFILE "$ar[0],$ar[1],$ar[2],$ar[3],etc.\n";
}
close OUTFILE;

This will put 'data.csv' in your working directory with an Excel-like
icon, and when you click on it, it opens up in Excel. Nice and easy.

CC

 

[Woland99]

I have the following output which I want to convert intoexcel...Any
pointers how to achieve it via Spreadsheet::WriteExcel;

I am a beginer so pardon my lack of knowledge in perl.

lpar_name minmem desmem maxmem minpr despr maxpr
commuq21 2048 8192 12288 0.1 1.0 2.0
commuq20 2048 8192 12288 0.1 1.0 2.0
commuq23 2048 16384 40960 0.1 2.0 4.0
commud18 3072 40960 61440 0.2 2.0 5.0
commup03 2048 24576 30720 0.1 3.0 4.0
commup02 10240 32768 32768 1.0 4.0 8.0
commup01 10240 32768 32768 1.0 4.0 8.0

Regards
Sana

I use Win32::OLE - try sth like this (edited version of my
own code may not work out of the box - for better examples
check Win32::OLE docs)
------------------------------------------
#for OLE automation
use Win32::OLE;
use Win32::OLE::Const "Microsoft Excel";
use Win32::OLE 'in';
use Win32::OLE::Variant;

#get current directory
use Cwd;

use strict;
my $excel;

#--------------------------------------------------------------------------------------
#writes array of array references as new excel spreadsheet
sub write_array_as_excel_file
{
#-------------------------------------------------------------------
#1st is name of output file
my $output_file = shift;

#2nd argument is assumes as refrence to array of array references
#each array references contains values for the whole row in
spreadsheet
my $array_ref = shift;

#-------------------------------------------------------------------

my $dir = cwd();
my $fullname_output_file = $dir.'/'.$output_file;

# use existing instance if Excel is already running
eval
{
$excel = Win32::OLE->GetActiveObject('Excel.Application')
};
die "Excel not installed" if $@;

unless (defined $excel)
{
$excel = Win32::OLE->new('Excel.Application', 'Quit')
or die "Oops, cannot start Excel";
}
#to avoid excessive dialogs when saving in non-Excel format
$excel->{DisplayAlerts} = 0;

# get a new workbook
my $wbook = $excel->Workbooks->Add
|| print STDERR "didnt add new workbook: $!\n";

# write to a particular cell
my $wsheet = $wbook->Worksheets(1);
my $ref;
my $cell_value;
my $row_num = scalar @{$array_ref};
my $col_num = scalar @{$array_ref->[0]};

$wsheet->Range($wsheet->Cells(1,1), $wsheet->Cells($row_num,
$col_num) )->{Value} = $array_ref;


$wbook->SaveAs($fullname_output_file);
$wbook->Close();
}