Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
IMPORTANT security patches for GeoAnalytics Server available to download: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Loading from multiple Excel files and multiple sheets

Hi,

I need to load data from multiple Excel files which each contain multiple sheets. I need to filter files by name (eg. *_sys.xlsm) and sheets by names (eg. 'start with number and atleast 4 characters long')

Is it possible to do this with Qlikview load scripts? As far as I can tell, looping through sheet names requires ODBC/OLE DB connection, and I'm not sure if those can be created in a loop (create the connection to each file in a loop).

Also I'm not sure if SQLTables selection works correctly with ODBC CONNECT32 ... Excel files. It always seems to be empty.

1 Solution

Accepted Solutions
jonathandienst
Partner
Partner

Hi

You can indeed use ODBC CONNECT 32 to load the table names using SQLTABLES, but I don't think you will be able to wildcard load, rather loop over the files using For Each vFile in FileList(vPath).

Something like:

ForEach vFile in FileList('$(vPath)')

     ODBC CONNECT32 To [Excel Files;DBQ=$(vFile)];

     Sheets:

     SQLTABLES;

     DISCONNECT;

     For i = 0 To NoOfRow('Sheets')

          Let zSheet = Peek('TABLE_NAME, i, 'Sheets');

          ... do something with the file and sheet name, eg

          LOAD ....

          From [$(vFile)]

          (ooxml, no labels, table is [$(zSheet)]);

     Next

Next

Hope that helps

Jonathan

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

View solution in original post

7 Replies
Siva_Sankar
Master II
Master II

Hi, Check out the link  http://community.qlik.com/message/220535

Not applicable
Author

Hi,

That thread does indeed show how to load from multiple files, but in that scenario there is only one sheet with static name from which the data is loaded. In my scenario I have also multiple sheets, where the name of each is not known until runtime.

Not applicable
Author

Hello

Here is an example how to select sheet name from which to load

LOAD

     Data1,

     Data2,

     DataX,

FROM

C:\YourFileName.xlsm

(ooxml, embedded labels, table is YourTableName);

jonathandienst
Partner
Partner

Hi

You can indeed use ODBC CONNECT 32 to load the table names using SQLTABLES, but I don't think you will be able to wildcard load, rather loop over the files using For Each vFile in FileList(vPath).

Something like:

ForEach vFile in FileList('$(vPath)')

     ODBC CONNECT32 To [Excel Files;DBQ=$(vFile)];

     Sheets:

     SQLTABLES;

     DISCONNECT;

     For i = 0 To NoOfRow('Sheets')

          Let zSheet = Peek('TABLE_NAME, i, 'Sheets');

          ... do something with the file and sheet name, eg

          LOAD ....

          From [$(vFile)]

          (ooxml, no labels, table is [$(zSheet)]);

     Next

Next

Hope that helps

Jonathan

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
jonathandienst
Partner
Partner

Oh, I forgot that you will need to Drop the Sheets table at the end of each loop, otherwise they will concatenate.

Jonathan

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Not applicable
Author

Works like a charm

In our case we don't have any ODBC datasources on our servers. You can use OLEDB with a tric:

OLEDB CONNECT32 TO [Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;Data Source=$(vFile);Mode=Share Deny None;Extended Properties="Excel 8.0;HDR=Yes;IMEX=1";Jet OLEDB:System database="";Jet OLEDB:Registry Path="";Jet OLEDB:Database Password="";Jet OLEDB:Engine Type=35;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False];

The extended properties property lets you connect to Excel files with the JET provider

Not applicable
Author

can't we connect without ODBC/OLEDB  ?

Thanks and Regards

Satti