topic Re: loop through set of files issue in QlikView

loop through set of files issue

alec1982 — Wed, 25 Nov 2020 16:16:04 GMT

hi all,

I have set of excel files. each file name has month name and year.

The files are as follow

Data_March_2014

Data_April_2014

Data_May_2014

...

Data_April_2016

Every month a new file gets generated with the same naming structure.

I need to load all files into one table and also extract the month and year from each file name to add them as columns.

Anyone has a good logic I can use?

Re: loop through set of files issue

Gysbert_Wassenaar — Sat, 07 May 2016 07:13:26 GMT

For each vFileName in Filelist ('C:\Path\*.xlsx')

Load *,

'$(vFileName)' as FileName,

SubField('$(vFileName)','_',-2) as Month,

SubField('$(vFileName)','_',-1) as Year

From [$(vFileName)] (ooxml, embedded label, table is Sheet1);

Next vFileName

Re: loop through set of files issue

alec1982 — Sat, 07 May 2016 07:58:28 GMT

The problem is the files are located on web and they are in sub folders.. here are some examples

http://www.lme.com/~/media/Files/Warehousing/Queue%20information/2015/warehouse%20company%20stocks%20and%20queue%20data%20April%20$2015.xls

http://www.lme.com/~/media/Files/Warehousing/Queue%20information/2016/warehouse%20company%20stocks%20and%20queue%20data%20March%20$2016.xls

Re: loop through set of files issue

alec1982 — Sat, 07 May 2016 08:26:21 GMT

thanks for the reply.. the problem is the files are located in sub directories. each year has its own folder..

any way I can work through that?

Re: loop through set of files issue

HirisH_V7 — Sat, 07 May 2016 08:29:26 GMT

Hi,

May be another way like this,

Picking all the files in a folder,

Temp:
LOAD Sno,
     Name,
     SubField(FileName(),'_',-2) as Month,
        Mid(SubField(FileName(),'_',-1),1,4) as Year
FROM
[..\Files in the File List-215959\*.xlsx]
(ooxml, embedded labels, table is Sheet1);

Check this,

Hope this helps,

PFA,

Hirish

Re: loop through set of files issue

HirisH_V7 — Sat, 07 May 2016 08:31:12 GMT

Hi,

look into this,

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

search inside folders for file load

HTH,

Hirish

Re: loop through set of files issue

alec1982 — Sat, 07 May 2016 09:03:47 GMT

thank you for the help..

the issue is the files are located on web so the url to access them starts with www and then the url has sub directories in it..

I will attach samples here as I couldn't add the URL..

Re: loop through set of files issue

tamilarasu — Sat, 07 May 2016 12:35:43 GMT

Alec,

You can try below script. Modify the field names according to your files.

Data:
Load '' as Temp AutoGenerate 0;

sub ScanFolder(Root) 
          for each FileExtension in 'xls' 
                    for each FoundFile in filelist( Root & '\*.' & FileExtension) 
                             Concatenate (Data) 
                              LOAD  SubField(FileName(),'_',-2) as Month,
                                    Left(SubField(FileName(),'_',-1),4) as Year,
                                    Field1,                        
                                    Field2
                              FROM [$(FoundFile)]
                             (biff, embedded labels, table is Sheet1$);
                    next FoundFile 
          next FileExtension 
          for each SubDirectory in dirlist( Root & '\*' ) 
                    call ScanFolder(SubDirectory) 
          next SubDirectory 
end sub 

Call ScanFolder('http://www.lme.com/~/media/Files/Warehousing/Queue%20information')

Drop Field Temp;

Re: loop through set of files issue

Gysbert_Wassenaar — Sun, 08 May 2016 16:50:51 GMT

Afaik FileList doesn't work on websites. So you need in advance to create a list of the files you want to retrieve.

Re: loop through set of files issue

alec1982 — Mon, 09 May 2016 17:50:34 GMT

hi Tamil.

this solution didnt work.

the variables return null values and therefore it doesnt find any file..

Any idea?

Re: loop through set of files issue

MarcoWedel — Wed, 11 May 2016 22:02:38 GMT

sample files

Re: loop through set of files issue

MarcoWedel — Wed, 11 May 2016 22:29:18 GMT

Hi,

if you could load a list of file-urls from some source, then one solution might be:

SET LongMonthNames = 'January;February;March;April;May;June;July;August;September;October;November;December';

tabFiles:
LOAD * INLINE [
    file
    http://community.qlik.com/servlet/JiveServlet/download/1037406-225490/Data_March_2014.xlsx
    http://community.qlik.com/servlet/JiveServlet/download/1037406-225489/Data_April_2014.xlsx
    http://community.qlik.com/servlet/JiveServlet/download/1037406-225512/Data_May_2014.xlsx
];

FOR vCounter = 1 to FieldValueCount('file')
  LET vFile = FieldValue('file',vCounter);
   
  table1:
  LOAD *,
      Month(MonthYear) as Month,
      Year(MonthYear) as Year;
  LOAD *,
      MonthName(Date#(Mid(FileBaseName(),Index(FileBaseName(),'_',-2)+1),'MMMM_YYYY')) as MonthYear
  FROM [$(vFile)] (ooxml, embedded labels, table is Tabelle1);
NEXT vCounter
   
DROP Table tabFiles;

hope this helps

regards

Marco