bfuchs
asked on
Cannot read file, permission denied.
Hi Experts,
We have a script that is downloading files from FTP server every 15 minutes.
I'm writing a function that will do the following.
Loop thru all files, process them, then delete them.
Part of the function is to see if file contains some data besides of just headers.
this is the code
guess when its still in middle the download process...
what is the solution?
Thanks on advance.
We have a script that is downloading files from FTP server every 15 minutes.
I'm writing a function that will do the following.
Loop thru all files, process them, then delete them.
Part of the function is to see if file contains some data besides of just headers.
this is the code
Function CountOfRecords(sFolderPath As String, sFile As String) As Long
Dim fso
Dim folderPath
Dim file
Dim oFile
Dim fileContent
Dim arr
Dim cnt
Dim recCnt
Set fso = CreateObject("Scripting.FileSystemObject")
'folderPath = "C:\FTP\5-18\31\" 'Change the folder path as required and remember to add a back slash in the end
Set oFile = fso.OpenTextFile(sFolderPath & sFile)
fileContent = oFile.ReadAll
arr = Split(fileContent, vbCrLf)
If UBound(arr) > 0 Then
cnt = cnt + 1
recCnt = recCnt + UBound(arr)
End If
oFile.Close
Set oFile = Nothing
CountOfRecords = recCnt
End Function
However some times I get an error, permission denied.guess when its still in middle the download process...
what is the solution?
Thanks on advance.
ASKER
Hi,
How would you modify this code to accomodate this suggestion?
Thanks,
Ben
If you wait for a few seconds and retry, does the issue go away?Yes this is the case.
ask user if we wants to exit, so you don't get the user stuck in infinite wait mode.No user involved, I need this to run indefinitely.
How would you modify this code to accomodate this suggestion?
sDir = "H:\FTP\test\"
'StrFile = Dir(sDir & "*PatChanges*")
StrFile = Dir(sDir & "*" & sFileStr & "*")
Do While Len(StrFile) > 0
If InStr(1, StrFile, "Full") = 0 Then
If CountOfRecords(sDir, StrFile) > 1 Then
'Debug.Print StrFile & " - " & CountOfRecords(sDir, StrFile)
If InStr(1, StrFile, "PatChanges") > 0 Then
l = ProcessFile("Patients", sDir & StrFile)
End If
If l > 0 Then
s = "Insert into API_CallHistory(TableName,FileName, RecordsSubmitted,Results) Values ('" & sTable & "','" & StrFile & "'," & l & ",'Success') "
Else
s = "Insert into API_CallHistory(TableName,FileName, RecordsSubmitted,Results) Values ('" & sTable & "','" & StrFile & "'," & l & ",'Failure') "
End If
db.Execute s
End If
End If
On Error Resume Next
Kill sDir & StrFile
StrFile = Dir
On Error GoTo 0
Loop
Thanks,
Ben
ASKER
Should I check for the following before continuing the loop?
https://support.microsoft.com/en-us/help/291295/macro-code-to-check-whether-a-file-is-already-open
Thanks,
Ben
https://support.microsoft.com/en-us/help/291295/macro-code-to-check-whether-a-file-is-already-open
Thanks,
Ben
ASKER
I added the following
Thanks,
Ben
Do While IsFileOpen(sDir & StrFile)
' do nothing
Loop
Function IsFileOpen(filename As String)
Dim filenum As Integer, errnum As Integer
On Error Resume Next ' Turn error checking off.
filenum = FreeFile() ' Get a free file number.
' Attempt to open the file and lock it.
Open filename For Input Lock Read As #filenum
Close filenum ' Close the file.
errnum = Err ' Save the error number that occurred.
On Error GoTo 0 ' Turn error checking back on.
' Check to see which error occurred.
Select Case errnum
' No error occurred.
' File is NOT already open by another user.
Case 0
IsFileOpen = False
' Error number for "Permission Denied."
' File is already opened by another user.
Case 70
IsFileOpen = True
' Another error occurred.
Case Else
Error errnum
End Select
End Function
Wondering if this is the right way to handle this?Thanks,
Ben
No, because it will wait for the open file to close. It will be much more efficient to ignore it and proceed with other files ready to be processed.
In the next run, the locked file may be ready. If not, in the next run, etc.
In the next run, the locked file may be ready. If not, in the next run, etc.
Yes, that could be one way of doing it. There might be smarter ways, as Gustav mentions, that way should work well enough.
Actually it's worse than you think; during a download, a FTP process will not lock a file, so you can get all kinds of weird results. Anything from "permission denied" to blank files, or partially filled ones.
Are you in control of the FTP process? In other words are you pulling these files from someplace, or is someone pushing them to you? Assuming it's the later. If so, do you have control over that process?
Typically one uses a "flag" to indicate that the download/upload is complete. A couple different ways that might happen:
1. File extension is set to something like ".sending", and then renamed to a valid extension when the transfer is complete.
2. A "guard" file is used. This is a variation of #1. If the the file is being sent is abc.txt, a guard file is first created called abc.locked. The file is then transmitted and when complete, the .locked file is deleted. As long as the guard file exists, you don't touch the file.
3. Different folders are used - you send to something like \myFolder\inbox and then move the file to \myFolder\Unprocessed when the transfer is complete (you do a remote rename). The receiver then only looks for files in the \unprocessed folder knowing that any file there is complete.
In some way, you need a semaphore (a flag) to indicate when you can start working with the file.
Jim.
Are you in control of the FTP process? In other words are you pulling these files from someplace, or is someone pushing them to you? Assuming it's the later. If so, do you have control over that process?
Typically one uses a "flag" to indicate that the download/upload is complete. A couple different ways that might happen:
1. File extension is set to something like ".sending", and then renamed to a valid extension when the transfer is complete.
2. A "guard" file is used. This is a variation of #1. If the the file is being sent is abc.txt, a guard file is first created called abc.locked. The file is then transmitted and when complete, the .locked file is deleted. As long as the guard file exists, you don't touch the file.
3. Different folders are used - you send to something like \myFolder\inbox and then move the file to \myFolder\Unprocessed when the transfer is complete (you do a remote rename). The receiver then only looks for files in the \unprocessed folder knowing that any file there is complete.
In some way, you need a semaphore (a flag) to indicate when you can start working with the file.
Jim.
ASKER
It will be much more efficient to ignore it and proceed with other files ready to be processed.So I changed the loop for the following
Do While Len(StrFile) > 0 And Not IsFileOpen(sDir & StrFile)
How does that sounds?@Jim,
Actually the FTP process does name that file differently while its in process, therefore I suspect the logic that I have on script to copy from the download folder to the processing folder is the culprit.
Here is the script,
https://www.experts-exchange.com/questions/29112971/Script-causing-an-error-after-running-for-long-time.html?anchor=a42664409¬ificationFollowed=212064540&anchorAnswerId=42664409#a42664409
Wonder how easy would be to modify that, in order to fix this issue...
Thanks,
Ben
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks to all experts.
If you wait for a few seconds and retry, does the issue go away? If so you could build in a re-try functionality, e.g. wait X seconds try again, and if you've tried more than Y times, ask user if we wants to exit, so you don't get the user stuck in infinite wait mode.