Link to home
Start Free TrialLog in
Avatar of michaeldphillips
michaeldphillipsFlag for United States of America

asked on

VBScript that searches several text files for specific data and then write that data to a spreadsheet

We have just finished our pilot of 400 workstations migrating to XP SP2. USMT was the tool used for data migration. We have several progress log files that we want to extract information from, then put that info into a spreadsheet with defined column headers. Here's what i thinking of:
1- browse to or define the folder where the text files reside
2-write the filename of each file in that folder to a spreadsheet (column A)
3-read each file for a line that contains specific data/value
4-write the contents of that entire line into the spreadsheet's row that contains the corresponding filename from (column A).

Ex: If the file contains "end,scanstate"; then write that entire line into the spreadsheet with the data/values (separated by a comma) in the appropriate columns. Same applies for the same file: if contains "end,loadstate."

2006/04/12 20:40:30,XXSOMENMWSJ020L,End,SCANSTATE,0,45868,00:04:31
2006/04/12 22:24:24,XXSOMENMWSJ020L,End,LOADSTATE,0,155006,00:00:48

Essentially, that's the information i want out of the several lines one (1) file will contain. It is possible that a file could contain more than one instance of the "End, Scanstate" or End Loadstate."

As mentioned i have approx 472 files from our pilot. Upon completion there will be several thousand log files that i would need to extrrapolate this info.
 
ASKER CERTIFIED SOLUTION
Avatar of Robberbaron (robr)
Robberbaron (robr)
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
'test files
---File1.log---
2006/04/12 20:40:30,XXSOMENMWSJ020L,End,SCANSTATE,0,45868,00:04:31
2006/04/12 22:24:24,XXSOMENMWSJ020L,End,LOADSTATE,0,155006,00:00:48
---File2.log----
2006/04/12 20:40:30,XXSOMENMWSJ020L,End,SCANSTATE,0,25868,00:04:31
2006/04/12 22:24:24,XXSOMENMWSJ020L,Start,LOADSTATE,0,135006,00:00:48
2006/04/12 22:24:24,XXSOMENMWSJ020L,End,LOADSTATE,0,135006,00:00:48

-results.csv---
Filename,Date,Code1,Endx,State,Count1,Count2,tElapsed
file1.log,2006/04/12 20:40:30,XXSOMENMWSJ020L,End,SCANSTATE,0,45868,00:04:31
file1.log,2006/04/12 22:24:24,XXSOMENMWSJ020L,End,LOADSTATE,0,155006,00:00:48
file2.log,2006/04/12 20:40:30,XXSOMENMWSJ020L,End,SCANSTATE,0,25868,00:04:31
file2.log,2006/04/12 22:24:24,XXSOMENMWSJ020L,End,LOADSTATE,0,135006,00:00:48