I have a script that loops through a network folder and all subfolders. if the target is an xlsm file, it should open, grab data, append it to the existing dataset, and continue on to the next file. this is mostly working.
I've turned off the error mode so if there is an error loading data from a file due to someone having the file open already (error "... it is being used by another process. (DirectAccess-5016))"), it continues to the next.
My issue is the load statements that are executed after this error no longer load data into the dataset. no additional error is thrown for these future load statements, but the script is functioning as if they had an error even though they wouldnt if the first error did not occur. I've added a check just after the load to check the scripterror variable by saving it to a variable and checking if it = 0 or not but the value never changes from <NULL> in the debug or if i trace the value it just prints the time. so I can't see what the script error value is even though my check of "If scripterror = 0" always results in false.
//This load into table temp is just to check to see if data can be grabbed from the file or if the file is locked by some other process and results in an error
temp: First 1 Load a, b
FROM [$(vFile)] (ooxml, embedded labels, header is 1 lines, table is [Table]);
let vError = ScriptError; // This is the variable that never reads anything other than NULL
IF vError = 0 THEN // But this evaluates as not zero every situation after the first error occurs even if no additional errors occur
IF NoOfRows('Data') > 0 THEN
//concatenates to existing table after verifying it exists with more than zero rows
concatenate(data)
Load a,b,c from [$(vFile)] (ooxml, embedded labels, header is 1 lines, table is [Table])
ELSE
//Else if table doesnt already exist with rows, it creates it
data:
Load a,b,c from [$(vFile)] (ooxml, embedded labels, header is 1 lines, table is [Table])
//If the table gets created but no data is loaded into it, remove the table
if NoOfRows('data') = 0 then drop table data; end if
//removes the temp table so we can check the next file during the next loop
drop table temp;
else
trace $(vError); //which should hold the value of scripterror after it fails to load data into temp table
i had to make a couple tweaks to where you used some variables (RootFolder, vFile,etc), but yes, i used your code and received the normal error when a file is opened as well as the subsequent loads failing to execute without throwing additional errors. it runs fine if no files are open for editing.
For additional information, i have reorganized my script to create a list of files on the network and loop through this list to load data. it runs slower, but it gives me the ability to see the temporary files windows creates when a file is open (files starting with ~$). I can then look through the list and see if there is a version of a file that contains the ~$ and skip it in the loading process in an effort to avoid these errors.
This however is not a 100% solution since the list of files i'm looping through is vast and takes time. a file that was not open at the time of check may now be open at time of load. I've shortened the time between check and load by including another loop around everything to list files, check if they should be loaded, loading, joining with stored qvd, and store on each folder within the root level.
I think this will be my solution for now, but feel the errormode = 0 should be made to handle this situation and load subsequent load statements.
should already preventing the load of an open file. Therefore I could imagine that the untouchable error isn't cause from the load-statement else from any other one, for example the file-access in beforehand through the file-function:
let vfiletime = filetime(vFile);
If so a change of the check-order may prevent the issue.
Another consideration may go to a rather fundamental change in the load-approach by transferring at first all files from the source to the target per copy & paste and then loading them from a local storage. It may sound stupid and outdated but it may a pragmatic way to bypass the limitations of the current technologies ... It was in the previous days a quite common workaround to load from ftp-server which didn't support any file-functions or wildcards to save a lot of efforts to get rid of such restrictions ...
Checking for the ~ is skipping just the temporary file that is created, not the actual file. So for a file opened, there are actually 2 version that exist, one with (the main file) and one without the ~ (the temporary file windows creates when it is open). Both would cause the load to experience the same error, so both need to be skipped.
the copy paste method is definitely an option of all else fails. I think my current method will be sufficient for now, but it would be nice if the error mode could handle this error at some point.
The check-logic might be extended to something like:
IF left(vFileName,1)='~' then
let vLockedFile = mid('$(vFilename)', 2);
trace 6;
exit for
END IF
if '$(vFilename)' = '$(vLockedFile )' then
...
Further thinkable would be to update the file-list within each iteration - in a second nested loop. In a local storage is filelist() usually quite fast - I would assume hundreds per second - and therefore this extra overhead may not significantly increase the overall run-time.
Beside the above you may consider to replace and/or to extend the trace-approach with a logging-load, like:
if x = y then trace X; t: load now() as RunTime, 'X' as Status, '$(vFileName') as File, ... autogenerate 1; ...
and then storing the table as qvd and/or combined with the historical runs which would also be useful to implement an incremental logic and to track the stuff over the time.
I believe you’ve discovered a bug in the File (via Direct Access Gateway) Data Connector. In short, during a Load Script execution, if an error occurs at any point, the File connector enters a state where it can no longer load anything - showing the message “MakeBusy cannot be called when state is Busy.” I tried several approaches to avoid this, but none were successful. It also appears that no solution to this issue is currently available.
However, I did find a way to reduce the likelihood of triggering this error. Below is my modified Load Script. I hope it helps:
// List all candidate files
// ******************************************************************
SET vFolderToScan = 'lib://IPC DevOps:Sample';
SET vSearchPattern = '*.xlsm';
Sub ListFiles(RootFolder)
TRACE Scanning folder: $(RootFolder) ...;
For Each vFile in FileList('$(RootFolder)/$(vSearchPattern)')
// Add any field here to support filtering the files
[TempFilesFound]:
LOAD
'$(vFile)' AS Filename,
SubField('$(vFile)','/',-1) AS FileBaseName,
FileTime('$(vFile)') AS FileCreateTime,
FileSize('$(vFile)') AS FileSize
AutoGenerate(1)
;
Next vFile
For Each vFolder in DirList('$(RootFolder)/')
TRACE Subfolder found $(vFolder);
Call ListFiles('$(vFolder)');
Next vFolder
End Sub
Call ListFiles('$(vFolderToScan)');
// Filter only the target files
// ******************************************************************
LET vReloadDate = MonthStart(Today()); // Add any date expression here
// Load non-opened files
[TempFilesToLoad]:
LOAD
FilesToLoad,
FileBasePath & '~$' & FileBaseName AS OpenedFilename
;
LOAD
Filename AS FilesToLoad,
FileBaseName,
Replace(Filename, FileBaseName,'') AS FileBasePath
RESIDENT
[TempFilesFound]
WHERE
1=1
AND FileCreateTime > $(#vReloadDate) // Add any logic needed here to filter the files
AND SubStringCount(Filename,'~$') = 0
;
DROP TABLE [TempFilesFound];
// Load the filtered files data
// ******************************************************************
SET vMyTableName = 'Data';
For vFileIndex = 0 To NoOfRows('TempFilesToLoad')-1
LET vFileToLoad = Peek('FilesToLoad',$(vFileIndex),'TempFilesToLoad');
LET vOpenedFilename = Peek('OpenedFilename',$(vFileIndex),'TempFilesToLoad');
TRACE Loading file $(vFileToLoad) ...;
// Last second opened file check
If IsNull(FileTime('$(vOpenedFilename)')) Then
[$(vMyTableName)]:
LOAD
'$(vFileToLoad)' AS FileLoaded, // Optional
Plan,
AgeGroupMin,
AgeGroupMax,
NewValue,
Adjustment
FROM
[$(vFileToLoad)]
(ooxml, embedded labels, table is Sheet1)
;
Else
TRACE [$(vFileToLoad)] file is opened and cannot be load;
End If
Next vFileIndex
// Clean remaining tables
// ******************************************************************
DROP TABLE [TempFilesToLoad];
The main difference in this approach is that we check every single found file, right before loading it, if the file is opened or not.
Thank you @marksouzacosta for replicating the error and proving its existence. You're solution is similar to what I had to putting together for my situation to minimize the chance of errors occurring. For now, i think this is the needed solution, but i hope this will be resolved in the future.
