topic Re: Loading qlikvier with open files in QlikView

Loading qlikvier with open files

yaguilej — Wed, 10 Mar 2021 14:33:56 GMT

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

Re: Loading qlikvier with open files

Or — Wed, 10 Mar 2021 14:38:52 GMT

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.

Re: Loading qlikvier with open files

yaguilej — Thu, 11 Mar 2021 11:06:36 GMT

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

Re: Loading qlikvier with open files

Or — Thu, 11 Mar 2021 11:10:12 GMT

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

Re: Loading qlikvier with open files

yaguilej — Thu, 11 Mar 2021 11:28:36 GMT

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

Re: Loading qlikvier with open files

Or — Thu, 11 Mar 2021 12:14:51 GMT

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.