loop through set of files issue

[alec1982]

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?

[Gysbert_Wassenaar]

  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

[alec1982]

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%2...

http://www.lme.com/~/media/Files/Warehousing/Queue%20information/2016/warehouse%20company%20stocks%2...

[alec1982]

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?

[HirisH_V7]

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

[HirisH_V7]

Hi,

look into this,

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

search inside folders for file load

HTH,

Hirish

[alec1982]

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..

[tamilarasu]

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;

[Gysbert_Wassenaar]

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

[alec1982]

hi Tamil.

this solution didnt work.

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

Any idea?