Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

qlik_aparnav
Contributor II

Loading dynamic sheet names

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

3 Replies

Re: Loading dynamic sheet names

qlik_aparnav
Contributor II

Re: Loading dynamic sheet names

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

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

Community Browser