topic Pb with quotes in a string in QlikView

Pb with quotes in a string

Tue, 22 Sep 2009 17:08:07 GMT

Hello,

i need to call the filesize('$(filename)') function to test if a file exists.

When the filename or the path has a quote into it, the script fails

And unfortunately i can't change the path name

For example :

set filename="c:\program files\that's my directory\my file.xls";

if isnull( filesize ('$(filename)')) then

exit script;

end if

Do you have any idea to solve this issue

Thank you

Pb with quotes in a string

Tue, 22 Sep 2009 22:05:49 GMT

The files with the single quotes do not work, because a single quote is the string terminator on your dollar sign expansion. When it hits the single quote in your filename, it thinks it has hit the end of the string. You're expression is looking for the filesize of "c:\program files\that", which I'm assuming doesn't exist.

I tried a bunch of different ways to replace the single quote with something else, but since QlikView uses a single quote as a special character, it is near impossible.

Alternatively, have you tried simply using:

filesize(filename)

I was able to use that in a test and it worked fine. Here's my load script:

Let filename = 'it' & Chr(39) & 's.txt';
Let fileSize = filesize(filename);

That gave me two variables. filename was "it's.txt" and fileSize was the correct size of the file. As you can see, you can't even create a string in QlikView load script containing a single quote, because QlikView uses it as the string terminator. Chr(39) is a QlikView function that returns a single quote. I'm guessing you are using some other means to populate the filename, so that will not be an issue. Note, I used Lets not Sets, which have slight differences, so you may want to try that (I'm not sure if it matters in your case, but it did in mine since filename use string concatenation).

Pb with quotes in a string

Tue, 22 Sep 2009 22:36:53 GMT

Thanks very much NMiller for your quick and detailed reply.

actually, as a general behaviour, if i directly use filesize(filename) instead of filesize($(filename)) in the script, QV doesn't use the content of the variable but instead tries to find the size of a file which is named filename. So, the filesize function returns null.

i already had this kind of issue before but each time i've found a workaround either by substituing the quote by another character or by chr(39) or by renaming the string. But this time it's not possible, and i must admit that i'm running out of ideas... grrrrrr

any other suggestions ?

thank you

Pb with quotes in a string

Tue, 22 Sep 2009 23:34:14 GMT

Can you post the code in your load script that pertains to this issue?

Here's a sample app I created. It uses Let filename = 'filename';. And then uses the code you posted to check if the filesize is null. It seems to work in this situation. If you reload as it, you won't get the result variable, because the file doesn't exist. If I use the commented out Let, it works.

Pb with quotes in a string

Fri, 25 Sep 2009 19:28:18 GMT

hello NMiller

finally it works with your syntax, i probably made a mistake...

however it's still a kind of mistery for me

thank you again

i'm still having an issue to solve : how to manage this quote problem when i want to use the variable content into a where clause

for example :

let myVar='Let' & chr(39) & 's rock!';

load field1, field2

from fic.xls

where field2='$(myVar)'

i can't put directly the 'Let' & chr(39) & 's rock!' in the where clause because the string is stored in the variable which is declared outside of the script (in a .qvs file)

Pb with quotes in a string

Fri, 25 Sep 2009 21:15:31 GMT

Can you manipulate the variable from the qvs file?

I was able to set up a load like this:

Directory;
LOAD Test
FROM
..\Book1.xls
(biff, embedded labels, table is Sheet1$)
WHERE(Replace(Test, Chr(39), '"') = '$(testVar)');

I had changed testVar to be Let"s Rock.

If you can somehow replace the single-quotes in your variable with double-quotes, this will work. And because you're doing the Replace in the Where clause, your data will still load with the single-quotes.

If you're loading the variable from Excel, you can also use the Replace in the load script:

LOAD Replace(Test, Chr(39), '"') ...