topic loop through to load all files from a folder and its subfolders? in QlikView

loop through to load all files from a folder and its subfolders?

Tue, 25 Sep 2012 14:23:21 GMT

Hi all,

This is my folder structure

MainFolder

SubFolderr1

File1

File2

SubFolder2

File3

File4

SubFolder3

File5

File6

How to load all the files dynamically if i give only "MainFolder" Name.

Thanks in advance

Regards

Kumar

Re: loop through to load all files from a folder and its subfolders?

hic — Tue, 25 Sep 2012 14:34:20 GMT

The basic structure would be the following, but you can add more in the Load statement.

HIC

sub ScanFolder(Root)

for each FileExtension in 'qvw','mp3','ape','flac','ogg'

for each FoundFile in filelist( Root & '\*.' & FileExtension)

FileList:

Load

'$(FoundFile)' as [FilenameWithPath],

'$(FileExtension)' as [Extension]

Autogenerate(1);

next FoundFile

next FileExtension

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

call ScanFolder(SubDirectory)

next SubDirectory

end sub

Call ScanFolder('.') ;

Re: loop through to load all files from a folder and its subfolders?

Tue, 25 Sep 2012 14:53:36 GMT

Hi Henric,

This is what i am using

sub ScanFolder(Root)

for each FileExtension in 'csv'

for each FoundFile in filelist( Root & '\*.' & Extension)

FileList:

Load

'$(FoundFile)' as [FilenameWithPath],

'$(FileExtension)' as [Extension]

Autogenerate(1);

next FoundFile

next FileExtension

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

call ScanFolder(SubDirectory)

next SubDirectory

end sub

Call ScanFolder('E:\ManFolder') ;

It is reading MainFolder and its SubFoldedrs.

But not loading the data.

Regards

Kumar

Re: loop through to load all files from a folder and its subfolders?

hic — Tue, 25 Sep 2012 15:03:19 GMT

Then you should replace the load statement by a

Load * from [$(FoundFile)] ( ... file specification ...)

(You need to generate one Load statement the normal way to get the file specification right.)

Do all the files have identical structure? if not, you should instead use the following so that you make sure that all files are concatenated:

Set vConcatenate = ;

sub ScanFolder(Root)

for each FileExtension in 'csv'

for each FoundFile in filelist( Root & '\*.' & Extension)

FileList:

$(vConcatenate)

Load * from [$(FoundFile)] ( ... file specification ...) ;

Set vConcatenate = Concatenate;

next FoundFile

next FileExtension

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

call ScanFolder(SubDirectory)

next SubDirectory

end sub

Call ScanFolder('E:\ManFolder') ;

HIC

Re: loop through to load all files from a folder and its subfolders?

Tue, 25 Sep 2012 15:17:07 GMT

Hi Henric,

It's not working.

Please collect the sample structure along with data in the attachment.

My target is to prepare a single qvd for all those files and those are having the same column structure.

(Note:read the files from 3rd row in csv files)

Regards

Kumar

Re: loop through to load all files from a folder and its subfolders?

hic — Tue, 25 Sep 2012 16:01:10 GMT

Then you need to set the header size correctly when you create your Load statement:

This Load works fine on my computer:

Set vConcatenate = ;

sub ScanFolder(Root)

for each FileExtension in 'csv'

for each FoundFile in filelist( Root & '\*.' & FileExtension)

FileList:

$(vConcatenate)

LOAD *, '$(FoundFile)' as SourceFile

FROM [$(FoundFile)] (txt, codepage is 1252, embedded labels, delimiter is ',', msq, header is 2 lines);

Set vConcatenate = Concatenate;

next FoundFile

next FileExtension

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

call ScanFolder(SubDirectory)

next SubDirectory

end sub

Call ScanFolder('C:\Users\hic\Documents\2012\Work\QV Apps\DoDir') ;

HIC

Re: loop through to load all files from a folder and its subfolders?

Tyler_Waterfall — Mon, 11 Mar 2013 14:03:02 GMT

Awesome Henric! Thanks for sharing!

Tyler

Re: loop through to load all files from a folder and its subfolders?

Fri, 11 Oct 2013 10:20:41 GMT

i am getting syntax error in

$(vConcatenate).

please help me how to reslove this

