Grave mark / single quote in filepath --> ' <--

[#1]

*QVW ADDED

hey everyone.

i have a problem with a Grave mark / single quote in my file path:

Cannot open file '\\MS-05\reacdata\2002Flash Casino''s Lemmer\ReacUurExport\REAC550FE420.XML' The system cannot find the path specified.

The folder is actualy called: 2002Flash Casino's Lemmer.

However when executing the script it makes the ' a "

In the files in the folder however it is still a '

Therefor it cannot load the data, it wont make the link.

is there anyway around it, my code for finding the XML files is:

CODE

sub ScanFolder(Root) let dirRoot = Root;           for each FileExtension in 'xml'                     for each FoundFile in filelist( Root & '\*.' & FileExtension)                               FileList:                               LOAD '$(FoundFile)' as SourceFile                               , '$(dirRoot)' as DirRoot                               , left(SubField('$(dirRoot)','\',-2),len(keepchar(SubField('$(dirRoot)','\',-2),'0123456789'))) as Kostenplaats_tmp                             , mid(SubField('$(dirRoot)','\',-2),len(keepchar(SubField('$(dirRoot)','\',-2),'0123456789'))+1) as Vestiging_tmp                             , len(keepchar(SubField('$(dirRoot)','\',-2),'0123456789')) as count                               AUTOGENERATE 1         where WildMatch('$(dirRoot)','*ReacUurExport');                              // Set vConcatenate = Concatenate;                     next FoundFile           next FileExtension           for each SubDirectory in dirlist( Root & '\*' )                     call ScanFolder(SubDirectory)           next SubDirectory end sub Call ScanFolder('\\MS-05\reacdata') ;

I have 53 Directories it has to open and search for the directory ReacUurExport in wich the XML's for each "Vestiging_TMP" are located

It works perfectly with 39 Directories, however the 14 witch use a Grave mark wont.

Kind Regards

Mike Cleven

Message was edited by: Mike Cleven

Message was edited by: Mike Cleven Added QVW

[#2]

Anyone? i really need an answer to this question.

[marcus_sommer]

I think two different approaches could be helpful:

1. replacing the inside single-quote with an another char

     let dirRoot = replace(Root, chr(39), '###');

     ...

     replace('$(dirRoot)', '###', chr(39)) as DirRoot

     ...

2. wrapping the path into double-quotes

     for each SubDirectory in dirlist( Root & '\*' )

          let temp = chr(34) & SubDirectory & chr(34);

          call ScanFolder(temp)

     next SubDirectory

There are several various places possible where you could try to change the script-syntax with one of these approaches.

- Marcus

[#4]

Both options result in an load error for me, it wont recognize the rest of the code and will refuse to load.

Even if i comment out the rest of the code which isn't needed for the load it will simply do nothing.

[#5]

I will upload my QVW here shortly if that would help.

[marcus_sommer]

Yes it's quite difficult and will need some attempts. I always struggle with this kind of syntax-issues but at least one of the above mentioned approaches worked for me whereby I hadn't have a case with single-quotes within the path.

I suggest you load one of these files manually with the table-wizard from qlikview to see how qlikview handled these path-syntax. Then I would try to rebuild these syntax in a very reduced script and would check my attempts with some trace-statements.

- Marcus

[#7]

When trying manually for 1 or even 5 XML's it works fine, but as soon as i put it in a loop the single-quote will mess things up.

And since the absolute path to the files changes every hour (new file with unique name every hour) i will have to use relative paths.
However i will try with your syntax and see if i get this fixed!

if so i will post it online.

[stjepan_stanic · Accepted solution]

replace this part of your code:

FileList:

                              LOAD '$(FoundFile)' as SourceFile

                              , '$(dirRoot)' as DirRoot

                              , left(SubField('$(dirRoot)','\',-2),len(keepchar(SubField('$(dirRoot)','\',-2),'0123456789'))) as Kostenplaats_tmp

                            , mid(SubField('$(dirRoot)','\',-2),len(keepchar(SubField('$(dirRoot)','\',-2),'0123456789'))+1) as Vestiging_tmp

                            , len(keepchar(SubField('$(dirRoot)','\',-2),'0123456789')) as count

                              AUTOGENERATE 1

        where WildMatch('$(dirRoot)','*ReacUurExport');

by this one:

FileList:

                              load

                              *

                              , left(SubField(DirRoot,'\',-2),len(keepchar(SubField(DirRoot,'\',-2),'0123456789'))) as Kostenplaats_tmp

                            , mid(SubField(DirRoot,'\',-2),len(keepchar(SubField(DirRoot,'\',-2),'0123456789'))+1) as Vestiging_tmp

                            , len(keepchar(SubField(DirRoot,'\',-2),'0123456789')) as count

                            ;

                              load

                              *

                              , Replace(SourceFile_tmp,chr(39)&chr(39),chr(39)) as SourceFile

                              , Replace(DirRoot_tmp,chr(39)&chr(39),chr(39)) as DirRoot

                              ;

                              LOAD

                              '$(FoundFile)' as SourceFile_tmp

                              , '$(dirRoot)' as DirRoot_tmp                               

                             AUTOGENERATE 1

         where WildMatch('$(dirRoot)','*ReacUurExport');