Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
HI,
I have different sheets in single excel file how i have to load all sheets data .
Please find attached file.
Thanks,
For Each file in FileList('C:\Users\QlikView\Downloads\test.xlsx');
ODBC CONNECT32 TO [Excel Files;DBQ=$(file)];
tables:
SQLtables;
DISCONNECT;
FOR i = 0 to NoOfRows('tables')-1
LET sheetName = purgeChar(purgeChar(peek('TABLE_NAME', i, 'tables'), chr(39)), chr(36));
Table:
Load *
From $(file)(ooxml, embedded labels, table is [$(sheetName)]);
NEXT i
Drop table tables;
Next file
====================
Define your File Path
For Each file in FileList('C:\Users\QlikView\Downloads\test.xlsx');
The structure of data is Always the same so you could load a sheet into a loop,
Define a string with your months then using the index of a loop with the subfield() expression you can loop through all the sheets, changing the sheet name into the load with a variable containing the piece of substring.
Hi,
If the sheets are constant then use Concatenate(), if they are dynamic then foreach as Manish specified.
Data:
Load *
From FileName.xlsx (ooxml, embedded labels, table is Sheet1);
Concatenate(Data)
Data:
Load *
From FileName.xlsx (ooxml, embedded labels, table is Sheet2);
Regards,
Jagan.
Hi try this script
let vPath='Your file path';
ODBC CONNECT TO $(vPath)
//------ Create a list of sheets from Excelworkbook
XLtable: // Name the table that will be created by SQLTables stmt
SQLtables;
DISCONNECT; //Discounnect from ODBC - tables are loaded into document
TRACE Loop through tables;
//EXIT Script;
FOR i = 0 to NoOfRows('XLtable')-1 //Loop and set number of tables
//Get name of each sheet
//Remove all $-signs and quotes '
//Get name of each sheet
//Remove all $-signs and quotes '
LET sheetName = purgechar(PurgeChar(peek('TABLE_NAME', i, 'XLtable'), chr(39)),'$');
TRACE Loading data from $(sheetName);
Tablename :
Load Statement ;
NEXT
DROP TABLE XLtable; //Drop list of possible sheets in XL
//exit script;
Try this first if you still face issue I will provide application and screen shots how to do it.