• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 308
  • Last Modified:

Get data from all text files to a excel.

Hi,

I have 100's of txt file in a folder.

Ex:

USER:rabinarayanp
DOMAIN:DEVELOPMENT
COMPUTER:BPDB1
Hardware summary for BPDB1:

Processor(s)

Name:Intel(R) Pentium(R) 4 CPU 2.40GHz
Manufacturer:GenuineIntel
Description:x86 Family 15 Model 3 Stepping 3
Current Clock Speed:2394 MHz

Model

Manufacturer:INTEL_
Model:D865GBF_
Name:BPDB1

Memory

Memory Banks:4
Memory Modules:4 (512 + 512 + 512 + 512 MB)
Total Physical Memory:2031 MB


Harddisk(s)

Manufacturer:
Model:
Size:76 GB

Video

Name:Standard VGA Graphics Adapter
Video Processor:Intel(r)865G Graphics Chip Accelerated VGA BIOS
Adapter RAM:16 MB
Video Mode Description:1024 x 768 x 4294967296 colors


I want a script to fetch these items from these text files.
Username,Computername,Processor,Ram,Hdd

egards
Sharath
0
bsharath
Asked:
bsharath
  • 2
1 Solution
 
nedfineCommented:
This is a perl script for the job. This will create an excel hw.xls in your 'my documents' .
change the foldername to required one(where the text files are present).

$req = "D:/Nithin/bin/hw";   # cahnge to the required folder name
use Win32::OLE qw(in with);
use Win32::OLE::Const 'Microsoft Excel';
$Win32::OLE::Warn = 3;                                
 #get already active Excel application or open new
  $Excel = Win32::OLE->GetActiveObject('Excel.Application')
    || Win32::OLE->new('Excel.Application', 'Quit');  

$Book = $Excel->Workbooks->Add();
$row=1;
$Sheet = $Book->Worksheets(1);
$Sheet->Range("A1:E1")->Font->{ColorIndex} =5;
$Sheet->Cells($row,1)->{'Value'} = "NAME";
$Sheet->Cells($row,2)->{'Value'} = "COMPUTER NAME";
$Sheet->Cells($row,3)->{'Value'} = "RAM";
$Sheet->Cells($row,4)->{'Value'} = "HDD";
$Sheet->Cells($row,5)->{'Value'} = "PROCESSOR";
$row++;
 opendir(DIR ,"$req") or die "cannot open $req";
  while (defined($doc = readdir(DIR)))
   {
         
        if($doc eq "\." || $doc eq "\..")
       {
                 #do nothing;
        }
        else
         {
           chdir($req);
           if ($doc =~/\./)
           {
             open SS, "$doc" or die "cannot open $req/$doc";
             while(<SS>)
             {
               if($_=~/USER:(.*)/)
               {
                $Sheet->Cells($row,1)->{'Value'} = $1;
               }
               elsif($_=~/COMPUTER:(.*)/)
               {
                $Sheet->Cells($row,2)->{'Value'} = $1;
               }
                elsif($_=~/RAM:(.*)/)
             {
              $Sheet->Cells($row,3)->{'Value'} = $1;
                }
                elsif($_=~/Size:(.*)/)
             {
              $Sheet->Cells($row,4)->{'Value'} = $1;
                }             
             }
             close SS;
             $i=1;
             open PP, "$doc" or die "cannot";
             while(<PP>)
             {
              if($i >= 8 and $i <= 10)
              {
               $string =$string."$_";
               $i++;
              }
              $i++;
             }
            $Sheet->Cells($row,5)->{'Value'} =   $string;      
            $string ="";
            close PP;
          }
              $row++;
          }
    }
$Book->SaveAs('hw.xls');
0
 
bsharathAuthor Commented:
What ext should i save this script.
Where will the script take the txt file from.Where should i change
0
 
nedfineCommented:
you must have perl installed in your machine to run this.
save this as "script.pl".
go to command prompt and give "perl  script.pl" to execute this.
change the first line.. give the folder name where the text files are present. The files will be taken from this folder.

if you do not have perl you can download it from  http://www.activestate.com/store/freedownload.aspx?prdGuid=81fbce82-6bd5-49bc-a915-08d58c2648ca  its free.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now