Converting the text output to excel via perl.

Discussion in 'Perl Misc' started by Sana, Aug 25, 2008.

  1. Sana

    Sana Guest

    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
     
    Sana, Aug 25, 2008
    #1
    1. Advertising

  2. Sana

    Grant Guest

    On Mon, 25 Aug 2008 00:10:41 -0700 (PDT), Sana <> 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/
     
    Grant, Aug 25, 2008
    #2
    1. Advertising

  3. Mladen Gogala, Aug 25, 2008
    #3
  4. Sana <> 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
     
    Sherm Pendley, Aug 25, 2008
    #4
  5. Sana <> wrote in news:9d2f3fe5-c804-480e-9feb-
    :

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

    comp.lang.perl.misc guidelines on the WWW:
    http://www.rehabitation.com/clpmisc/
     
    A. Sinan Unur, Aug 25, 2008
    #5
  6. 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
     
    John W. Krahn, Aug 26, 2008
    #6
  7. "John W. Krahn" <> 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 <>
    (remove .invalid and reverse each component for email address)

    comp.lang.perl.misc guidelines on the WWW:
    http://www.rehabitation.com/clpmisc/
     
    A. Sinan Unur, Aug 26, 2008
    #7
  8. Sana

    Ben Morrow Guest

    Quoth "A. Sinan Unur" <>:
    > "John W. Krahn" <> 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
    # []
     
    Ben Morrow, Aug 26, 2008
    #8
  9. Ben Morrow <> wrote in
    news:eek::

    >
    > Quoth "A. Sinan Unur" <>:
    >> "John W. Krahn" <> 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 <>
    (remove .invalid and reverse each component for email address)

    comp.lang.perl.misc guidelines on the WWW:
    http://www.rehabitation.com/clpmisc/
     
    A. Sinan Unur, Aug 26, 2008
    #9
  10. Sana

    cartercc Guest

    On Aug 25, 3:10 am, Sana <> 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
     
    cartercc, Aug 26, 2008
    #10
  11. Sana

    Woland99 Guest

    On Aug 25, 2:10 am, Sana <> wrote:
    > 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();
    }
     
    Woland99, Aug 27, 2008
    #11
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Replies:
    2
    Views:
    610
  2. =?Utf-8?B?c2hhc2hhbmsga3Vsa2Fybmk=?=

    Problem with Excel reports ::::Excel 2003 Migration To Excel 2007

    =?Utf-8?B?c2hhc2hhbmsga3Vsa2Fybmk=?=, Oct 5, 2007, in forum: ASP .Net
    Replies:
    15
    Views:
    1,541
    =?Utf-8?B?c2hhc2hhbmsga3Vsa2Fybmk=?=
    Oct 24, 2007
  3. Chris Weiss

    How to capture output of CVS via Perl?

    Chris Weiss, Oct 8, 2004, in forum: Perl Misc
    Replies:
    7
    Views:
    193
    Tad McClellan
    Oct 9, 2004
  4. Chris Weiss

    How to capture output of CVS via Perl?

    Chris Weiss, Oct 8, 2004, in forum: Perl Misc
    Replies:
    1
    Views:
    102
    John Bokma
    Oct 8, 2004
  5. Replies:
    1
    Views:
    299
    Mirco Wahab
    Apr 17, 2007
Loading...

Share This Page