Dynamically Loading Multiple Excel files and sheet in subfolder

[arul2608]

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

[Gysbert_Wassenaar]

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.

[Anonymous]

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]

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