Reading a flat file into Excel sheet

Discussion in 'Perl Misc' started by dn_perl@hotmail.com, Nov 20, 2003.

  1. Guest

    How can I read a flat file into an excel sheet using perl?
    If it is possible to do it, I will install both Excel and
    Activestate's perl binaries on my computer.

    My flat file has fields of fixed length separated by tabs. Each
    field ends in 'Z'.
    Sample :

    ----------------------------
    one Z washingtonZ 1788Z
    two Z adams Z 1792Z
    sixteenZ lincoln Z 1860Z
    ----------------------------
     
    , Nov 20, 2003
    #1
    1. Advertising

  2. () wrote in
    news::

    > How can I read a flat file into an excel sheet using perl?
    > If it is possible to do it, I will install both Excel and
    > Activestate's perl binaries on my computer.
    >
    > My flat file has fields of fixed length separated by tabs. Each
    > field ends in 'Z'.
    > Sample :
    >
    > ----------------------------
    > one Z washingtonZ 1788Z
    > two Z adams Z 1792Z
    > sixteenZ lincoln Z 1860Z
    > ----------------------------


    I used something like the following code to convert some Excel sheets to
    plain text. I guess you could use it to do the opposite:

    use strict;
    use Win32::OLE qw(in with);
    use Win32::OLE::Const 'Microsoft Excel';
    $Win32::OLE::Warn = 3; # die on errors...

    my $open_path = 'C:/Home/';
    my $Book = $Excel->Workbooks->Open($open_path.$file);
    $Book->Worksheets(1)->SaveAs($save_path.$Book->Worksheets(1)->Name,
    xlTextMSDOS);
    $Book->Close(0);

    HTH.

    Sinan.
    --
    A. Sinan Unur

    Remove dashes for address
    Spam bait: mailto:
     
    A. Sinan Unur, Nov 21, 2003
    #2
    1. Advertising

  3. Dan Anderson Guest

    () writes:

    > How can I read a flat file into an excel sheet using perl?
    > If it is possible to do it, I will install both Excel and
    > Activestate's perl binaries on my computer.
    >
    > My flat file has fields of fixed length separated by tabs. Each
    > field ends in 'Z'.

    <snip>

    If you read in a file line by line, i.e. like:

    use strict;
    use warnings;
    open ("FILE", "< ./file")
    or die ("Couldn't open the file");
    if ((not (-T FILE)) or (not (-r FILE))) {
    die ("The file was either not a text file or not readable");
    while ($_ = <FILE>) {
    $_ = chomp ($_);
    check_file($_);
    }

    You can split the fields on each line into an array using the split
    command:

    sub check_file {
    my $split_up = shift (@_);
    my @columns = split 'Z', $split_up;
    do_something_with_data(@columns);
    }

    Now you can use one of the modules on CPAN which allows you to
    read/write directly to an excel file. I forget exactly what it's
    called, but I'm sure if you STW you'll find it.

    -Dan
     
    Dan Anderson, Nov 21, 2003
    #3
    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. =?Utf-8?B?ZGF2ZQ==?=

    Reading Excel sheet from asp.net returns empty cell

    =?Utf-8?B?ZGF2ZQ==?=, Feb 12, 2006, in forum: ASP .Net
    Replies:
    2
    Views:
    5,485
    venkatu2005
    Sep 5, 2009
  2. zxo102
    Replies:
    3
    Views:
    3,323
    Stuart Corrie
    Aug 9, 2005
  3. sajithkahawatta

    read a excel sheet using aspose.excel

    sajithkahawatta, Apr 19, 2007, in forum: ASP .Net
    Replies:
    3
    Views:
    604
    Steve C. Orr [MCSD, MVP, CSM, ASP Insider]
    Apr 19, 2007
  4. sagar

    Reading From an Excel Sheet

    sagar, Apr 30, 2007, in forum: Python
    Replies:
    2
    Views:
    265
    Dave Borne
    Apr 30, 2007
  5. c676228
    Replies:
    3
    Views:
    376
    Mike Brind [MVP]
    Jul 17, 2008
Loading...

Share This Page