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!
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
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
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Hi,
Let me give you a quick start-
Refer FileSystemObject-https://www.tutorialspoint.com/vbscript/vbscript_fso_objects.htm
Now, you just need to loop through all files in the folder.
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
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.
Thanks,
Ben
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
objRegEx.Pattern = "[Patient ID] = 1391315"
Thanks,
Ben
Also tried the following (added all other requirements)
Thanks,
Ben
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
Thanks,
Ben
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.
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
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
Your code will go like-
If InStr (strSearchString, "Patient ID = 1391315") > 0 Then
'' Write strSearchString
End If
instr will only get the portion 1391315regardless if its in the Patient Id field or somewhere else.
Thanks,
Ben
Thanks,
Ben
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 positionNot 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
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?
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
if thats too complicate, I will close this thread and open a new one for that.
Thanks,
Ben
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
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
Premium Content
You need an Expert Office subscription to comment.Start Free Trial