Solved

Populating the Excel sheet with Data

Posted on 2006-11-20
3
312 Views
Last Modified: 2010-08-05
The script below needs major modification to perform the following tasks. This script looks into files for a particular work or number and populates the excel sheet wiht the number of times it occurs.
There is an excel sheet with a list of folder names in the column starting A2
There is a folder that contains many sub folders, these folder include 100 to 130 files with exension *.ipd.
These ipd extension files containes data with for each folder name mentioned in the excel shert for example
Address is a folder name in excel sheet
Now ipd file containes
Folder           Number of records      Size
Address              52                        1311KB
 
Now the real deal here is that the script should be able to work two ways here, One it should go to the excel sheet and see the folders there and check the ipd file for that particular folder in the IPD file and list the number of records and size in the column B and C  then move on the next file and do the same. It should populate the data for all the folders in the Excel sheet.
Note IPD files have many folders the matching folder data should go in the Excel Eg:
In excel it be somthing like this
                 File 1                                     File 2                                     File 3                                      ........................ so uptil 100 files or more
Folder       Number of Records    Size        Number of Records    Size       Number of Records   Size
Address       53                        100k               93                      5200k       Data does not exist
name          20                         522k              85                       696k          95                      78k
person
....... so on

I dont how hard would that be and how to modify this code to accomodate this all. Can you please help

Thanks
Musaab



if( $version ) {
      print "Search Filter -- Version $VERSION\n";
      print "Copyright September 2006, Research In Motion\n";
      print "Author:  Musaab Choudhry mchoudhry\@rim.net\n";
      exit(1);
}

# Output help info
outputUsage() if $help;

use File::Find;

my $search_pattern=$ARGV[0];
my $directory = $ARGV[1];
my $file_pattern  =$ARGV[2];
my $file_name =$ARGV[3];
my $counter=0;
s/\\/\\\\/g, s/\./\\./g, s/\*/.*/g, s/\?/./g, $_=qr/(^|\\)$_$/ for $file_pattern;

open(LOGFILE,">>$file_name.xls");
find(\&d, $directory);

find(\&d, cwd);

sub d {

  my $file = $File::Find::name;

  $file =~ s,/,\\,g;

  return unless -f $file;
  return  unless $file =~ /$file_pattern/;

  open F, $file or print "couldn't open $file\n" && return;


$counter=0; #none yet in this file

while (<F>) { #get each line of the file
if (my ($found) = m/($search_pattern)/o) { $counter++} #if in this line, inc counter
}
print LOGFILE "In the file $file, we found $counter lines containing $search_pattern\n";
print "In the file $file, we found $counter lines containing $search_pattern\n";


  close F;
}
0
Comment
Question by:Musaab1
  • 2
3 Comments
 
LVL 84

Accepted Solution

by:
ozo earned 500 total points
ID: 17982140
Are you saying you want to read a file that looks like

Folder           Number of records      Size
Address              52                        1311KB

to produce a file that looks like

                 File 1                                     File 2                                     File 3                                      ........................ so uptil 100 files or more
Folder       Number of Records    Size        Number of Records    Size       Number of Records   Size
Address       53                        100k               93                      5200k       Data does not exist
name          20                         522k              85                       696k          95                      78k
person

or are you saying that you want to read a file that looks like

                 File 1                                     File 2                                     File 3                                      ........................ so uptil 100 files or more
Folder       Number of Records    Size        Number of Records    Size       Number of Records   Size
Address       53                        100k               93                      5200k       Data does not exist
name          20                         522k              85                       696k          95                      78k
person

to produce a file that looks like

Folder           Number of records      Size
Address              52                        1311KB

and if so, what is the relationship between the input and the output?
0
 

Author Comment

by:Musaab1
ID: 17982468
The top one is correct but it has to pull data from *.ipd files and populate them in this fashion for file1 and then file 2 and file three and the most important thing is the "Address" data should go in the rows infront of "Address" written in excel and "Names" data show go infront of "Names" and so on.

The realtionship is that Excel has a list

Address            
Name
Person
Location
.....
...
..
.
(Now on the right sides its all empty, this needs to be populated form the *.ipd files but in way that script sees the values for Eg "Name" in the IPD file and puts them infront of the Name and PErson data infrom person and sp on and then check the next file puts its dat in the next column and then next file)

I hope this explain what I mean over here  
0
 

Author Comment

by:Musaab1
ID: 18045772
Closing this question
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

On Microsoft Windows, if  when you click or type the name of a .pl file, you get an error "is not recognized as an internal or external command, operable program or batch file", then this means you do not have the .pl file extension associated with …
Many time we need to work with multiple files all together. If its windows system then we can use some GUI based editor to accomplish our task. But what if you are on putty or have only CLI(Command Line Interface) as an option to  edit your files. I…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…

789 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question