topic Re: On Error continue not loading additional rows in App Development

On Error continue not loading additional rows

Jebrezov — Sun, 16 Nov 2025 15:41:25 GMT

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.

Anyone ever run into an issue like this?

code is something of this structure...

-------------------------------------------------------------------------------------------------

sub ScanFolder(Root)

for each Ext in '.xlsm'

for each vFile in filelist(Root & '/*' & Ext)

//if there was an error in the last script statement, scripterror will have a nonzero value

IF ScriptError<>0 THEN

exit for

END IF

trace $(vFile);

let vfiletime = filetime(vFile);

if vfiletime < vReloadDate then

trace 5;

exit for

end if

IF left(vFileName,1)='~' then

trace 6;

exit for

END IF

Call GetProduction (vFile);

next vFile

next Ext

for each SubDirectory in dirlist(Root & '/')

call ScanFolder(SubDirectory)

next SubDirectory

end Sub

--------------------------------------------------------------------------------

sub GetProduction(vFile)

trace 10;

//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

trace 30;

end if;

end sub;

-------------------------------------------------------------------------------------------------

LIB CONNECT TO 'DataConnection';

SET ErrorMode = 0;

Let vRoot1 = 'parentfolder';

let vReloadDate = monthstart(today());

Call ScanFolder('$(vRoot1)');

SET ErrorMode = 1;

---------------------------------------------------------------------------------

Hopefully this provides a bit more helpful info.

sorry for the formatting of code, its not letting me indent and make it easier to read...

Re: On Error continue not loading additional rows

marksouzacosta — Sun, 16 Nov 2025 05:51:55 GMT

Hi @Jebrezov ,

Could you please share your Load Script?

Regards,

Mark Costa

Re: On Error continue not loading additional rows

Jebrezov — Mon, 17 Nov 2025 22:10:40 GMT

i edited my post to include more details.

Re: On Error continue not loading additional rows

marksouzacosta — Tue, 18 Nov 2025 01:36:32 GMT

Thank you @Jebrezov.

So, I would take a slightly different approach. My goal here is to isolate subjects per method to make the code more flexible and easier to manage. I also have removed some redundancies - like checking Number of Records, Concatenate statement, etc.

I hope it helps!

// List all candidate files // ****************************************************************** SET vFolderToScan = 'C:\MyDataSourceFolder'; SET vSearchPattern = '*.qvd'; Sub ListFiles(RootFolder) TRACE Scanning folder: $(vFolder) ...; For Each vFile in FileList('$(vFolder)\$(vSearchPattern)') // Add any field here to support filtering the files [TempFilesFound]: LOAD '$(vFile)' AS Filename, FileTime('$(vFile)') AS FileCreateTime, FileSize('$(vFile)') AS FileSize AutoGenerate(1) ; Next vFile For Each vFolder in DirList('$(RootFolder)\*') Call ListFiles('$(vFolder)'); Next vFolder End Sub Call ListFiles('$(vFolderToScan)'); // Filter only the target files // ****************************************************************** LET vReloadDate = MonthStart(Today()); // Add any date expression here [TempFilesToLoad]: LOAD Filename AS FilesToLoad RESIDENT [TempFilesFound] WHERE FileCreateTime > $(#vReloadDate) // Add any logic needed here to filter the files ; DROP TABLE [TempFilesFound]; // Load the filtered files data // ****************************************************************** SET vMyTableName = 'Data'; SET ErrorMode = 0; // Disable error For Each vFileToLoad in FieldValueList('FilesToLoad') TRACE Loading file $(vFileToLoad) ...; [$(vMyTableName)]: LOAD '$(vFileToLoad)' AS FileLoaded, // Optional * FROM [$(vFileToLoad)](qvd) // Change to any file format and load statement ; Next vFileToLoad SET ErrorMode = 1; // Enable error // Clean remaining tables // ****************************************************************** DROP TABLE [TempFilesToLoad];

Regards,

Mark Costa

Re: On Error continue not loading additional rows

Jebrezov — Tue, 18 Nov 2025 03:01:52 GMT

Thank you for looking @marksouzacosta, but as you mention, your response ultimately performs the same actions and does not address the issue.

If the excel file located on our local network is opened by a user, it will be locked for editing and result in a load error. By using error mode=0, I can force the script to continue, but future load statements do not execute.

any ideas relating to why all load statements after the first error result in an error? If the file causing the error is closed and the script is run, everything loads as expected.

Since this seems like a bug in qlik, my new goal is to try to identify what files are open and try to skip them so there simply are no errors, but I have not found a good way to do this.

Re: On Error continue not loading additional rows

