On Error continue not loading additional rows

[Jebrezov]

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

[marksouzacosta]

Hi @Jebrezov ,

Could you please share your Load Script?

Regards,

Mark Costa

[Jebrezov]

i edited my post to include more details. 

[marksouzacosta]

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

[Jebrezov]

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.

[marksouzacosta]

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

[Jebrezov]

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.

[marcus_sommer]

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.

[Jebrezov · Accepted solution]

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

[marksouzacosta]

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