Search text of external files and import
 

I have about 80 text files (.txt) that contain results of PC vulnerability
scans. Each file represents a different machine, and each file contains
multiple "Passed" and/or "Failed" results depending on what vulnerabilities
are present. All files are in a common networked folder - separate from the
Excel spreadsheet.
I wish to query each file for any instances of "=Failed" and output the
preceeding 12 alphanumeric characters into a cell. "=Passed" can be ignored.

For instance, if "testcase1.txt" contains the following data:
2005_292_004=Failed
2005_292_005=Failed
2005_315_001=Passed

Then the reference cell should return "2005_292_004, 2005_292_005"; if all
were "Passed" then the cell can read "none".

So if multiple files were queried, the xls may look like below (the '|'
indicates separate columns:
testcase1.txt | 2005_292_004, 2005_292_005
testcase2.txt | None
testcase3.txt | 2005_292_004
testcase4.txt | 2005_315_005
testcase5.txt | None


Thank you for any help.

Search text of external files and import
 

Sorry I can't help you then..

You will be able to do it, but it's going to take you a fair amount of
coding..

Think of excel and access as different tools.. Access is used for data
gathering and maniuplation. Excel is use for analysis and reporting.

if I can use a tool analogy, you can drive a screw into a wall with a
hammer, but it's not the right tool.. In the end it will work, but it's not
going to be a pleasant experience...



"flaterp" wrote:

I am not familiar with Access and other data is within the existing
spreadsheet, so I would like to use it.


"TomHinkle" wrote:

if you use access, and link the txt files, should take you about 5 minutes to
write the queries..

Search text of external files and import
 

Sub ReadStraightTextFile()
Dim sStr as String
Dim LineofText As String
Dim fName as String
dim i as Long
fname = dir("C:\Myfiles\*.txt)
i = 0
Redim list(1 to 1)
do while fname<""
i = i + 1
if i < 1 then
Redim Preserve list(1 to i)
end if
List(i) = fname
fName = dir()
Loop
Open "C:\Results.txt" For Output As #2

for i = 1 to ubound(list)
Open "C:\MyFiles\" & List(i) For Input As #1
sStr = ""
Do While Not EOF(1)
Line Input #1, LineofText
if(instr(1,LineofText,"=Failed",vbTextCompare) then
sStr = sStr & Left(LineofText,12) & ", "
End if
Loop
if len(str) = "" then
Print #2, List(i) & " | None"
else
Print #2, List(i) & " | " & Left(sStr,len(sStr)-2)
end if
'Close the file
Close #1
Next
Close #2
End Sub

Code is untested and may contain typos, but should get you started.

--
Regards,
Tom Ogilvy


"flaterp" wrote in message
...
I have about 80 text files (.txt) that contain results of PC vulnerability
scans. Each file represents a different machine, and each file contains
multiple "Passed" and/or "Failed" results depending on what
vulnerabilities
are present. All files are in a common networked folder - separate from
the
Excel spreadsheet.
I wish to query each file for any instances of "=Failed" and output the
preceeding 12 alphanumeric characters into a cell. "=Passed" can be
ignored.

For instance, if "testcase1.txt" contains the following data:
2005_292_004=Failed
2005_292_005=Failed
2005_315_001=Passed

Then the reference cell should return "2005_292_004, 2005_292_005"; if all
were "Passed" then the cell can read "none".

So if multiple files were queried, the xls may look like below (the '|'
indicates separate columns:
testcase1.txt | 2005_292_004, 2005_292_005
testcase2.txt | None
testcase3.txt | 2005_292_004
testcase4.txt | 2005_315_005
testcase5.txt | None


Thank you for any help.

Search text of external files and import
 

Thank you very much, Tom. I made some adjustments to accomodate the desired
output, and the code works well.


"Tom Ogilvy" wrote:

Sub ReadStraightTextFile()
Dim sStr as String
Dim LineofText As String
Dim fName as String
dim i as Long
fname = dir("C:\Myfiles\*.txt)
i = 0
Redim list(1 to 1)
do while fname<""
i = i + 1
if i < 1 then
Redim Preserve list(1 to i)
end if
List(i) = fname
fName = dir()
Loop
Open "C:\Results.txt" For Output As #2

for i = 1 to ubound(list)
Open "C:\MyFiles\" & List(i) For Input As #1
sStr = ""
Do While Not EOF(1)
Line Input #1, LineofText
if(instr(1,LineofText,"=Failed",vbTextCompare) then
sStr = sStr & Left(LineofText,12) & ", "
End if
Loop
if len(str) = "" then
Print #2, List(i) & " | None"
else
Print #2, List(i) & " | " & Left(sStr,len(sStr)-2)
end if
'Close the file
Close #1
Next
Close #2
End Sub

Code is untested and may contain typos, but should get you started.

--
Regards,
Tom Ogilvy


"flaterp" wrote in message
...
I have about 80 text files (.txt) that contain results of PC vulnerability
scans. Each file represents a different machine, and each file contains
multiple "Passed" and/or "Failed" results depending on what
vulnerabilities
are present. All files are in a common networked folder - separate from
the
Excel spreadsheet.
I wish to query each file for any instances of "=Failed" and output the
preceeding 12 alphanumeric characters into a cell. "=Passed" can be
ignored.

For instance, if "testcase1.txt" contains the following data:
2005_292_004=Failed
2005_292_005=Failed
2005_315_001=Passed

Then the reference cell should return "2005_292_004, 2005_292_005"; if all
were "Passed" then the cell can read "none".

So if multiple files were queried, the xls may look like below (the '|'
indicates separate columns:
testcase1.txt | 2005_292_004, 2005_292_005
testcase2.txt | None
testcase3.txt | 2005_292_004
testcase4.txt | 2005_315_005
testcase5.txt | None


Thank you for any help.