Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

qlik_aparna
New Contributor III

loop through folders and sub folders to load multiple excel files

I am using the below code to loop through folder

sub DoDir (Root)

   for each Ext in 'xslx'

      for each FoundFile in filelist (Root&'/*.' &Ext)

         FileList:

         LOAD

            '$(FoundFile)' as FullPathName,

            '$(Dir)' as FileDir,

            right('$(Dir)', 8) as FileDate,

            Subfield(Subfield('$(File)', '\', SubstringCount('$(File)', '\') + 1), '.', 1) as FileBase,

            FileSize('$(FoundFile)') as  Size        

         autogenerate 1;

      next FoundFile

   next Ext

   for each Dir in dirlist (Root&'/*' )

      call DoDir (Dir)

   next Dir

end sub

call DoDir ('lib://path')

I have created new data connection and named it as path.I tried it loading by giving relative path also.But no data is loaded and entire data model is empty.

1 Solution

Accepted Solutions
MVP
MVP

Re: loop through folders and sub folders to load multiple excel files

Try

MakeDate(

     mid(SubField(FilePath(),'/',6),1,4),                    // Year

     mid(SubField(FilePath(),'/',7),1,3),                    // Month

     mid(SubField(FileBaseName(),'_',-1),1,2)          // Day of Month

) as Date

edit:

Ah, Month is in 'MMM' format, hence

MakeDate(

     mid(SubField(FilePath(),'/',6),1,4),                    // Year

     Month(Date#(mid(SubField(FilePath(),'/',7),1,3),'MMM')),                    // Month

     mid(SubField(FileBaseName(),'_',-1),1,2)          // Day of Month

) as Date

9 Replies

Re: loop through folders and sub folders to load multiple excel files

aparna v wrote:

I am using the below code to loop through folder

sub DoDir (Root)

  for each Ext in 'xslx'

      for each FoundFile in filelist (Root&'/*.' &Ext)

        FileList:

        LOAD

            '$(FoundFile)' as FullPathName,

            '$(Dir)' as FileDir, // Where this variable stored in? Looks like stored this after the Next statement that means you are going to break the code. Can you reload after copy paste of below For each to before this?

            right('$(Dir)', 8) as FileDate,

            Subfield(Subfield('$(File)', '\', SubstringCount('$(File)', '\') + 1), '.', 1) as FileBase, // I haven't seen anything here related File

            FileSize('$(FoundFile)') as  Size      

        autogenerate 1;

      next FoundFile

  next Ext

  for each Dir in dirlist (Root&'/*' )

      call DoDir (Dir)

  next Dir

end sub

call DoDir ('lib://path')

Life is so rich, and we need to respect to the life !!!
qlik_aparna
New Contributor III

Re: loop through folders and sub folders to load multiple excel files

All the excel files  has no header ie dummy data, I just want to extract date(01 to 30) from excel filename and month from sub folder and year from main folder.

Re: loop through folders and sub folders to load multiple excel files

Then, Share real time script where you used with blur of image path ...

Life is so rich, and we need to respect to the life !!!
qlik_aparna
New Contributor III

Re: loop through folders and sub folders to load multiple excel files

This is the script i have used,

Sub DoDir(Root)

for each ext in 'xlsx'

for each File in FileList(Root&'\*.'&ext)

Table:

load

filepath() as path,

mid(SubField(FilePath(),'/',6),1,4) as year,

month(Date#(mid(SubField(FilePath(),'/',7),1,3),'MMM')) as month,

Date(Date#(mid(SubField(FileBaseName(),'_',-1),1,2),'DD'),'DD') as date,

MakeDate(Date(mid(SubField(FilePath(),'/',6),1,4),'YYYY'),

Date(Date#(mid(SubField(FilePath(),'/',7),1,3),'MMM'),'MM'),

Date(Date#(mid(SubField(FileBaseName(),'_',-1),1,2),'DD'),'DD')) as Date

from ['$(File)'](ooxml,no labels);

Next File

Next ext

for Each Dir in DirList(Root&'\*')

call DoDir(Dir)

next Dir

end sub

call DoDir('lib://pathto/Funds')

Re: loop through folders and sub folders to load multiple excel files

The script looks feel good to me. Not sure, Where you made wrong if this is the full script involved in your machine.

Life is so rich, and we need to respect to the life !!!
qlik_aparna
New Contributor III

Re: loop through folders and sub folders to load multiple excel files

This is the full script everything works fine except date. Is there any other option of using makedate function in another temp table and resident this table. Will that fetch date for all files?

Re: loop through folders and sub folders to load multiple excel files

can you try this? confirm me whether date / Date ??

Date(Date#(MakeDate(mid(SubField(FilePath(),'/',6),1,4),month(Date#(mid(SubField(FilePath(),'/',7),1,3),'MMM')),

Date(Date#(mid(SubField(FileBaseName(),'_',-1),1,2),'DD'),'DD')),'YYYY-MMM-DD'),'DD/MM/YYYY') as Date

Re: makedate not working

Life is so rich, and we need to respect to the life !!!
MVP
MVP

Re: loop through folders and sub folders to load multiple excel files

Try

MakeDate(

     mid(SubField(FilePath(),'/',6),1,4),                    // Year

     mid(SubField(FilePath(),'/',7),1,3),                    // Month

     mid(SubField(FileBaseName(),'_',-1),1,2)          // Day of Month

) as Date

edit:

Ah, Month is in 'MMM' format, hence

MakeDate(

     mid(SubField(FilePath(),'/',6),1,4),                    // Year

     Month(Date#(mid(SubField(FilePath(),'/',7),1,3),'MMM')),                    // Month

     mid(SubField(FileBaseName(),'_',-1),1,2)          // Day of Month

) as Date

qlik_aparna
New Contributor III

Re: loop through folders and sub folders to load multiple excel files

Thanks a lot Stefan.

Community Browser