topic Re: Need help @ data Extraction. in QlikView

Need help @ data Extraction.

lalitkgehlot89 — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Need help @ data Extraction.

tresB — Mon, 06 Jun 2016 05:21:41 GMT

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.

Re: Need help @ data Extraction.

avinashelite — Mon, 06 Jun 2016 05:55:21 GMT

try this too

Dynamically Loading Multiple Excel Files

Loading Multiple Excel Sheets Dynamically along with file name and sheet name

Re: Need help @ data Extraction.

Pulkit_Thukral — Mon, 06 Jun 2016 06:04:11 GMT

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

Re: Need help @ data Extraction.

qlikview979 — Mon, 06 Jun 2016 06:27:38 GMT

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.

Re: Need help @ data Extraction.

Anonymous — Mon, 06 Jun 2016 07:41:15 GMT

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