topic Re: Loading data from an xls file stored in qlik directory in Data Movement & Streaming

Loading data from an xls file stored in qlik directory

CardiMatt02 — Wed, 19 Mar 2025 21:48:59 GMT

I have these tree lines of code in Qlik Data Load Editor:

Set vFilePath = 'lib://Spazio condiviso MEX:DataFiles/'&$(endYear)&'.xls';
//Set vFilePath = 'lib://Spazio condiviso MEX:DataFiles/FTP_Prova.xls';
//Set vFilePath = 'lib://Spazio condiviso MEX:DataFiles/2024.xls';

The first one works and the endYear variable is '2024' but the second and third way to get the xls file return me an error in the next line of code:

The following error occurred:
Unexpected token: 'condiviso', expected one of: ':', 'OPERATOR_PLUS', 'OPERATOR_MINUS', 'OPERATOR_MULTIPLICATION', 'OPERATOR_DIVISION', 'OPERATOR_STRING_CONCAT', 'like', ...
The error occurred here:
IF FileSize(lib://Spazio >>>>>>condiviso<<<<<< MEX:DataFiles/FTP_Prova.xls) > 0 THEN

I need help to understand why.

(I tried to post this in Qlik Community/Support/Support/Get Help but could not select a Forum there)

Re: Loading data from an xls file stored in qlik directory

marksouzacosta — Wed, 31 Jul 2024 23:13:02 GMT

Hi @CardiMatt02,

Can you please share your load script with the error? I think you have a syntax problem.

Regards,

Mark Costa

Re: Loading data from an xls file stored in qlik directory

CardiMatt02 — Mon, 05 Aug 2024 07:16:40 GMT

Do while endYear<=Date#(Date(Today(),'YYYY'));

//Set vFilePath = 'lib://Spazio condiviso MEX:MEX_SFTP_PROD/prodotto/'&$(endYear)&'.xls';

//Set vFilePath = 'lib://Spazio condiviso MEX:DataFiles/'&$(endYear)&'.xls';
Set vFilePath = 'lib://Spazio condiviso MEX:DataFiles/FTP_Prova.xls';

IF FileSize($(vFilePath)) > 0 THEN
DatiVenditeTmpAnno:
REPLACE LOAD
$(endYear) as "ANNO_FILE",
$(endYear)&"CODICE ARTICOLO"&DESCRIZIONE as KeyTariffeVenduto,
"CODICE ARTICOLO" as [codice_tariffa],

This is the part of the load script where the program get the error.

The error is:
The following error occurred:
Unexpected token: 'condiviso', expected one of: ':', 'OPERATOR_PLUS', 'OPERATOR_MINUS', 'OPERATOR_MULTIPLICATION', 'OPERATOR_DIVISION', 'OPERATOR_STRING_CONCAT', 'like', ...
The error occurred here:
IF FileSize(lib://Spazio >>>>>>condiviso<<<<<< MEX:DataFiles/FTP_Prova.xls) > 0 THEN

As i said i dont understand why
Set vFilePath = 'lib://Spazio condiviso MEX:MEX_SFTP_PROD/prodotto/'&$(endYear)&'.xls';
and
Set vFilePath = 'lib://Spazio condiviso MEX:DataFiles/'&$(endYear)&'.xls';
works but
Set vFilePath = 'lib://Spazio condiviso MEX:DataFiles/FTP_Prova.xls';
where i write the name of the file instead of using a variable to define it doesn't work.

Re: Loading data from an xls file stored in qlik directory

marksouzacosta — Mon, 05 Aug 2024 13:39:39 GMT

Hi @CardiMatt02 ,

You have to add single quotes in the FileSize function:

IF FileSize('$(vFilePath)') > 0 THEN

Regards,

Mark Costa

Re: Loading data from an xls file stored in qlik directory

CardiMatt02 — Tue, 06 Aug 2024 07:55:45 GMT

I'm going to try it right now and tell you how it goes

Re: Loading data from an xls file stored in qlik directory

CardiMatt02 — Tue, 06 Aug 2024 08:14:30 GMT

Ok i tried it and with the single quotes it takes $(vFilePath) not as a variable but as a string.
So the file $(vFilePath) doesnt exists and the if isnt triggered.
Thats not the solution unfortunately.

Anyway i also want to share some more info i found yesterday evening:

i used the filepath

//Set vFilePath = 'lib://Spazio condiviso MEX:DataFiles/FTP_Prova.xls';

with a LOAD and FROM commands before the IF to see if also the LOAD and FROM commands would have given me an error of file not found.

The result is that the file IS found by the FROM command but it still doesn't want to work inside the fileSize function

Re: Loading data from an xls file stored in qlik directory

marksouzacosta — Tue, 06 Aug 2024 13:07:48 GMT

I think you are missing something in your script, or maybe you don't have access to the file you are trying to load. I did a small sample, and it works without any problem:

Be aware that using // - double slashes - comments out your code. I see you using in your example and I'm not sure if you just mistyped it or not.

Also be careful with variables like this:

Set vFilePath = 'lib://Spazio condiviso MEX:DataFiles/'&$(endYear)&'.xls';

This will not work. You have to use SET only when you are not processing/calculating the variable values - the values are hard coded. In the above case, you have to use LET and you don't need the & to combine the variables inside strings:

 LET vFilePath = 'lib://Spazio condiviso MEX:DataFiles/$(endYear).xls';

Re: Loading data from an xls file stored in qlik directory

CardiMatt02 — Tue, 06 Aug 2024 14:12:32 GMT

First of all I would like to thank you for your time.

I'm going to try again the single quotes for the variable and eventually share the error i saw here.

But there is another info that contradicts what you said about SET and LET and that really confuse me...
I've posted 4 line of code that set the variable with different values and i said that:

These work

Set vFilePath = 'lib://Spazio condiviso MEX:MEX_SFTP_PROD/prodotto/'&$(endYear)&'.xls'; Set vFilePath = 'lib://Spazio condiviso MEX:DataFiles/'&$(endYear)&'.xls';

These dont work

Set vFilePath = 'lib://Spazio condiviso MEX:DataFiles/2024.xls'; Set vFilePath = 'lib://Spazio condiviso MEX:DataFiles/FTP_Prova.xls';

So i don't understand how Set is not good with processing/calculating if the

IF FileSize($(vFilePath)) > 0 THEN

is passed and works fine with the first two.

The LOAD FROM command works fine with every value inside $(vFilePath) but the FileSize function doesn't.

So from that i don't think its a SET or LET error but i'm gonna try that too.

Update after i tried again with the single quotes:

It works now, idk whats different from the last try but it now works somehow.
So thank you.
But there still is a mistery about why

These worked also without single quotes Set vFilePath = 'lib://Spazio condiviso MEX:MEX_SFTP_PROD/prodotto/'&$(endYear)&'.xls'; Set vFilePath = 'lib://Spazio condiviso MEX:DataFiles/'&$(endYear)&'.xls';

But these needed the single quotes Set vFilePath = 'lib://Spazio condiviso MEX:DataFiles/2024.xls'; Set vFilePath = 'lib://Spazio condiviso MEX:DataFiles/FTP_Prova.xls';

Re: Loading data from an xls file stored in qlik directory

marksouzacosta — Tue, 06 Aug 2024 15:35:32 GMT

You can try this exercise, add a TRACE function under your vFilePath tests and see how Set and Let works.

This is my test code:

These are the outputs:

Note when you use Let the vFilePath gets its finished formatted value while with Set you don't.

So, when you use FileSize function without the single quotes and using SET, Qlik is 'calculating' the filename at the moment you are calling the function. Using the LET and with quotes, you are passing the already calculated/formatted filename.

FileSize with single quotes and using Set does not work because the statement gets awkward and Qlik is not able to process it. It is something like this:

FileSize(''lib://Spazio condiviso MEX:MEX_SFTP_PROD/prodotto/'& 2024 &'.xls'')

That is why you get the error:

So, as a general rule, to be safe, use SET for hardcoded values like strings, dates and, numbers. User LET for anything that will be calculated. Like strings concatenation, values returned from Qlik Functions or other variables. By the way, LET also works fine with hard coded values.

Re: Loading data from an xls file stored in qlik directory

CardiMatt02 — Wed, 07 Aug 2024 07:28:09 GMT

Great!
Thank you for the help! I've learnt a lot from this