How to handle with Single Quotes in Load Script

[#1]

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)');

Next

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?

[Gysbert_Wassenaar · Accepted solution]

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.

[#3]

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

[flipside]

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);

Next

... 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

[#5]

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

Thank you for your help!