Qlik Community

Ask a Question

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Qlik Highlights 2020 Giveaway! Watch, reply and have a chance to win a $200 Amazon Gift Card! Watch Video
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

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
Master II
Master II

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

Not applicable

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

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

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

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

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

can't we connect without ODBC/OLEDB  ?

Thanks and Regards

Satti