Solved

Populating the Excel sheet with Data

Posted on 2006-11-20
3
303 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
Comment Utility
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
Comment Utility
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
Comment Utility
Closing this question
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

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…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.

763 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now