Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
//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
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.
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