topic Re: How to handle with Single Quotes in Load Script in QlikView

How to handle with Single Quotes in Load Script

Tue, 09 Oct 2012 17:44:48 GMT

I have a code similar to this:

For each File in Filelist ('..\source\*.csv')

// some code here, not important for this topic

Call ReadFile('$(File)');

The code works fine with the file does not have single quote in the name. However, if does, the code fails when I pass the file name to the function ReadFile.

I tried to rename the file via a macro vbscript as follows, but It also fails to pass the file name as a parameter to any function because of the single quote.

I also try to use Chr(39), Replace and many functions but one of them works.

Macro function:

function renamefile(strFile)

set fso = CreateObject("Scripting.FileSystemObject")

newfilename = replace(strFile, chr(39), "`")

fso.MoveFile strFile, newfilename

set fso = Nothing

renamefile = newfilename

end function

Anyone has any idea of how to solve this?

Re: How to handle with Single Quotes in Load Script

Gysbert_Wassenaar — Tue, 09 Oct 2012 18:08:27 GMT

I found this thread: http://community.qlik.com/thread/3915

Have you tried Readfile(File) ?

edit: something else you can try is creating a function that first loops through all the files and renames any file that contains a ', so you are sure that afterwards all files will load without problems.

Re: How to handle with Single Quotes in Load Script

Wed, 10 Oct 2012 12:48:01 GMT

It worked for that case specific but when I pass the file name for a macro function, It does not work. Ideas?

Re: How to handle with Single Quotes in Load Script

flipside — Wed, 10 Oct 2012 13:35:39 GMT

Hi Igor,

Try replacing the single quote with a double quote in the load script and put it into a variable submit to function ...

For each File in FileList ('*.csv')

newFile = Replace(File,chr(39),chr(34)) // replace ' with "

let x = ReadFile();

let x = ReadFile(newFile);

... then recover the variable value in the macro and change the characters back ...

Function ReadFile() ReadFile(newFile)

strFile = ActiveDocument.Variables("newFile").GetContent

ReadFile = Replace(strFile.String,chr(34),chr(39))

end Function

flipside

Message was edited by: flipside Corrected method, variables obviously not available during load

Re: How to handle with Single Quotes in Load Script

Wed, 10 Oct 2012 13:58:08 GMT

I mde a few changes in my rename file name function and It is working now.

Thank you for your help!