bfuchs
asked on
Check why log file does not include file names.
Hi Experts,
I have the following code that processes files and then writes to a log file the file name and time .
Now for some reason, many entries do not contain file names, for example
How do I change that it should always include file names?
Thanks
I have the following code that processes files and then writes to a log file the file name and time .
Public Sub CallImportDataToCaspio()
Dim StrFile As String, strTable As String, sFileStr As String
Dim sDir As String
Dim l As Long, s As String, i As Long
Dim db As Database
Set db = CurrentDb
sDir = "E:\AppDev\FTP\Caspio\"
'sDir = "E:\AppDev\FTP\Caspio\"
'StrFile = Dir(sDir & "*PatChanges*")
StrFile = Dir(sDir & "*" & sFileStr & "*")
Do While Len(StrFile) > 0
If Not IsFileOpen(sDir & StrFile) Then
'' Do While IsFileOpen(sDir & StrFile)
'' ' do nothing
'' Loop
If InStr(1, StrFile, "Full") = 0 And InStr(1, StrFile, "Part") = 0 Then
i = CountOfRecords(sDir, StrFile)
If i > 1 Then
'Debug.Print StrFile & " - " & CountOfRecords(sDir, StrFile)
If InStr(1, StrFile, "PatChanges") > 0 Then
strTable = "Patients"
l = ImportDataToCaspio(strTable, sDir & StrFile, i)
ElseIf InStr(1, StrFile, "SchChanges") > 0 Then
strTable = "Schedule"
l = ImportDataToCaspio(strTable, sDir & StrFile, i)
ElseIf InStr(1, StrFile, "CGChanges") > 0 Then
strTable = "Caregivers"
l = ImportDataToCaspio(strTable, sDir & StrFile, i)
'' ElseIf InStr(1, StrFile, "PatMedProfileChangesV2") Then
'' strTable = "Patients_Medications"
'' l = ImportDataToCaspio(strTable, sDir & StrFile, i)
Else
strTable = ""
l = 0
End If
If l > 0 Then
s = "Insert into API_CallHistory(TableName,FileName, RecordsSubmitted,Results) Values ('" & strTable & "','" & StrFile & "'," & l & ",'Success') "
Else
If strTable = "Patients" Or strTable = "Schedule" Then
s = "Insert into API_CallHistory(TableName,FileName, RecordsSubmitted,Results) Values ('" & strTable & "','" & StrFile & "'," & l & ",'Failure') "
End If
End If
If Len(s) > 0 Then
db.Execute s
End If
End If
End If
On Error Resume Next
Kill sDir & StrFile
End If
StrFile = Dir
On Error GoTo 0
LogFile "E:\AppDev\Logs\UpdateCaspio.txt", StrFile & " - " & Now
Loop
If StrFile = "" Then
DoEvents
End If
End Sub
Now for some reason, many entries do not contain file names, for example
- 8/28/2019 2:03:09 PM
- 8/28/2019 2:03:09 PM
- 8/28/2019 2:03:12 PM
- 8/28/2019 2:03:14 PM
- 8/28/2019 2:03:34 PM
- 8/28/2019 2:18:41 PM
- 8/28/2019 2:18:42 PM
- 8/28/2019 2:18:43 PM
- 8/28/2019 2:18:43 PM
- 8/28/2019 2:18:45 PM
- 8/28/2019 2:19:16 PM
How do I change that it should always include file names?
Thanks
ASKER
Hi,
Thanks,
Ben
- Folders, wich the code/program thinks are filesThere are no files and no folders recorded as shown above.
- Files with special characters (eg. spaces) in it.No such files.
Thanks,
Ben
What is the value of the sFileStr variable?
It's a bad idea to change the set of items you're iterating. In this case, you shouldn't rename or delete any of the files in the directory in your loop.
Kill sDir & StrFile
Instead, add the sDir & StrFile to a collection in the loop. Then add a For Each...Next loop and do your file deletions.
Alternatively, you can do an initial iteration, adding the file paths to a collection and change your current iteration to a For Each...Next
Yeah I dont get sFilestr variable either
why not just
StrFile = Dir(sDir & "*" & sFileStr & "*")
why not just
sDir = "E:\AppDev\FTP\Caspio\*.*"
strFile = Dir(sDir)
I think its a bit overcomplex
Public Function WriteLog()
Dim StrFile As String
Open "C:\Temp2\LogF.txt" For Output As #1 ' Create file name.
StrFile = Dir("E:\AppDev\FTP\Caspio\")
Do While Len(StrFile) > 0
Write #1, StrFile
StrFile = Dir
Loop
Close #1
End Function
Take a look and change paths accordingly
A little change to avoid some nasty quotes
Public Function WriteLog()
Dim StrFile As String
Open "C:\Temp2\LogF.txt" For Output As #1 ' Create file name.
StrFile = Dir("E:\AppDev\FTP\Caspio\")
Do While Len(StrFile) > 0
Print #1, StrFile
StrFile = Dir
Loop
Close #1
End Function
ASKER
Hi Experts,
Sorry for posting this but I'm not really getting how your suggestions will take care of my problem...
Firstly, the reason I need the LogFile procedure executing within the main loop, is so in case something goes wrong while processing those files, I know which file it happened, including the time it happened. (I also have a script checking for that log file's time updated and send an email if it paused for more than a half hour, so must know the exact time it stopped).
Yeah I dont get sFilestr variable either
I see your point, this code has been rearranged (where this was getting its value from a passing param) and this portion remained there...
However as this not seem to cause any problems with the file execution loop, I cant see how changing this will fix my log issue?
@John,
Are you suggesting a different WriteLog approach?
from what I am seeing, the problem is not laying there, just in the calling function posted.
FYI- this is the LogFile function being used.
Perhaps you can post those suggestions implemented within my code snippet above, so I will test and see if it works (as long it works i'm fine for now, have time to understand it at a later occasion...-:)
Thanks,
Ben
Sorry for posting this but I'm not really getting how your suggestions will take care of my problem...
Firstly, the reason I need the LogFile procedure executing within the main loop, is so in case something goes wrong while processing those files, I know which file it happened, including the time it happened. (I also have a script checking for that log file's time updated and send an email if it paused for more than a half hour, so must know the exact time it stopped).
What is the value of the sFileStr variable?
Yeah I dont get sFilestr variable either
I see your point, this code has been rearranged (where this was getting its value from a passing param) and this portion remained there...
However as this not seem to cause any problems with the file execution loop, I cant see how changing this will fix my log issue?
@John,
Are you suggesting a different WriteLog approach?
from what I am seeing, the problem is not laying there, just in the calling function posted.
FYI- this is the LogFile function being used.
Public Sub LogFile(ByVal LogfilePath As String, ByVal Content As String)
' Text file I/O constants
Const ForWriting = 2
Const ForAppending = 8
' Local variables
Dim FSO As Object
Dim LogFile As Object
Dim LogFolder As String
' Suppress any errors in this routine
On Error Resume Next
' Create filesystem object
Set FSO = CreateObject("Scripting.FileSystemObject")
' If log file exists, append to it, otherwise create it
If FSO.FileExists(LogfilePath) = True Then
Set LogFile = FSO.OpenTextFile(LogfilePath, ForAppending)
Else
' Make sure the log file parent folder exists, create if needed
LogFolder = FSO.GetParentFolderName(LogfilePath)
If Not FSO.FolderExists(LogFolder) Then
MakeDir LogFolder, FSO
End If
If Not FSO.FolderExists(LogFolder) Then
Exit Sub
End If
Set LogFile = FSO.OpenTextFile(LogfilePath, ForWriting, True)
End If
' Write content and close log file
LogFile.WriteLine Content
LogFile.Close
Set LogFile = Nothing
Set FSO = Nothing
End Sub
Perhaps you can post those suggestions implemented within my code snippet above, so I will test and see if it works (as long it works i'm fine for now, have time to understand it at a later occasion...-:)
Thanks,
Ben
Hi Ben
sFileStr is empty so:
Your dirname is now: E:\AppDev\FTP\Caspio\**
or E:\AppDev\FTP\Caspio\*NULL *
I dont see why the logfile function should have anything to do with it. Every time the loop passes, the logfile function is executed and writes a line into that textfile. So you get empty values from the loop (StrFile)
OOOHH. I think I just saw it. If a file is open. Its not processed and you get an empty StrFile value, that is written in the LOGFILE....Because logfilecreation is outside the If NOT isFileOpen
@all others...would that make sense?
sFileStr is empty so:
Your dirname is now: E:\AppDev\FTP\Caspio\**
or E:\AppDev\FTP\Caspio\*NULL
I dont see why the logfile function should have anything to do with it. Every time the loop passes, the logfile function is executed and writes a line into that textfile. So you get empty values from the loop (StrFile)
OOOHH. I think I just saw it. If a file is open. Its not processed and you get an empty StrFile value, that is written in the LOGFILE....Because logfilecreation is outside the If NOT isFileOpen
@all others...would that make sense?
and remove StrFile = Dir at 56..makes no sense either for me.
I suggest you give it a try...its a very simple piece of code...your code just uses the FSO object ...that's just another way...
Just a small update...if the file exists then you Append instead of Output
Just a small update...if the file exists then you Append instead of Output
Open "C:\Temp2\LogF.txt" For Append As #1
ASKER
@Thomas U,
Well if the folder/file path was wrong then the entire function shouldn't of be functioning, no?
As mentioned e/t is working perfectly except for the log file.
In rare cases will the file be open (only if another process is just middle creating them), and as you can see, none of the log records contain file names, which would mean that all files were open, maybe code logic makes sense, but in actuality its not the case.
See attached what files were processed at the time the log above was created.
Thanks,
Ben
Untitled.png
sFileStr is empty so:
Your dirname is now: E:\AppDev\FTP\Caspio\**
or E:\AppDev\FTP\Caspio\*NULL*
Well if the folder/file path was wrong then the entire function shouldn't of be functioning, no?
As mentioned e/t is working perfectly except for the log file.
OOOHH. I think I just saw it. If a file is open. Its not processed and you get an empty StrFile value, that is written in the LOGFILE....Because logfilecreation is outside the If NOT isFileOpen
In rare cases will the file be open (only if another process is just middle creating them), and as you can see, none of the log records contain file names, which would mean that all files were open, maybe code logic makes sense, but in actuality its not the case.
See attached what files were processed at the time the log above was created.
and remove StrFile = Dir at 56..makes no sense either for me.No way, this is were the next file in folder gets into the StrFile variable, in order to get executed at the next round of the loop...
Thanks,
Ben
Untitled.png
remove StrFile = Dir at 56This declaration is needed for the loop...without this...after the 1st file the iteration will jump back to the same..
Ben i think the code is good besides the unitialized variable...just make a small temp folder and drag-drop some files...debug your code if it fails somewhere else and you should be good to go.
ASKER
@John,
Can you post the entire section/function?
Thanks,
Ben
I suggest you give it a try...its a very simple piece of code..I've gladly try it, just not sure where exactly to place it, and what should be replaced by this...
Can you post the entire section/function?
Thanks,
Ben
ah sry my bad...msaccess vba..
Well ....this is the complete code... :)
Just change here
Open "C:\Temp2\LogF.txt" For Output As #1
to the path where you want the log saved...i think is : E:\AppDev\Logs\UpdateCaspi o.txt"
and the folder to iterate...
StrFile = Dir("E:\AppDev\FTP\Caspio\ ") .... again i think this is the source folder
Just add a Dir(logFile) to check if the log File exists....eg. strLogFile = Dir("E:\AppDev\FTP\Caspio\ ")
If NO [Len(StrLogFile) =0] -->Open StrLogFile For Output As #1
If YES [Len(StrLogFile)>0-->Open StrLogFile For Append As #1
and that's all
Just change here
Open "C:\Temp2\LogF.txt" For Output As #1
to the path where you want the log saved...i think is : E:\AppDev\Logs\UpdateCaspi
and the folder to iterate...
StrFile = Dir("E:\AppDev\FTP\Caspio\
Just add a Dir(logFile) to check if the log File exists....eg. strLogFile = Dir("E:\AppDev\FTP\Caspio\
If NO [Len(StrLogFile) =0] -->Open StrLogFile For Output As #1
If YES [Len(StrLogFile)>0-->Open StrLogFile For Append As #1
and that's all
ASKER
@John,
Is this meant to use in the function you posted above?
If yes, as mentioned I cannot have a separate routine for looping thru all files and writing them to the log, as I need to inspect each file if it was successful and also record the time file was processed.
Thanks,
Ben
Is this meant to use in the function you posted above?
If yes, as mentioned I cannot have a separate routine for looping thru all files and writing them to the log, as I need to inspect each file if it was successful and also record the time file was processed.
Thanks,
Ben
Ben ...in short ...yes.. this is the code...
As for separate/non-separated is up to you...i just provided a working sample..
Essentially the code would be :
As for separate/non-separated is up to you...i just provided a working sample..
Essentially the code would be :
Public Function WriteLog()
Dim logFile as String
Dim StrFile As String
strLogFile = Dir("E:\AppDev\FTP\Caspio\")
If len(strLogFile)>0 then
Open strLogFile For Append As #1 ' Append to Log File
else
Open strLogFile For Output As #1 ' Create Log File
end if
StrFile = Dir("E:\AppDev\FTP\Caspio\")
Do While Len(StrFile) > 0
'Here you put your checking ...it can be in the body of this method or separated...e.g CheckValidForLogging(strFile)
Print #1, StrFile
StrFile = Dir
Loop
Close #1
End Function
ASKER
Hi John,
Obviously there seems to be some kind of confusion here, as I am trying to convey the message multiple times, that the goal here is not to get a list of all files in that directory, it is about to get a log of all processed files including the time they were processed.
From your suggestion, (and same for Aikimark), I don't see that being accomplished.
Thanks,
Ben
Obviously there seems to be some kind of confusion here, as I am trying to convey the message multiple times, that the goal here is not to get a list of all files in that directory, it is about to get a log of all processed files including the time they were processed.
From your suggestion, (and same for Aikimark), I don't see that being accomplished.
Thanks,
Ben
Ben
Since I can't see the entire module, please check that you have an
Option Explicit
statement in your General Declarations section and compile your code.
Since I can't see the entire module, please check that you have an
Option Explicit
statement in your General Declarations section and compile your code.
ASKER
@Aikimark,
No I don't have that.
But is that going to solve my problem or just another enhancement to the program...?
BTW, didn't think its gonna be such a difficult task...
Thanks,
Ben
No I don't have that.
But is that going to solve my problem or just another enhancement to the program...?
BTW, didn't think its gonna be such a difficult task...
Thanks,
Ben
Please add the statement and compile your code.
ASKER
@aikimark,
done that, took quite a while to declare all variables, hope e/t was done correctly...
Still having same issue, some get recorded and some not. see attached.
Thanks,
Ben
Untitled.png
done that, took quite a while to declare all variables, hope e/t was done correctly...
Still having same issue, some get recorded and some not. see attached.
Thanks,
Ben
Untitled.png
Ben
Can you discern any name(s) of the missing files?
If so, check for extended ASCII or unicode characters in the name.
Are you still deleting the file inside the iteration loop? You can revisit my comment about that change.
Can you discern any name(s) of the missing files?
If so, check for extended ASCII or unicode characters in the name.
Are you still deleting the file inside the iteration loop? You can revisit my comment about that change.
ASKER
Hi,
Thanks,
Ben
If so, check for extended ASCII or unicode characters in the name.As shown above, those file names do not contain any of them.
Are you still deleting the file inside the iteration loop? You can revisit my comment about that change.Can you offer an explanation why not, and how will changing this fix my issue...?
Thanks,
Ben
ASKER
Hi Experts,
Think I got it.
It was the order of the code below that was wrong...
While it should have been.
Wondering how come none of you experts were even looking at this direction...
Thanks,
Ben
Think I got it.
It was the order of the code below that was wrong...
StrFile = Dir
On Error GoTo 0
LogFile "E:\AppDev\Logs\UpdateCaspio.txt", StrFile & " - " & Now
Loop
While it should have been.
LogFile "E:\AppDev\Logs\UpdateCaspio.txt", StrFile & " - " & Now
StrFile = Dir
On Error GoTo 0
Loop
Wondering how come none of you experts were even looking at this direction...
Thanks,
Ben
It isn't a matter of "not looking at this direction" as it was not having a sample data file to be able to step through your code, inspecting the variables.
Can you offer an explanation why notIf you hear the phrase "pulled the rug out from under me(/you/them/us)", what does it mean to you?
If a program or process is processing a fixed number of items and the process changes the number of items, what do you think happens?
Example:
You have the following collection (myCol) : 1, 2, 3, 4, 5, 6, 7, 8
You iterate through the array with a For...Next loop, printing each item.
For lngLoop = 1 To myCol.Count
Debug.Print myCol(lngLoop)
Next
In the Immediate Window, you see1
2
3
4
5
6
7
8
Now, if we delete one of our items while we're looping, see what happensFor lngLoop = 1 To myCol.Count
Debug.Print myCol(lngLoop)
If lngLoop = 3 Then
myCol.Remove lngLoop
End If
Next
The Immediate Window displays an interesting sequence, where 4 is missing. Note that 4 is still in the collection, but we've screwed around with the list during iteration. 1
2
3
5
6
7
8
We also raise an error, since we have changed the value of myCol.Count during our iteration.
A small edit..since in my test i only created the file ...no Appending
Public Function WriteLog()
Dim logFile as String
Dim StrFile As String
strLogFile = "E:\AppDev\FTP\Caspio\"
If len(Dir(strLogFile))>0 then
Open strLogFile For Append As #1 ' Append to Log File
else
Open strLogFile For Output As #1 ' Create Log File
end if
StrFile = Dir("E:\AppDev\FTP\Caspio\")
Do While Len(StrFile) > 0
'Here you put your checking ...it can be in the body of this method or separated...e.g CheckValidForLogging(strFile)
Print #1, StrFile
StrFile = Dir
Loop
Close #1
End Function
Just for kicks the code worked beautifully when i tested..why it failed beats me.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Didn't read all the code...but usually:
- Folders, wich the code/program thinks are files
- Files with special characters (eg. spaces) in it.
regards
Thomas