Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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.. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Import text files into EXCEL | Excel Discussion (Misc queries) | |||
import a many text files to excel programmatically | Excel Discussion (Misc queries) | |||
Import External Text Data and Refreshing Problem | Excel Worksheet Functions | |||
Search text of external files and import | Excel Programming | |||
How can you import external text file paragraph a into single cell | Excel Discussion (Misc queries) |