Function to return given records from multiple files needed.

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
LVL 6
bfuchsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rikin ShahMicrosoft Dynamics CRM ConsultantCommented:
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
bfuchsAuthor Commented:
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
bfuchsAuthor Commented:
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
Maximize Customer Retention with Superior Service

The IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more to help build customer satisfaction and retention.

Download the guide
Rikin ShahMicrosoft Dynamics CRM ConsultantCommented:
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.
bfuchsAuthor Commented:
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 ConsultantCommented:
Hi,

Can you try and execute with InStr available in VBScript instead of RegEx.
Rikin ShahMicrosoft Dynamics CRM ConsultantCommented:
Your code will go like-

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

Open in new window

bfuchsAuthor Commented:
instr will only get the portion 1391315regardless if its in the Patient Id field or somewhere else.
Thanks,
Ben
bfuchsAuthor Commented:
Patient ID = 1391315
there is no such a string,
see file attached on OP
Thanks,
Ben
Rikin ShahMicrosoft Dynamics CRM ConsultantCommented:
Not if you search whole string. It should work.
Rikin ShahMicrosoft Dynamics CRM ConsultantCommented:
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?
bfuchsAuthor Commented:
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 ConsultantCommented:
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?
bfuchsAuthor Commented:
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
bfuchsAuthor Commented:
Thanks Rikin.
Rikin ShahMicrosoft Dynamics CRM ConsultantCommented:
You're welcome!! Glad you're near to your solution.
bfuchsAuthor Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
System Programming

From novice to tech pro — start learning today.