topic Re: Grave mark / single quote in filepath --> ' <-- in QlikView

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

Thu, 09 Apr 2015 09:34:36 GMT

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

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

Re: Grave mark in filepath --> ' <--

Tue, 14 Apr 2015 07:10:49 GMT

Anyone? i really need an answer to this question.

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

marcus_sommer — Tue, 14 Apr 2015 10:53:15 GMT

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

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

Tue, 14 Apr 2015 11:40:13 GMT

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.

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

Tue, 14 Apr 2015 11:55:57 GMT

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

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

marcus_sommer — Tue, 14 Apr 2015 11:56:19 GMT

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

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

Tue, 14 Apr 2015 12:06:25 GMT

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.

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

stjepan_stanic — Fri, 17 Apr 2015 12:19:44 GMT

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