Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

leni_balakrishn
Contributor III

To extract folder and subfolder names

Hi,

I have requirement to extract the folder name and its subfolder name in Qlikview script.

The folder structure will be like below

Main Folder

     SubFolder1 (the folder name will be date format. For eg., 20121001)

          InnerSubFolder (the folder name will be in time format (24-hrs) appended by the name. For eg., 210125 - Invoice (HH:MMSmiley FrustratedS))

          ...........

     SubFolder2

     .......

So in this till Main Folder the path is constant. I need to fetch the SubFolder1, SubFolder2,.... and InnerSubFolder,... paths dynamically in Qlikview script.

Under SubFolder we have many InnerSubFolders in that I need to take a particular folder based on the appended text as in above example I need to check for Invoice folder. Inside the 210125-Invoice folder I have text document and that text document will be the source file.

An Example:

To get the text document placed at *Invoice folder

Main Folder = c:\Sample\test\

SubFolder = 20120928, 20120929,20120930, 20121001

InnerFolder = 102345-Order,034546-Inventory,210125-Invoice

In this need to loop through all the SubFolders and inside those SubFolders need to check for the folders Invoice it can be prefixed with any time and need to use the text file inside those Invoice folders.

Thanks in Advance.

Thanks,

Leni Balakrishnan

Tags (1)
1 Solution

Accepted Solutions
shekharnil
New Contributor III

Re: To extract folder and subfolder names

This should do :-

set vBaseDirName = 'D:\Sample\Test\*';


FOR Each vSubFolder in DirList(vBaseDirName)
   SET vSFolder = $(vSubFolder)\*;

FOR Each vInnerFolder in DirList(vSFolder)

if WildMatch(vInnerFolder,'*Invoice')>0 then
tb:
LOAD
    *
   FROM [$(vInnerFolder)\file.txt]
(txt, utf8, no labels, delimiter is '\t', msq);

ENDIF
NEXT vInnerFolder
NEXT vSubFolder

13 Replies
MVP
MVP

Re: To extract folder and subfolder names

Leni

I dont have a complete script for you,. but you can solve this with some nested "For Each .. in DirList()" loops:

     For Each vSubfolder in DirList('c:\Sample\test')

          ...

          <any logic using the subfolder name in vSubfolder can be performed here>         

          ...

          For Each vInnerSubfolder in DirList(vSubFolder)

               ...

               <any logic using the inner subfolder name in vInnerSubfolder can be performed here>     

                ...

          Next

          ...

          <more logic using the subfolder name in vSubfolder can be performed here>        

          ...

     Next  

Hope that helps

Jonathan

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
leni_balakrishn
Contributor III

Re: To extract folder and subfolder names

Thanks for your help..

Let me try ur logic and let you know

Thanks,

Leni

shekharnil
New Contributor III

Re: To extract folder and subfolder names

Use the following  in your load :-

  LOAD

MakeDate(mid(SubField(filepath( ),'\',4),1,4), mid(SubField(filepath( ),'\',4),5,2), mid(SubField(filepath( ),'\',4),7,2)) as DATE,
  Maketime(mid(SubField(filepath( ),'\',5),1,2), mid(SubField(filepath( ),'\',5),3,2), mid(SubField(filepath( ),'\',5),5,2)) as Time,

Note:-  for  SubField(filepath( ),'\',4)

  the value depends on the constant path

e.g:-  c:\Sample\test\ 20120928\ 102345-Order.txt

use         SubField(filepath( ),'\',4)  for date   

               SubField(filepath( ),'\',5) for time

change in case directory changes

leni_balakrishn
Contributor III

Re: To extract folder and subfolder names

Hi Shekharnil,

Thanks for your reply.

But my requirement is to get the subFolders and the innerfolders name to frame the whole path.

Thanks,

Leni

leni_balakrishn
Contributor III

Re: To extract folder and subfolder names

Hi Jonathan,

I have tried the loop structure you have said above

set vBaseDirName = 'D:\Sample\Test\';

FOR Each vSubFolder in DirList($(vBaseDirName))

  FOR Each vInnerFolder in DirList(vSubFolder)

                         if(WildMatch($(vInnerFolder),'*Invoice') >0)

                              Tb1:

                                        LOAD

                         *

                                        FROM

                                        [$(vBaseDirName)\$(vSubFolder)\$(vInnerFolder)\file1.txt]

                                        (txt, utf8, no labels, delimiter is '\t', msq);

                    ENDIF

          NEXT

NEXT

When I tried to load this script into Qlikview , The value of vSubFolder, vInnerFolder are same as vBaseDirName.

I am not sure why the value is coming like tat.

Please guide me in this.

Thanks,

Leni

MVP
MVP

Re: To extract folder and subfolder names

Leni

If you use a $() expansion, you need to also include quotes when used as a parameter. Or just remove the unneeded variable expansions:

FOR Each vSubFolder in DirList(vBaseDirName)

          TRACE vSubFolder = $(vSubFolder);

          FOR Each vInnerFolder in DirList(vSubFolder)

                    TRACE vInnerFolder = $(vInnerFolder);

                     if(WildMatch(vInnerFolder,'*Invoice') >0)

                                Tb1:

                                        LOAD

                     *

                                        FROM [$(vInnerFolder)\file1.txt]

                                        (txt, utf8, no labels, delimiter is '\t', msq);

                    ENDIF

            NEXT

NEXT

Also, I think vInnerFolder contains the complete folder path, so I have amended the FROM clause. The TRACE statements will help with debugging and can be deleted once the script is working

Regards

Jonathan

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

Re: To extract folder and subfolder names

Instead of coding up your own dirlist loop, you may want to consider using the Qlikview Components script library http://qlikviewcomponents.org. Using the Qvc, your complete code would look like this:

$(Include=..\qvc_root\qvc\qvc_runtime\qvc.qvs);

SUB LoadInvoice (file)

          IF '$(file)' LIKE '*\*-Invoice\*' THEN

                    LOAD *      // Your load statement

                    FROM

                    '$(file)'

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

          ENDIF

END SUB

CALL Qvc.ListFiles('c:\temp\MainFolder', '*', -1, 'LoadInvoice');

-Rob

http://robwunderlich.com

shekharnil
New Contributor III

Re: To extract folder and subfolder names

This should do :-

set vBaseDirName = 'D:\Sample\Test\*';


FOR Each vSubFolder in DirList(vBaseDirName)
   SET vSFolder = $(vSubFolder)\*;

FOR Each vInnerFolder in DirList(vSFolder)

if WildMatch(vInnerFolder,'*Invoice')>0 then
tb:
LOAD
    *
   FROM [$(vInnerFolder)\file.txt]
(txt, utf8, no labels, delimiter is '\t', msq);

ENDIF
NEXT vInnerFolder
NEXT vSubFolder

leni_balakrishn
Contributor III

Re: To extract folder and subfolder names

I am getting the same vBaseDirName value in vSubFolder and vInnerFolder. I have done as you instructed only.

Community Browser