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

  2. Sana

    Grant Guest

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

    Grant.
     
    Grant, Aug 25, 2008
    #2
    1. Advertisements

  3. Obviously, that darned stingray hasn't done its job....
    There is, of course, Text::CSV module on CPAN...
    http://search.cpan.org/~makamaka/Text-CSV-1.08/lib/Text/CSV.pm
     
    Mladen Gogala, Aug 25, 2008
    #3
  4. 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--
     
    Sherm Pendley, Aug 25, 2008
    #4
  5. 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. Or more simply:

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

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

    John
     
    John W. Krahn, Aug 26, 2008
    #6
  7. 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.
    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

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

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

    :)

    Ben
     
    Ben Morrow, Aug 26, 2008
    #8
  9. 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

    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

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

Ask a Question

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

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.