Need help @ data Extraction.

[lalitkgehlot89]

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

[tresesco]

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.

[avinashelite]

try this too

Dynamically Loading Multiple Excel Files

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

[Pulkit_Thukral]

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

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.

[Anonymous]

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