loop through folders and sub folders to load multiple excel files

[qlik_aparna]

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)', 😎 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.

[Anil_Babu_Samineni]

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)', 😎 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')

[qlik_aparna]

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.

[Anil_Babu_Samineni]

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

[qlik_aparna]

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')

[Anil_Babu_Samineni]

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

[qlik_aparna]

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?

[Anil_Babu_Samineni]

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

[swuehl · Accepted solution]

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]

Thanks a lot Stefan.