Function to return given records from multiple files needed.

bfuchs
bfuchs used Ask the Experts™
on
Hi Experts,

I'm looking for a function that would do the following.

Loop thru all files of a given folder.
Search for files containing some wording in their name "PatMed" for example.
All files are ".txt"
and look for PatientID = 123.

Function should return the entire record
If found more than one, should return all
This should be in order of file created.
If possible, to include in each row, the date/time of file created.

So function should Receive 4 parameters (FolderName, FileName, FieldName, Criteria)
and should either create a file with results, or just return a text delimited string.

Sample of file attached.

PS. Would prefer VBA function.

Thanks
Untitled.png
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Microsoft Dynamics CRM Consultant
Commented:
Hi,

Let me give you a quick start-
Refer FileSystemObject-https://www.tutorialspoint.com/vbscript/vbscript_fso_objects.htm


Const ForReading = 1
Set objRegEx = CreateObject("VBScript.RegExp")
objRegEx.Pattern = "<your search pattern>"

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile("C:\Scripts\Test.txt", ForReading)
Set newFile = objFSO.CreateTextFile("C:\Scripts\New.txt", True)

Do Until objFile.AtEndOfStream
    strSearchString = objFile.ReadLine
    Set colMatches = objRegEx.Execute(strSearchString)
    If colMatches.Count > 0 Then
        For Each strMatch in colMatches
            '' Write strSearchString
            newFile.Write strSearchString
        Next
    End If
Loop

objFile.Close
newFile.Close

Open in new window


Now, you just need to loop through all files in the folder.
Hi,
How do I enter for field with a space?
Tried the following and nothing showing up.
Public Function ListRecords()
Const ForReading = 1
Set objRegEx = CreateObject("VBScript.RegExp")
objRegEx.Pattern = "Patient ID = 1391315"

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile("C:\Users\bfuchs\Documents\ENTOUT_613_PATMedProfileChanges_20181229061326_FULL.csv", ForReading)
Set newFile = objFSO.CreateTextFile("C:\temp\New.txt", True)

Do Until objFile.AtEndOfStream
    strSearchString = objFile.ReadLine
    Set colMatches = objRegEx.Execute(strSearchString)
    If colMatches.Count > 0 Then
        For Each strMatch In colMatches
            '' Write strSearchString
            newFile.Write strSearchString
        Next
    End If
Loop

objFile.Close
newFile.Close
End Function

Open in new window

Also tried
objRegEx.Pattern = "[Patient ID] = 1391315"

Open in new window


Thanks,
Ben
Also tried the following (added all other requirements)
Public Function ListRecords(sFolder As String, sFile As String, sCriteria As String, sNewFile As String)
    Const ForReading = 1
    Dim objRegEx As Object, objFSO As Object, objFile As Object
    Set objRegEx = CreateObject("VBScript.RegExp")
    Set objFSO = CreateObject("Scripting.FileSystemObject")

    Set newFile = objFSO.CreateTextFile(sNewFile, True)
    
    objRegEx.Pattern = sCriteria
    sDir = sFolder & "\"
    StrFile = Dir(sDir & "*" & sFile & "*")
    Do While Len(StrFile) > 0
        Set objFile = objFSO.OpenTextFile(sDir & StrFile, ForReading)
            
        Do Until objFile.AtEndOfStream
            strSearchString = objFile.ReadLine
            Set colMatches = objRegEx.Execute(strSearchString)
            If colMatches.Count > 0 Then
                For Each strMatch In colMatches
                    '' Write strSearchString
                    newFile.Write strSearchString
                Next
            End If
        Loop
        
        objFile.Close

        
        StrFile = Dir

    Loop
    
    objFile.Close
    newFile.Close
End Function

Open in new window

But not working, nothing gets created besides for empty file.

Thanks,
Ben
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Rikin ShahMicrosoft Dynamics CRM Consultant

Commented:
Hi,

Your first script looks good. You need to change your regex pattern to "/Patient ID = 1391315/gi".

Put MsgBox "Something" and try debugging and see what is missing or not working.
hi,
no error message, just nothing getting created.
I put breakpoint on the line to write to new file, but code does not get there
there is definitely records there with this criteria.
what else do you suggest for debugging?
see attached.
thanks,
ben
Untitled.png
Rikin ShahMicrosoft Dynamics CRM Consultant

Commented:
Hi,

Can you try and execute with InStr available in VBScript instead of RegEx.
Rikin ShahMicrosoft Dynamics CRM Consultant

Commented:
Your code will go like-

If InStr (strSearchString, "Patient ID = 1391315") > 0 Then
'' Write strSearchString
End If

Open in new window

instr will only get the portion 1391315regardless if its in the Patient Id field or somewhere else.
Thanks,
Ben
Patient ID = 1391315
there is no such a string,
see file attached on OP
Thanks,
Ben
Rikin ShahMicrosoft Dynamics CRM Consultant

Commented:
Not if you search whole string. It should work.
Rikin ShahMicrosoft Dynamics CRM Consultant

Commented:
Alrighty.

there is no such a string,
But you know the position, start and end point of the Patient ID? You can specify them with InStr function. Can you try with that?
But you know the position
Not always are they the same, for example if its field number 8, how can I determine where exactly to find that string (or number)?
some fields may even contain no value at all.
Thanks,
Ben
Rikin ShahMicrosoft Dynamics CRM Consultant

Commented:
Hi,

In that case, you need to programmatically find number of commas based on the header.  Find the comma position and figure out the position of commas and start the search from there.

Does that make sense?
yes, any idea how to accomplish that?
if thats too complicate, I will close this thread and open a new one for that.
Thanks,
Ben
Thanks Rikin.
Rikin ShahMicrosoft Dynamics CRM Consultant

Commented:
You're welcome!! Glad you're near to your solution.
Hi Rikin,
I've posted another thread here
https://www.experts-exchange.com/questions/29131174/Function-to-return-given-records-from-multiple-files-needed-2.html#questionAdd
leaving now, will test that tom.
Try to complete as much as possible.
Thanks,
Ben

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial