Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2022, LIVE in Denver CO., May 16-19, 2022. REGISTER NOW TO RECEIVE EARLY BIRD PRICING
cancel
Showing results for 
Search instead for 
Did you mean: 
lalitkgehlot89
Creator II
Creator II

Need help @ data Extraction.

Hi,

I have data in  excel  with n number of sheets and need to automatically load data from all the sheets (number of sheets can be increase or decrease ).

Plz help me out.

Thanks,

Lalit Kumar

5 Replies
tresesco
MVP
MVP

This has been discussed many times here in this community. Please check:

Re: Loading multiple sheets from excel

Load Multiple excel sheets using For loop

Or you can simply search here for more such links.

Pulkit_Thukral
Partner
Partner

//Create Odbc driver first

Set ErrorMode=0;

FOR EACH file in FileList('FullPath of excel file');   // Loops each excel file in the given Folder

//In order to get the file information from SQLtables command making use of the ODBC connection format

ODBC CONNECT TO [ODBC_CONNECTER_NAME;DBQ=$(file)];

SheetNames:

SQLtables;  // Loads all sheet names in the Excel file.

DISCONNECT;

FOR index = 0 to NoOfRows('SheetNames')-1  // Loops for each sheet in the Excel file.

LET sheetName = PurgeChar(PurgeChar(Peek('TABLE_NAME', index, 'SheetNames'), Chr(39)), Chr(36));

Table1:

Load *,

From $(file)(ooxml, embedded labels, table is [$(sheetName)]);

NEXT index

DROP TABLE SheetNames;

NEXT

qlikview979
Specialist
Specialist

Hi,

when all  sheets having same fields

Try this  (in for each condition instead of 'Sheet1'.......'Sheet4' Write your sheet names.)

for each Vpeek in  'Sheet1','Sheet2','Sheet3','Sheet4';

T1:

LOAD ID,

    NAME,

    NUMBER

FROM

(ooxml, embedded labels, table is $(Vpeek));

NEXT Vpeek;

Note:- write the "*" instead of Excel name.

kaveri_bhar123
Contributor II
Contributor II

Hi Lalit,

Please find the below script for loading data from multiple sheets:

 

 

FOR i = 0 to NoOfRows('tables')-1
LET sheetName = purgeChar(peek('TABLE_NAME', i, 'tables'), chr(39));

       IF wildmatch('$(sheetName)', 'Sales*') THEN  // When sheetNames that begin "Sales"
              Sales:   
              LOAD *,
'$(sheetName)'
as Sheet 

              FROM workbook.xls (biff, embedded labels, table is [$(sheetName)]);
END IF     

NEXT

 

Best regards,

Kaveri