Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > Perl > Perl Misc > Converting the text output to excel via perl.

Reply
Thread Tools

Converting the text output to excel via perl.

 
 
Sana
Guest
Posts: n/a
 
      08-25-2008
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
 
Reply With Quote
 
 
 
 
Grant
Guest
Posts: n/a
 
      08-25-2008
On Mon, 25 Aug 2008 00:10:41 -0700 (PDT), Sana <(E-Mail Removed)> wrote:

>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.
--
http://bugsplatter.id.au/
 
Reply With Quote
 
 
 
 
Mladen Gogala
Guest
Posts: n/a
 
      08-25-2008
Grant wrote:

> 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/Tex...ib/Text/CSV.pm

--
http://mgogala.freehostia.com
 
Reply With Quote
 
Sherm Pendley
Guest
Posts: n/a
 
      08-25-2008
Sana <(E-Mail Removed)> writes:

> 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--

--
My blog: http://shermspace.blogspot.com
Cocoa programming in Perl: http://camelbones.sourceforge.net
 
Reply With Quote
 
A. Sinan Unur
Guest
Posts: n/a
 
      08-25-2008
Sana <(E-Mail Removed)> wrote in news:9d2f3fe5-c804-480e-9feb-
http://www.velocityreviews.com/forums/(E-Mail Removed):

> 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 <(E-Mail Removed)>
(remove .invalid and reverse each component for email address)

comp.lang.perl.misc guidelines on the WWW:
http://www.rehabitation.com/clpmisc/
 
Reply With Quote
 
John W. Krahn
Guest
Posts: n/a
 
      08-26-2008
A. Sinan Unur wrote:
>
> 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
--
Perl isn't a toolbox, but a small machine shop where you
can special-order certain sorts of tools at low cost and
in short order. -- Larry Wall
 
Reply With Quote
 
A. Sinan Unur
Guest
Posts: n/a
 
      08-26-2008
"John W. Krahn" <(E-Mail Removed)> wrote in news:4mLsk.116668
$nD.65207@pd7urf1no:

> A. Sinan Unur wrote:
>>


....

>> 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 <(E-Mail Removed)>
(remove .invalid and reverse each component for email address)

comp.lang.perl.misc guidelines on the WWW:
http://www.rehabitation.com/clpmisc/
 
Reply With Quote
 
Ben Morrow
Guest
Posts: n/a
 
      08-26-2008

Quoth "A. Sinan Unur" <(E-Mail Removed)>:
> "John W. Krahn" <(E-Mail Removed)> wrote in news:4mLsk.116668
> $nD.65207@pd7urf1no:
>
> > 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

--
#!/bin/sh
quine="echo 'eval \$quine' >> \$0; echo quined"
eval $quine
# [(E-Mail Removed)]
 
Reply With Quote
 
A. Sinan Unur
Guest
Posts: n/a
 
      08-26-2008
Ben Morrow <(E-Mail Removed)> wrote in
news(E-Mail Removed):

>
> Quoth "A. Sinan Unur" <(E-Mail Removed)>:
>> "John W. Krahn" <(E-Mail Removed)> wrote in news:4mLsk.116668
>> $nD.65207@pd7urf1no:
>>
>> > 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 <(E-Mail Removed)>
(remove .invalid and reverse each component for email address)

comp.lang.perl.misc guidelines on the WWW:
http://www.rehabitation.com/clpmisc/
 
Reply With Quote
 
cartercc
Guest
Posts: n/a
 
      08-26-2008
On Aug 25, 3:10*am, Sana <(E-Mail Removed)> wrote:
> 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
 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Problem with Excel reports ::::Excel 2003 Migration To Excel 2007 =?Utf-8?B?c2hhc2hhbmsga3Vsa2Fybmk=?= ASP .Net 15 10-24-2007 01:34 PM
adding an excel worksheet to a generetad excel file via perl FlorianKraft@gmx.de Perl Misc 1 04-17-2007 12:53 PM
rotate text for Excel output from JSP judiphuongtu@yahoo.com Java 2 03-15-2006 05:15 PM
converting vertical text to horizontal text Mike Green Perl Misc 9 02-02-2005 08:56 AM
Content from a memo field: converting the rich text into plain text Alfredo Agosti ASP General 3 09-19-2003 05:01 PM



Advertisments