Howto: Load all excel files in a directory and use part of filename as inserted value

[#1]

I would like to load all excel files in a directory (growing by one file every day) - and use part of the file name (date)

as a inserted value in the table - Below is a manual example of a single file - that I today repeat for every file....

...must be a smarter way of just loop thru the files and dynamically create the LoadDate ? 🙂

LOAD

  '2015/12/20' As LoadDate,

    Number,

    Number1,

    Priority,

    State

FROM [lib://somedata/daily_2015-12-20.xls]

(biff, embedded labels, table is [Page 1$]);

[m_woolf]

LOAD

  left(subfield(FileName(),'_',2),10) As LoadDate,

    Number,

    Number1,

    Priority,

    State

FROM [lib://somedata/*.xls] 

(biff, embedded labels, table is [Page 1$]);

[reddy-s]

Hi Richard,

Try this :

sub ScanFolder(Root)

          for each FileExtension in 'qvw','mp3','ape','flac','ogg'

                    for each FoundFile in filelist( Root & '\*.' & FileExtension)

                              FileList:

                              Load

                                        '$(FoundFile)'                     as [FilenameWithPath],

                                        '$(FileExtension)'           as [Extension]

                              Autogenerate(1);

                    next FoundFile

          next FileExtension

          for each SubDirectory in dirlist( Root & '\*' )

                    call ScanFolder(SubDirectory)

          next SubDirectory

end sub

Call ScanFolder('.') ;

Thanks and Regards,

Sangram Reddy.