topic Load multiple Excel-Files with multiple Sheets in QlikView

Load multiple Excel-Files with multiple Sheets

Tue, 27 Oct 2015 13:17:14 GMT

Hello Community,

i searched for this context and got many results with the "For" Loop. The problem is that i cant adapt that because of my lack in programming skills. So what i wanted to ask is the possibility of Loading Data with the same "Header" from different Sheets/Files.

Example:

i have 2 Excel Sheets with same name, except the numbers at the end:

- Data_02.xls

- Data_33.xls

They all got the same header, so right now iam doing 2 times:

LOAD [ColumnA1],

[ColumnA2],

...

FROM

(ooxml, embedded labels, table is [detail]);

LOAD [ColumnA1],

[ColumnA2],

...

FROM

(ooxml, embedded labels, table is [detail]);

Is there a possibility to simply put the same "Load-statements" on one "FROM-Statement" ?

for excample:

FROM

(ooxml, embedded labels, table is [detail]);

Thanks in advance !

Re: Load multiple Excel-Files with multiple Sheets

m_woolf — Tue, 27 Oct 2015 17:05:42 GMT

try:

Data_*.xlsx

Re: Load multiple Excel-Files with multiple Sheets

miguelbraga — Tue, 27 Oct 2015 17:45:38 GMT

Try this:

Example:

Load * From [C:\...\Data_*.xlsx] (ooxml, embedded labels, table is [detail]);

This should do the trick

Regards,

Re: Load multiple Excel-Files with multiple Sheets

Oleg_Troyansky — Tue, 27 Oct 2015 18:01:44 GMT

Loading from multiple excel file is easier - you can simply use a wildcard, like others suggested.

Loading the same data format from multiple worksheets is a bit trickier. If the number of worksheets is large or unknown, the only way is to connect to your Excel spreadsheet via ODBC (yes, there is a special ODBC driver for Excel!), get the list of the existing tables using the ODBC command SQLTABLES and then process the multiple tables one by one.

cheers,

Oleg Troyansky

Check out my new book QlikView Your Business: An expert guide to Business Discovery with QlikView and Qlik Sense