topic Re: Loading file names from a single directory in QlikView

Loading file names from a single directory

kdmarkee — Sat, 16 Nov 2024 03:03:13 GMT

Can someone tell me why not all of my file names are being loaded from my directory when I use this script logic in QlikView 12? Thanks.

//step 1 - set variables

let vRootDir = 'Q:\QlikWarehouse\Production\Source Documents\Load Scripts\';

//step 2 - loop through group folders and collect file info in each

sub DoDir (Root)
for each Ext in 'qvw'

for each File in filelist (Root & '\*.' & Ext)
MyFiles:
LOAD
FileBaseName('$(File)') as FileBaseName,
FileSize('$(File)') as FileSize,
FileName('$(File)') as FileName,
FileDir('$(File)') as FileDirectory
from '$(File)'
;
next File

next Ext

for each Dir in dirlist (Root & '\*' )
call DoDir (Dir)
next Dir

end sub

call DoDir ('$(vRootDir)')

Re: Loading file names from a single directory

olivetwist — Wed, 10 Jul 2019 11:11:11 GMT

My first question is why use the line "for each Ext in 'qvw'"? Are you planning to add additional extensions?

Re: Loading file names from a single directory

marcus_sommer — Wed, 10 Jul 2019 12:34:54 GMT

You query only qvw-files - are you really missing a qvw within your results?

- Marcus

Re: Loading file names from a single directory

kdmarkee — Wed, 10 Jul 2019 15:42:53 GMT

I'm expecting 10 files and only get 5 to load.

Re: Loading file names from a single directory

olivetwist — Wed, 10 Jul 2019 16:51:22 GMT

That code seems like overkill, unless you have a need for all the loops and excess, you can accomplish what you need with the following. Or you could use the code and modify it slightly to fit the need of your subroutine:

//step 1 - set variables

let vRootDir = 'Q:\QlikWarehouse\Production\Source Documents\Load Scripts\';

//step 2 - loop through group folders and collect file info in each

MyFiles:
LOAD Distinct
FileBaseName() as FileBaseName,
FileSize() as FileSize,
FileName() as FileName,
FileDir() as FileDirectory
from $(vRootDir)*.qvw (txt, codepage is 1252, no labels, delimiter is '\t', msq);

Re: Loading file names from a single directory

kdmarkee — Wed, 10 Jul 2019 19:36:25 GMT

Thank you, that seems to work. I was just trying different scripting code I found on the Community to get my files loaded, and although what I tried was more involved, I guess I'm still curious as to why it didn't work.

Re: Loading file names from a single directory

olivetwist — Thu, 11 Jul 2019 11:49:48 GMT

I'm not 100% sure why it didn't, but my best guess is that without the "(txt, codepage is 1252, no labels, delimiter is '\t', msq);" parameters for reading the file it may not have been able to collect the data correctly so no value was returned. not sure why it would work on some and not others. I was able to get it working by adding the parameters:

from '$(File)' (txt, codepage is 1252, no labels, delimiter is '\t', msq);

I also noticed that your orig code will loop through any sub-folders in your root while the code i provided is for a specific folder.

Hope that helps.