Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
Not applicable

import all excel sheets

Hi,

I'd like to import an excel file which got many excel sheets. All excel sheets got the same data structur. But i don't know how many sheets are in the file. I think I need a command that imports all sheets.

Did anybody know a command like that?

Many Thanks.

Hernandez

1 Solution

Accepted Solutions
Not applicable

import all excel sheets

hello hernandez

maybe this will help you.

http://community.qlik.com/blogs/qlikviewblogs/archive/2008/09/29/loading-multiple-excel-sheets.aspx

View solution in original post

8 Replies
Not applicable

import all excel sheets

hello hernandez

maybe this will help you.

http://community.qlik.com/blogs/qlikviewblogs/archive/2008/09/29/loading-multiple-excel-sheets.aspx

View solution in original post

Not applicable

import all excel sheets

Hi muncho,

that did not work, because with the Microsoft.Jet.OLEDB.4.0 driver I have to insert an username and passwort. But I need it for XLS.

Highlighted
deepakk
Valued Contributor III

import all excel sheets

hi, try out this application.

Not applicable

import all excel sheets

Now it works, thanks!

Not applicable

import all excel sheets

Now I've got another problem. The script only checks, how many sheets the first excel file got, but in the folder are a lot of excel files. Maybe the first file only got tewnty sheets and the second file got thirty sheet. Now only the first 20 charts are included in qlikview.

How could I edit this script for more files?

Many Thanks!

ODBC CONNECT TO [Excel-Dateien;DBQ=C:\Page\nm.xls];
tables:
SQLtables;

FOR i = 0 to NoOfRows('tables')-1
LET sheetName = purgeChar(peek('TABLE_NAME', i, 'tables'), chr(39));
UniqueUser:
LOAD @1 as Uhrzeit,
@2 as UU,
@3 as Datum,
Date(@3) as %KeyDatum,
FROM C:\Page\* (biff, no labels, table is [$(sheetName)]);
NEXT

prieper
Honored Contributor II

import all excel sheets

You need to embed this script into another loops, which goes through all files. Filename then must be handed over into a variable. So script might be somehow:

FOR EACH vFile IN FILELIST ('YourDirectory\*.xls')
ODBC CONNECT TO [Excel-Dateien;DBQ=$(vFile)];
....
NEXT vFile[/coce]
HTH
Peter</body>
Not applicable

import all excel sheets

Thanks for your reply. I'll try it.

Not applicable

import all excel sheets

That works!

Many thanks!