Reading a flat file into Excel sheet

[dn_perl]

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 :

 

[A. Sinan Unur]

(e-mail address removed) ([email protected]) wrote in

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.

 

[Dan Anderson]

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