Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
*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
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');
Anyone? i really need an answer to this question.
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
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.
I will upload my QVW here shortly if that would help.
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
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.
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');