3 Replies Latest reply: Dec 20, 2017 7:49 AM by Marcus Sommer RSS

    Loading dynamic sheet names

    aparna v

      Hi Community,

       

      I have below script which works fine to load excel files from folders and subfolders. But now i want to load sheets of all excel files which are dynamic. Any help on this?

       

      Sub DoDir(Root)

      for each ext in 'xlsx'

      for each File in FileList(Root&'\*.'&ext)

      Table:

      load

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

        • Re: Loading dynamic sheet names
          aparna v

          I have checked that script, but here I'am not taking excel files from sql tables.

          Is there any other way to do?

            • Re: Loading dynamic sheet names
              Marcus Sommer

              If you know the sheetnames you could just add another loop which runs through this list. Thinkable are also external macro-batches which create a table with those sheetnames and which could be used within a loop. If this isn't very practically you will the need the sql-approach from the above link which is not very complex to apply - maybe in this way:

               

              Sub DoDir(Root)

              for each ext in 'xlsx'

                   for each File in FileList(Root&'\*.'&ext)

                        ODBC CONNECT32 TO [Excel Files;DBQ=$(File)];
                        tables: SQLtables;
              DISCONNECT;

                        FOR i = 0 to NoOfRows('tables')-1

                             LET sheetName = purgeChar(purgeChar(peek('TABLE_NAME', i, 'tables'), chr(39)), chr(36));
                             Table:
              load from ['$(File)'] (ooxml, no labels, table is [$(sheetName)]);

                        next

                   Next File

              Next ext


              for Each Dir in DirList(Root&'\*')

                   call DoDir(Dir)

              next Dir

              end sub


              call DoDir('lib://pathto/Funds')

               

              - Marcus