Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

arul2608
New Contributor

Dynamically Loading Multiple Excel files and sheet in subfolder

Hi folks.

I have the following requiremnts, Please help me resolve this issue.

We have multiple excel files in different folders, I would like to load all the files and sheets in single load dynamically without the ODBC connection

I m working on the4 below mentioned code:

but the code loads only the path, I would like to retrive the data from it. So that I can use it in multiple application

Set vConcatenate = ;

FileList:

LOAD

'' AS SourceFile

AUTOGENERATE 0;

sub ScanFolder(Root)

          for each FileExtension in 'xlsx'

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

                   

                              LOAD '$(FoundFile)' as SourceFile

       AUTOGENERATE 1;

                            

                              Set vConcatenate = Concatenate;

                             

                   

                    next FoundFile

          next FileExtension

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

                    call ScanFolder(SubDirectory)

          next SubDirectory

         

End Sub

CALL ScanFolder ('C:\Funds\Fund_1');

Tags (1)
3 Replies

Re: Dynamically Loading Multiple Excel files and sheet in subfolder

without the ODBC connection

You need the odbc connection to get a list of the sheets in an excel file. If you have an alternative way to get the list of sheets for each excel file then you can use that instead of the odbc SQLTABLES function. Otherwise you're stuck with ODBC.


talk is cheap, supply exceeds demand
rodolfoag
New Contributor II

Re: Dynamically Loading Multiple Excel files and sheet in subfolder

Hi Arulmozhi Ayyappan,

you need to iterate the file table and get the data.

After your script try something like this.

for i=1 to len(SourceFile)

     vPath = peek('SourceFile', i, 'SourceFileTable');

     load *

     from $(vPath);

next i;

maybe if you are using qlik sense you need to lib:// you will need to make some substringcount() and/or subfiled() to get the path after the Root.

hope it helps.

RS

arul2608
New Contributor

Re: Dynamically Loading Multiple Excel files and sheet in subfolder

But the ODBC connection doesn't seems to be work in the qlik sense. Thats the reason I m trying withou ODBC

Community Browser