zzspectrez has asked for the wisdom of the Perl Monks concerning the following question:

Im looking for some suggestions on some code I just wrote. I have a friend who works in the real estate business and he needed some help importing some data into a microsoft excel database. A friend or business associate sent him a rather large text file with contacts. He wasnt sure what file the program was from and didnt know how to import it. He only needed the name and address for each record. I told him to send me the file and I would see what I could do.

Looked like a good chance to see if I could write some usefull code. I have to say I was impressed how easy it was to get something working with my limited perl knowledge and in such short time (about 25 minutes). Now Im looking for suggestions on my code. Since I have no idea how to use OLE services I tried out the Spreadsheet::WriteExcel module.


Sample line of data from text file

"1909","W","22th St ","Santa Ana CA","92688-2328", "C002","Jose Z Cuerva","Jose","Z","Cuerva","","","","", "","","1909 W 22th St","Santa Ana CA","92688-2328", "","(714)555-1212","Single Fam Res","SA","974","5", "3","1.0","","","1","$137,500","F","09/09/1999", "456645","","$108,573","25","$199,999",".15A","6,352", "022-022-22","1922","Oceanside MTG","$199,999","","VAR","FHA"



My Code

#!/usr/bin/perl -w

use strict;
use Spreadsheet::WriteExcel;

my ($inp_line, $num_addr, $dir_addr, $str_addr, $city_addr, $zip_addr,
+ $junk, $name, $junk2) = '';

my $inp_file = shift @ARGV || die "\nusage: $0 inpfile\n";

open (INP, "$inp_file") or die "\nCan't open file [$inp_file]: $!\n";

my $workbook  = Spreadsheet::WriteExcel->new("$inp_file.xls");
my $worksheet = $workbook->addworksheet();

$worksheet->write(0,0,"NAME");
$worksheet->write(0,1,"ADDRESS");
$worksheet->write(0,2,"CITY");
$worksheet->write(0,3,"ZIP");

my $row = 2;
my $temp = '';

while (<INP>) {
    chomp;
    
    ($num_addr,$dir_addr,$str_addr,$city_addr,$zip_addr,$junk,$name,$j
+unk2) =
        split /\",\"/;

    $num_addr =~ /^\"(.*)$/;        
    $num_addr = $1;
    $worksheet->write($row,0,$name);
    
    if ($dir_addr){
        ($temp = $num_addr . " $dir_addr" . " $str_addr")
    }else {
        ($temp = $num_addr . " $str_addr");
    }
    $worksheet->write($row,1,$temp);
    $worksheet->write($row,2,$city_addr);
    $worksheet->write($row,3,$zip_addr);
    ++$row;
}
        
close (INP);

[download]

Thanks!!
zzspectrez


Replies are listed 'Best First'.
Re: Creating Excel Spreadsheet from text data file
by $code or die (Deacon) on Nov 06, 2000 at 16:56 UTC
    Hi,

    There was a great article on Spreadsheet::WriteExcel in this month's TPJ by the author.

    This looks like a standard CSV file, so you could have opened it up straight into excel by double-clicking the icon(if it has a csv extension). But hey, Perl is fun and learning new new things is great so why not use it!

    The Win32::OLE module is surprisingly easy to use once you have braved it once or twice. I wrote a script the other day that uses OLE to interface with an ASP dll. OLE really opens things up (if you are on a Win32 system {grin})

    BTW, there is some documentation with Excel examples in the ActiveState HTML docs.
[reply]

      I'll have to check out the article in TPJ. I understand that excel can open CSV files. You only have to tell it what the delimiters are and select which fields to import. However, this was an oppurtunity for me to write a usefull program that did something other then print "Hello World\n";, if you know what I mean! I especially find it hard to use perl for windows tasks.

      This is really one of my first attempts at using perl to parse a file and do something usefull with the data. I'm looking for suggestions on coding style and general improvements on my code.I'm still learning how to properly use regexes and not sure if Im doing things the best way.

      As far as the Win32::OLE module, I have looked at the Activestate documents and seen that faq that show how to control Excel. The documentation still leaves me not exactly sure what OLE is and how to use it properly. For example, how do you know which applications can be controlled using OLE and what methods are available through this OLE control? The documentation gives reference on how to find information to controll Microsoft office apps and Lotus notes but falls silenty on all others.

      I looked at your script. Not sure what program needs to be installed for access to the OLE, but it does not work on my system.

      Thanks for your response!

      zzspectrez

[reply]
[d/l]
        I agree with you. It's great to try your hand at things to find other ways of doing everyday tasks. Perl is perfect for this sort of thing

        Have a look at this node for information on the OLE browser. I posted this question because I had a vague idea about what OLE did but didn't know how the browser could help me. In the end, I used I couldn't have written the above code without it. For some reason the OLE browser didn't work on my PC - it turns out that I needed to make a change to the registry. Thanks Zoogie!

        My understanding(from my imagination) of OLE is that most (not all) Win32 apps provide sort of external hooks. You can create an OLE object (it becomes an application server) with one of those applications and access those hooks from your Perl script. So in theory you can do whatever the application itself does (assuming that the author provided all the hooks.)

        I have since written a Win32 script that uses OLE to "access" an Access database and pull out records etc.

        While it's all quite fun, it means that your code isn't very portable though - you can only run it on Win32 (and maybe soon Mac?). So if there is a module out there that does the job I would stick to that.

        UPDATE: I think maybe I put too many '()' in this reply. Maybe it's unreadable. Sorry.
[reply]
Re: Creating Excel Spreadsheet from text data file
by Fastolfe (Vicar) on Nov 06, 2000 at 20:07 UTC
    You know, Excel natively supports Comma-Separated-Value (CSV) text files. No transformation like this should be necessary. Excel will also do tab-delimited text files, and if you use its import features to their fullest, you can get it to read just about any kind of tabulated textual data at all, including HTML tables.

    If there's some other reason you're doing this aside from allowing Excel to access the data, the other posts should be able to help you on your way. From what your question seems to indicate, however, none of what you're doing here should be necessary.

[reply]
(jptxs) Re: Creating Excel Spreadsheet from text data file
by jptxs (Curate) on Nov 06, 2000 at 22:55 UTC

    Your code's no worse than mine (which you can read however you like : )

    The only observation I have is that you need not assign null values for your declarations, i.e. you can do my $temp; when you just want to declare the variable. In fact line 6 where you have a list of variables declared and set equal to ' ' it's even a bit confusing at first glance. However TMTOWTDI rules and what your doing does no harm at all.

    The only other thing is I've never seen someone put the ++ _brfore_ the variable. cool : ) I like it. I think I'll put it there from now on. It reads better, "increment this variable" as opposed to "this variable is to be incremented".

    "sometimes when you make a request for the head you don't
    want the big, fat body...don't you go snickering."
                                             -- Nathan Torkington UoP2K a.k.a gnat

[reply]
[d/l]
[select]
      There is a subtle difference between $a++ and ++$a when you use it in an expression (and not like he did in his code): 
      $a = 0;
print $a++;    # 0
print $a;      # 1

print ++$a;    # 2
print $a;      # 2

      [download]
[reply]
[d/l]
[select]

Back to Seekers of Perl Wisdom