Loading from multiple Excel files and multiple sheets

[#1]

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.

[Siva_Sankar]

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

[#3]

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.

[#4]

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 · Accepted solution]

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

[jonathandienst]

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

Jonathan

[#7]

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

[#8]

can't we connect without ODBC/OLEDB  ?

Thanks and Regards

Satti