topic Loading from multiple Excel files and multiple sheets in QlikView

Loading from multiple Excel files and multiple sheets

Tue, 29 May 2012 12:43:26 GMT

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.

Loading from multiple Excel files and multiple sheets

Siva_Sankar — Tue, 29 May 2012 13:01:39 GMT

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

Loading from multiple Excel files and multiple sheets

Wed, 30 May 2012 05:01:33 GMT

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.

Loading from multiple Excel files and multiple sheets

Wed, 30 May 2012 08:48:10 GMT

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

Re: Loading from multiple Excel files and multiple sheets

jonathandienst — Wed, 30 May 2012 09:38:22 GMT

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

Hope that helps

Jonathan

Re: Loading from multiple Excel files and multiple sheets

jonathandienst — Wed, 30 May 2012 09:41:39 GMT

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

Jonathan

Re: Loading from multiple Excel files and multiple sheets

Fri, 12 Oct 2012 09:36:53 GMT

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

Re: Loading from multiple Excel files and multiple sheets

Fri, 14 Jun 2013 07:22:08 GMT

can't we connect without ODBC/OLEDB ?

Thanks and Regards

Satti