Loading qlikvier with open files

[yaguilej]

Hello,

I am setting up a very simple qlikview, what it does is load excel files, the script is not complicated and works correctly. It only fails if one of the files is open.

Can you think of the reason?

Thanks

Yolanda

[Or]

Depending on the script, it could be one of two things:

1) The file is blocked for reading while it is open

2) You are reading e.g. all files in the folder, and when the file is open a temporary file is created which Qlik can't read

I would guess the second option is more likely, but either could be the problem.

For the first issue, there's nothing much you can do except try and get your sysadmins to make the file readable when open. For the second issue, you can tell it to only read specific files (ones not starting with ~, which designates a temporary file, for example), or you can use Set Errormode=0; to tell Qlik to ignore issues and move to the next file.

[yaguilej]

Hello, thank you very much for your answer.
In principle, it would be the second option, although it does not work since even if the error appears, it does not reload the file.


Regards

[Or]

I'm not sure what that means... did you try the solutions I suggested? Did they not work?

[yaguilej]

Hi,



I tried but it didn't work, what I need is that even if I have Excel open, it reloads all the files.

Right now what it does is that it skips the error, but it does not load the Excel, which I have open.



I show you the code.





Presupuestos:



//Generamos tabla ppto con un primer registro ficticio



load * Inline

[Contador_Presupuesto,Nombre_Fichero,"posicion CM",Descripción,"Importe Unitario","Cantidad (*)",Total,Observaciones,Cod.Mat.Prov.,CM,PRJ,Descripción SAP,Sistema,Proveedor,

0,Malo,xxx,aa,12,1,5656,90000,3333,4600000000,PRJ,xxxx,OOOO,ppp];







for each vFile in FileList('$(vPath)*.xlsx')



Let vFileExist=if(FileSize('$(vFile)')>0,-1,0);



if $(vFileExist) then



let vContador=$(vContador)+1;



//Generamos tabla temporal para poder almacenar CM en variable







temp:

LOAD

D



FROM

[$(vFile)] (ooxml, no labels)

Where Left(D,5)='46000';

let vContrato=Peek ('D',0);

drop Table temp;



temp2:

LOAD

C,

D



FROM

[$(vFile)] (ooxml, no labels, header is 1 lines)

Where C='Proveedor:';

//Store temp2 into [$(vQVD)prueba.qvd](qvd);

let vProveedor=Peek ('D',0);

drop Table temp2;





//Concatenamos presupuestos



Concatenate (Presupuestos)



LOAD $(vContador) as Contador_Presupuesto,

'$(vContrato)' as CM,

'$(vProveedor)' as Proveedor,

FileName () as Nombre_Fichero,

[posicion CM],

Descripción,

[Importe Unitario],

[Cantidad (*)],

Total,

Observaciones,

Cod.Mat.Prov.,

PRJ,

"Descripción SAP",

Sistema & PRJ as Clave,

Sistema



FROM [$(vFile)](ooxml, embedded labels,header is 7 lines)

Where Len([Cantidad (*)]) >0;





End If;



Next;



store Presupuestos into [$(vQVD)presupuestos.qvd](qvd);



Thanks

[Or]

If Qlik can't read the file that is currently open, no amount of code will change that. Qlik attempts to read a file and if that fails, it fails. All you can do is tell it to ignore that error and move to the next file.

If the file is temporarily open, you can put Qlik on a loop to keep trying to read it, but if the file has been left open and this prevents reading it, that won't help either. Note that Qlik typically has no issues loading from an open Excel file, so this is something local to your deployment and should be taken up with your sysadmins.