Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

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.

Tags (2)
1 Solution

Accepted Solutions
MVP
MVP

Re: Loading from multiple Excel files and multiple sheets

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
7 Replies
Siva_Sankar
Honored Contributor

Loading from multiple Excel files and multiple sheets

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

Not applicable

Loading from multiple Excel files and multiple sheets

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

Loading from multiple Excel files and multiple sheets

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

MVP
MVP

Re: Loading from multiple Excel files and multiple sheets

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
MVP
MVP

Re: Loading from multiple Excel files and multiple sheets

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

Re: Loading from multiple Excel files and multiple sheets

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 OLEDBSmiley Frustratedystem database="";Jet OLEDB:Registry Path="";Jet OLEDBSmiley Very Happyatabase Password="";Jet OLEDB:Engine Type=35;Jet OLEDBSmiley Very Happyatabase 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 OLEDBSmiley Very Happyon't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDBSmiley FrustratedFP=False];

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

Not applicable

Re: Loading from multiple Excel files and multiple sheets

can't we connect without ODBC/OLEDB  ?

Thanks and Regards

Satti 

Community Browser