Re: loop through to load all files from a folder and its subfolders?

hic — Tue, 15 Oct 2013 08:05:00 GMT

You will get a swiggly line under the $(vConcatenate) since the syntax check does not know what text this dollar-expansion will create. But this is not a problem. The script will run anyway.

HIC

Re: loop through to load all files from a folder and its subfolders?

rustyfishbones — Wed, 08 Jul 2015 12:29:46 GMT

Hi Henric,

Is it possible to Parametrize the FileExtension

so that the CALL statement would be

CALL ScanFolder('csv','C:\Users\hic\Documents\2012\Work\QV Apps\DoDir')

Regards

Alan

Re: loop through to load all files from a folder and its subfolders?

hic — Wed, 08 Jul 2015 12:46:40 GMT

Yes, no problem. I use

Sub ScanFolder(Root)

for each FileExtension in 'csv'

for each FoundFile in filelist( Root & '\*.' & FileExtension)

...

End Sub

But you should instead use

Sub ScanFolder(FileExtension, Root)

for each FoundFile in filelist( Root & '\*.' & FileExtension)

...

End Sub

HIC

Re: loop through to load all files from a folder and its subfolders?

uacg0009 — Wed, 12 Oct 2016 10:07:04 GMT

Hi Henric,

Is it possible to load files stored not in a folder in my computer, but from Internet?

I tried Call Scanfolder('http:\\00.00.0.00\data\qlikview') and end up with no result. But I can successfully load a single file from the same address.

BR, Aiolos

Re: loop through to load all files from a folder and its subfolders?

hic — Wed, 12 Oct 2016 10:18:53 GMT

To my knowledge, it is not possible to use the http protocol for this, since there is no possibility to browse a folder using http. In principle, it should be possible to use ftp though.

HIC

Re: loop through to load all files from a folder and its subfolders?

ngrunoz — Tue, 11 Apr 2017 20:29:14 GMT

Hi Henric. I am new to qlikview and am not so conversant with programming. Please may you help me follow through this synthesis.

When I copied the script its showing me some errors.

Re: loop through to load all files from a folder and its subfolders?

hic — Wed, 12 Apr 2017 12:13:26 GMT

This script is OK. You are right that the color coding and the squiggly lines make it look as if there is a problem, but there isn't. What happens is that the parser gets lost at $(Concatenate) which it cannot interpret (since it gets it value only when the script is run.

Change the path in the Call statement to something that exists on your computer, and I'll bet you it works.

HIC

Re: loop through to load all files from a folder and its subfolders?

Anonymous — Wed, 12 Apr 2017 13:29:21 GMT

Hi Henric,

I am getting below error, could you please help me

My script as follows

Re: loop through to load all files from a folder and its subfolders?

hic — Wed, 12 Apr 2017 14:20:03 GMT

The ( ... file specification ...) is just a placeholder for the real file specification. You need to replace this with a file specification that fits your file. Use the file wizard (the button "Table Files") to create one that is OK.

HIC

Re: loop through to load all files from a folder and its subfolders?

Anonymous — Tue, 25 Apr 2017 17:43:13 GMT

The loop works great for some of my datasets, however, I am having an issue with a few of them.

For some of the datasets, field names are changed when running the data sets through the loop. For example, rather then feeding in [Region] we feed in [ï»¿Region]; similarly, rather then feeding in [PrimaryID] we feed in [ï»¿PrimaryID].

Does anyone happen to know why this may be happening, or how I can correct this? Appreciate any help.

Re: loop through to load all files from a folder and its subfolders?

Tyler_Waterfall — Tue, 25 Apr 2017 19:12:01 GMT

Are you loading from Excel files or text files?

If so, check the headers for those files to make sure there are no special characters or weirdness there. I've seen issues like this because of "invisible" character in the source files.

If you are not sure which files are causing the issue, you could add in your load * statement another two fields:

filename() as file_name,

recno() as file_record_number

And then select the data that looks bad and see what file_name is associated with it. (I guess the record_number is not useful in this case, but maybe later ).

Re: loop through to load all files from a folder and its subfolders?

ngrunoz — Mon, 05 Jun 2017 17:35:00 GMT

Hi Heric,

Is it possible to add an IF Condition of an other condition to say if Set Variable is =X then execute the Sub Else skip the Sub.