2 Replies Latest reply: Jan 9, 2016 6:38 AM by Sangram Reddy RSS

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

    Rickard Andersson

      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$]);

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

          LOAD

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

              Number,

              Number1,

              Priority,

              State

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

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

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

            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.