marksouzacosta — Tue, 18 Nov 2025 13:47:33 GMT

Hi @Jebrezov,

From my tests, when that happens, my code just moves to the next file without any problem. So, the code just ignores the problematic file. Everything else should work regularly.

Regards,

Mark Costa

Re: On Error continue not loading additional rows

Jebrezov — Tue, 18 Nov 2025 14:48:26 GMT

are you connecting through a 'file via direct access gateway' connector to the location where the files are? also have you tried with xlsm files instead of qvd? these are the requirements i have to work with. ive gotten this process to work using qvds inside the tennant, but not for xlsm files through direct access gateway.

another comment, the specific error that causes future loads to not work is when the file exists and is open for editing. other errors occur (such as there not being any xlsm files within a specified folder), but the script just continues along and processes the next folder/files correctly after this type of error.

Re: On Error continue not loading additional rows

marcus_sommer — Wed, 19 Nov 2025 14:18:06 GMT

IMO there is no bug within the ErrorMode but maybe some limitations and there are also various syntax and/or logic issues possible. Limited is for example the fetched types of errors - only those one which are explicitly defined are covered and everything else will further break the execution or leading to an unexpected behaviour.

Further I doubt that ScriptError could be assigned to a variable with a LET statement - an direct access like shown in the help-example will be working (and a SET statement should be also possible). The ScriptError exists only direct after the error - each further statement will reset the value.

Another cause may be the validity of the ErrorMode and other variables. Using routines and/or loops means to create script-areas which don't be running within the global validity. It's like in other programming languages which require an explicit transfer of variables from one part to another. Therefore the order and/or the kind of nesting of the statements could have an impact and necessary variables might be added to the call-statement.

Re: On Error continue not loading additional rows

Jebrezov — Wed, 19 Nov 2025 17:16:48 GMT

I've done a bunch of tests and it appears all errors related to the direct access gateway results in a NULL value since it does not fit into any of the existing 0-16 that are defined.

when i use LET to store the scripterror value, it is correctly passing 0 or null according to the results of the following if statement "IF isnull(vErr) THEN" (even though the values always shows empty in the debug window). If i try SET, it does not work. Either way, i've removed this step and am directly checking the scripterror value in the if statement so this would not be causing any issues.

Not all errors across the data gateway cause further execution to break. an example is if a file is not found in a folder, it throws error "Connector error: Cannot find the path 'filepath/*.xlsm'", but then it continues to the next folder and successfully loads future files. However, the error "Cannot open file: 'FilePathName' (Connector error: A precondition failed. The process cannot access the file 'FilePathName' because it is being used by another process. (DirectAccess-5016)) The engine error code: EDC_ERROR:11020". Both the errors result in a NULL value and scripterror resets to 0 on next line of code execution but it seems if the script errors out during a load statement (as in the current example) all future load statements will evaluate with a scripterror of NULL even though they themselves do not throw an error. If handling this type of error isn't a bug, then it simply isn't supported yet which would be good to document.

I did find where there was a plan to allow loading from an opened excel file in direct access gateway 1.7.6 found in the below post, but I am on 1.7.8 and it is still not working. Maybe it got delayed to a future version?

https://community.qlik.com/t5/Data-Movement-Streaming/Can-t-load-data-from-open-file-on-Direct-Access-Gateway/td-p/2519761

Re: On Error continue not loading additional rows

marksouzacosta — Wed, 19 Nov 2025 17:48:18 GMT

I can reproduce this scenario, but I'll need some time to do so. Have you tried my code in your environment?

One note, I usually avoid manipulating Qlik error variables - unless is extremely required - because these variables are very restricted in terms of manipulation and is easy to miss how and where to use them.

Regards,

Mark Costa

Re: On Error continue not loading additional rows

Jebrezov — Wed, 19 Nov 2025 19:30:38 GMT

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.

Re: On Error continue not loading additional rows

marcus_sommer — Thu, 20 Nov 2025 06:00:49 GMT

Your origin code of:

IF left(vFileName,1)='~' then

trace 6;

exit for

END IF

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 ...

Re: On Error continue not loading additional rows

Jebrezov — Thu, 20 Nov 2025 06:57:01 GMT

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.

Re: On Error continue not loading additional rows

marcus_sommer — Thu, 20 Nov 2025 07:24:51 GMT

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.

Re: On Error continue not loading additional rows

marksouzacosta — Mon, 24 Nov 2025 05:59:24 GMT

Sorry for the delay @Jebrezov.

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.

Tested on Direct Access 1.7.8.

Regards,

Mark Costa

Re: On Error continue not loading additional rows

Jebrezov — Mon, 24 Nov 2025 13:46:37 GMT

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.