Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

stevelord
Not applicable

Load files from subfolders in one load script?

Hi All, there was a legacy habit of saving new files into folders named for the date they were received and a current desire to see if we can pull all of those files into qlikview in an efficient way.

So for fun, I tried just putting an * for a folder name, but I didn't expect that to work and sure enough it didn't work.  I have some hope there's maybe a 2-step and a variable type approach to move through the folders.  Hoping to avoid making a separate piece of load script for each folder or having to move all the files to a single folder.

LOAD MemberID

FROM

(txt, codepage is 1252, embedded labels, delimiter is '|', msq);

Thanks in advance for any advise!

(99% of the data exists in the database, but this would be used to either pickup the piece missing from the database or to audit the database data. Also the files were generated by someone else's automated process, so we're hopeful their internal structures are all consistent in terms of delimiters and field names etc.  We'd be able to tell which errored by the gaps in the dates though.)

1 Solution

Accepted Solutions
pho3nix90
Not applicable

Re: Load files from subfolders in one load script?

Try this

SET vDataPath = 'C:\Activity\Client\';

FOR Each vDir in DirList('$(vDataPath)*')

  LOAD MemberID

  FROM

  [$(vDir)\ClientActivity*.txt]

  (txt, codepage is 1252, embedded labels, delimiter is '|', msq);

NEXT

5 Replies
karmariv82
Not applicable

Re: Load files from subfolders in one load script?

Hi Steve,

Maybe this thread can be useful Loop through root directory (including subfolders) and list filenames of *.csv

Regards,

-- Karla

qlikview979
Not applicable

Re: Load files from subfolders in one load script?

Hi

please try this, may be help full for you.

SUB Scanfolder(Root)

For each FileExtension in 'xlsx'

FOR Each FoundFile in FileList(Root &'\*.'& FileExtension)

T1:

LOAD ID,

    NAMES,

    SAL,

    YEARS

   

  FROM

[$(FoundFile)]

(ooxml, embedded labels, table is Sheet1);

  Next FoundFile 

NEXT FileExtension

For Each SubDirectory in DirList (Root & '\*' ) 

Call ScanFolder(SubDirectory) 

Next SubDirectory 

End Sub 

Call ScanFolder('D:\Personal\LOADING DATA FROM MULTIPLE FOLDERS') ;

Note:-

Instead  of  "D:\Personal\LOADING DATA FROM MULTIPLE FOLDERS"    Give your source file Folder Name.













pho3nix90
Not applicable

Re: Load files from subfolders in one load script?

Try this

SET vDataPath = 'C:\Activity\Client\';

FOR Each vDir in DirList('$(vDataPath)*')

  LOAD MemberID

  FROM

  [$(vDir)\ClientActivity*.txt]

  (txt, codepage is 1252, embedded labels, delimiter is '|', msq);

NEXT

stevelord
Not applicable

Re: Load files from subfolders in one load script?

Ah, this didn't work for me initially, but now I see the other half of your script when I login.  (Maybe I forgot to hit 'more' on the email side I was reading.)  The 'for each next' loop someone else suggested worked off the bat, so giving that one correct answer credit and calling it a day.  I will give this one helpful credit since it probably works and I just lack time for further testing right now.

stevelord
Not applicable

Re: Load files from subfolders in one load script?

Thanks, this did the job exactly as you had it written in the post (just changing to the actual fields and filepaths on my side).

Thanks everyone for your replies.  Sorry I can only give the one correct answer credit, but I gave helpful credits to the other answers.