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

O Monks-

I'm writing a program that searches pushes a set of text files into an array, then searches through each file of the array for keyword matches. The way I have the program written now, I pull the text match and the text around it and push it to an outfile, then move on to the next file. Now, instead of pushing the text match to an output file, I only want to create one excel spreadsheet with two columns: one with the file name and one that can take on values of Y or N, depending on whether or not the file contains the keyword match.

Here's the code:

foreach $list (@list) {
    if ($list =~ m/\/if\/home\/m1hrb00\/covenants\/Contracts\/(.*)\.tx
+t/) { #select the index of contract names, trim off the filepath
    push (@contract_list, $1);
    print STDERR "$1 \n";
}
}    

$beforecount = 10;                                                    
+      #set how many lines before the match you'd like to pull;
$aftercount = 10;                                                     
+      #set how many lines after the match you'd like to pull;

foreach (@contract_list) {                                            
+      #pull in the array of contracts

  open Contract1, "<./$_.txt \n";
  @lines = <Contract1>;
  close Contract1;
  my $contents = join "", @lines;
  
  @all = undef;
  shift @all;

#loop for Governance                                                  
+                                                                     
+                                                       
  $infile = "$_";
  my $incontract = "$infile".".txt";
  print STDERR "$incontract read \n";  
  while ($contents =~ m/Governance/i) { 
  
    if ($contents =~ m/((\n.*){$beforecount}Governance(.*\n){$aftercou
+nt})/i) {
        
        push (@all, "$1\n");
        print STDERR "$1\n";
       $contents =~ s/Governance//i;
        
       $contract = "$_";
       my $outfile = "$contract".".txt";
        open (Contract1a, ">", "/if/home/m1hrb00/covenants/CorporateGo
+vernance/Governance/$outfile")
            || die("Cannot open output file $outfile: $!");
        print Contract1a join("\n", (@all));
        print STDERR "$outfile matched \n";
        close Contract1a;

[download]

I want to add at the end another line to do what I mentioned above. Thanks!

Replies are listed 'Best First'.
Re: Writing to an Excel file
by biohisham (Priest) on Oct 20, 2009 at 19:32 UTC
    This can require you to invest time in studying some of the Perl modules out there that can allow you to effectively have control over spreadsheets down to each cell level. I can think of: Each one of these modules has its areas of strengths.

    Excellence is an Endeavor of Persistence. Chance Favors a Prepared Mind.
[reply]
[d/l]
      AFAICT i.e. from one or two clues in the snippet, this is an exercise based solely on Windoze i.e. an application to write an Excel file, running on Windoze.

      Ergo, for me, the way to go would be to use (pun intended) Win32::OLE - if for no other reason than I've been there, done that & got the T shirt, successfully I might add, several times previously.

      One of the most useful aids you could procure, because sooner or later it'll become invaluable in such exercises, would be an object browser - one of the simplest being the one built in to the Excel application itself.

      A user level that continues to overstate my experience :-))
[reply]
[reply]
      I don't know how many times you need to do this, how often and whether you need a 100% automated process or not. I really like biohisham's suggestions but its not "cheating" to use some of the features of Excel to help you.

      I remember one project a long time ago where I had to produce a report in WinWord format once per week. I used Perl as sort of "glue" code to query a weird legacy database on a mainframe and do a bunch of reformatting, sumarizing, translating terms, etc. The output was a text file.

      I wrote a Word macro to womp on this Perl generated text file.
      So for the process: I ran my Perl program, then opened the resulting file in Word and hit like CTRL-R to make the fancy report. Now of course I designed the text file output to make this Word macro easy to write! But this whole process even with a couple of manual steps took just a minute or two. I never refined the process past that because there was no need...couple of minutes per week was no big deal - it took a lot longer than that to print it and Xerox copies for the management meeting!

      You appear to have a simple spreadsheet with 2 columns that would be easily adapatable to this kind of approach. Perl generates a CSV file. Excel macro, imports this file, sizes columns, sets fonts(heading and data), etc. Maybe even this report should be a Word doc with 2 or 3 of these (term, Y/N) paired columns per page?(Word macro can do that).

      I don't know the whole picture of what are doing. For all I know just opening this Perl generated CSV file in Excel and then save as "some Excel format" is enough. I use this technique often when doing "one off" reports to send to folks who do wonders with Excel but have never seen any real program code in their lives.

      Anyway, when thinking about how fancy to get with say Win32::OLE, you may just need ability to open the file and run a template/macro. Then font type stuff, column width is in this Excel/Word template, not in your Perl code.

      This won't solve world hunger, it is just an idea...

[reply]
        I don't think using Excel or Word macros would be a good idea in this case. It would introduce another component into the process -- an additional possible source of error and an additional part that you'd have to maintain.

        The point about the compromise between the time to develop a fully automated process and the time spent using a not fully automated solution is a valid one - but in this case, a fully automated, simple solution is well within reach.

        I'd say Spreadsheet::WriteExcel is the way to go and not Win32::OLE. The latter requires a working installation of Excel and for every manipulation with an Excel object it has to interact with a running instance of Excel - therefore it's slow and wastes resources.

        On the other hand, S::WE creates the Excel table on the fly, independent from Excel. In other words, it does just what the OP needs.
[reply]
Re: Writing to an Excel file
by VinsWorldcom (Prior) on Oct 21, 2009 at 14:21 UTC

    I understand you're on Windows an thus the Win32::OLE recommendations above are dead-on. I've done this too with Perl (for Excel specifically) and it does exactly what I wanted.

    However, if you were on Unix, you could just use 'grep'. grep -A and -B will give you lines before and after your match and grep -l/-L will print filenames if they do or don't contain your match, respectively. Why reinvent the wheel?

    For Windows, have a look at:

    http://downloads.sourceforge.net/project/unxutils/unxutils/current/UnxUtils.zip

    This contains grep and all the Unix text-util favorites for Windows boxes. I find it INVALUABLE!!!

[reply]

Back to Seekers of Perl Wisdom