topic Re: Check If Excel file exists in QlikView

Check If Excel file exists

Anonymous — Thu, 12 Jun 2014 21:02:43 GMT

Hi,

I want to check the existance of an excel file in a relative path in between 2 LOAD scripts which are being CONCATENATED.

Eg.

Table1:

LOAD * FROM ExcelA;

CHECK FILE(ExcelB) and If exists then

CONCATENATE(Table1)

LOAD * FROM ExcelB;

Could anyone help on how this can be achieved in QV script.

Thanks.

Re: Check If Excel file exists

Nicole-Smith — Thu, 12 Jun 2014 21:05:33 GMT

Table1:

LOAD * FROM ExcelA;

IF FILETIME(ExcelB)>0 THEN

CONCATENATE(Table1)

LOAD * FROM ExcelB;

END IF

Re: Check If Excel file exists

Anonymous — Thu, 12 Jun 2014 21:16:45 GMT

Hi Smith,

I have tried the above script, but it is not working

Used Debugger but the loop is not going insisde IF condition.

Table1:

LOAD * FROM ExcelA;

IF FileTime([..\Files\ExcelB.xlsx]) THEN

CONCATENATE(Table1)

LOAD * FROM ExcelB;

END IF

Please let me know whether the above script is correct. Specifically the syntax for file path.

Also, I have checked the 'Relative paths' check box.

Re: Check If Excel file exists

Nicole-Smith — Thu, 12 Jun 2014 21:27:15 GMT

Try just loading from the file using the path like you have written:

Table1:

LOAD * FROM ExcelA;

CONCATENATE(Table1)

LOAD * FROM [..\Files\ExcelB.xlsx];

Does this work?

Re: Check If Excel file exists

Anonymous — Fri, 13 Jun 2014 14:07:09 GMT

Hi Smith,

In normal LOAD the file is fetched from the path which I have mentioned above.

But in the IF statement the condition gets failed and coming out of loop.

I am using the same path in the IF statement as like used in the LOAD.

Re: Check If Excel file exists

Nicole-Smith — Fri, 13 Jun 2014 14:14:26 GMT

I think you need to write the filetime() piece like this:

FileTime('..\Files\ExcelB.xlsx')

Re: Check If Excel file exists

hic — Fri, 13 Jun 2014 14:14:32 GMT

I would do the following:

Table1:

LOAD * FROM ExcelA;

For each vFileName in FileList('C:\Path\ExcelB.xls')

CONCATENATE(Table1)

LOAD * FROM [vFileName];

Next vFileName

HIC

Re: Check If Excel file exists

Anonymous — Fri, 13 Jun 2014 14:34:48 GMT

Thanks Smith...Its working fine now.

Re: Check If Excel file exists

Anonymous — Fri, 13 Jun 2014 14:36:03 GMT

Thanks for your reply Henric.