Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > Perl > Perl Misc > Reading a flat file into Excel sheet

Reply
Thread Tools

Reading a flat file into Excel sheet

 
 
dn_perl@hotmail.com
Guest
Posts: n/a
 
      11-20-2003
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
----------------------------
 
Reply With Quote
 
 
 
 
A. Sinan Unur
Guest
Posts: n/a
 
      11-21-2003
http://www.velocityreviews.com/forums/(E-Mail Removed) ((E-Mail Removed)) wrote in
news:(E-Mail Removed) om:

> 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
(E-Mail Removed)
Remove dashes for address
Spam bait: (E-Mail Removed)
 
Reply With Quote
 
 
 
 
Dan Anderson
Guest
Posts: n/a
 
      11-21-2003
(E-Mail Removed) ((E-Mail Removed)) 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

 
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
Reading Excel sheet from asp.net returns empty cell =?Utf-8?B?ZGF2ZQ==?= ASP .Net 2 09-05-2009 04:08 AM
Reading Data From an Excel Sheet sagar Python 3 04-30-2007 04:22 PM
Reading From an Excel Sheet sagar Python 2 04-30-2007 01:39 PM
read a excel sheet using aspose.excel sajithkahawatta ASP .Net 3 04-19-2007 02:54 PM
Python-Excel: How to paste selected cells (range) to different location on the same sheet in Excel zxo102 Python 3 08-09-2005 02:41 PM



Advertisments