Searching csv files for particular string
 

Iain,
If it is just a text search then you can amend the code below to suit. Note
you should add checks/error traps.
Also, if your file are massive (~50MB, depending on hardware), you may have
to split the file reading into chunks.

If you require information more like column/row, post back.

Private Sub CommandButton4_Click()
MsgBox "Total matches = " & SearchFile("Some Text")
End Sub


Public Function SearchFile(TextToFind As String, _
Optional LogFile As String = "C:\LogFile.txt", _
Optional FileFilter As String = "CSV Files
(*.csv), *.csv") _
As Long

Dim FileNames As Variant
Dim FileIn As Integer
Dim FileOut As Integer
Dim i As Long
Dim FileText As String
Dim LastPosition As Long
Dim FoundCountThisFile As Long
Dim FoundCountTotal As Long

FileNames = Application.GetOpenFilename(FileFilter, , , , True)

If Not IsArray(FileNames) Then Exit Function

FileOut = FreeFile
Open LogFile For Append As #FileOut
Print #FileOut, "Started: " & Format(Now(), "dd/mmmm/yyyy h:mm")
Print #FileOut, "Searching for: " & TextToFind

FileIn = FreeFile
For i = LBound(FileNames) To UBound(FileNames)
Open FileNames(i) For Input As #FileIn
Print #FileOut, "Processing file: " & FileNames(i)

FileText = Input(LOF(FileIn), #FileIn)

LastPosition = InStr(1, FileText, TextToFind)
Do While LastPosition 0
FoundCountThisFile = FoundCountThisFile + 1
Print #FileOut, LastPosition;
LastPosition = InStr(LastPosition + 1, FileText, TextToFind)
Loop
FoundCountTotal = FoundCountTotal + FoundCountThisFile
Print #FileOut, ""
Print #FileOut, "Total found in this file: " & FoundCountThisFile
Print #FileOut, "----------------"
Close #FileIn
FoundCountThisFile = 0
Next

Print #FileOut, "Total found in all files: " & FoundCountTotal
Print #FileOut, "Finished: " & Format(Now(), "dd/mmmm/yyyy h:mm")
Print #FileOut, "------------------------------------------------"

Close #FileOut

SearchFile = FoundCountTotal

End Function

NickHK

"Iain M" wrote in message
...
I have a requirement to search a number of csv files for a particular
string.
I would prefer to be able to input this string as it will vary for each
search. I would then like to concatenate each of the successful finds
into
one csv file.

All help greatly appreciated.