2 Replies Latest reply: Mar 16, 2018 10:49 AM by Sasidhar Parupudi RSS

    Qlik Sense load excel file set the variable of the worksheet name

    kong ch

      hi all,

       

      the following code is looping excel files in a folder and sub folder,

      however, it is difficult to set the variable of the worksheet name.

      (for each vSheet in 'a','b,'c*','d','e','f'.....) because the worksheet name is dynamic

       

       

      other than legacy mode, and odbc connect (not work in the qliksense) <<< I try several days

      any idea of this, thank you.

       

       

      code:

      -------------------------------------------------------------------------

      sub DoDir (Root)

      For Each Ext in 'xls'

      For Each File in filelist (Root&'\*.' &Ext)

       

      for each vSheet in 'a','b,'c*','d','e','f'.....

       

      Table:

       

      LOAD

      *,

       

      FROM $(File)(biff, embedded labels, header is 3 lines,table is $([vSheet]$)));

       

      next vSheet

       

       

      Next File

      Next Ext

       

      For Each Dir in dirlist (xxxx)

      Call DoDir (Dir)

      Next Dir

      End Sub

      Call DoDir (xxxxx)