Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Qlik gurus/Experts,
I'm trying to load multiple sheets of an excel file and had no luck.So i'm here for some help please help me.
I have multiple excels, each excel has multiple sheets (same fields but different years data). I'm trying to load these multiple sheets data and need help with that. I know how to load excel's with similar naming convention if each excel has one sheet only. But here the requirement is each excel has multiple sheets. I have attached the sample excel data for your reference. Thank you in advance for our help.
Dear Ramya,
Please find the attached qlikview document.
I hope it is suitable for you.
ODBC CONNECT32 TO [Excel Files;DBQ=E:\NIGERA-DOCUMENT\Multiple Excel sheet.xlsx];
tables:
SQLTables;
DISCONNECT;
Tables1:
load *,
replace(TABLE_NAME,'$','') as NEW
resident tables;
DROP Table tables;
let var=NoOfRows('Tables1');
FOR i = 0 to $(var)-1
let sheetName=peek('NEW', i,'Tables1');
Data:
CrossTable(Month, Data, 3)
LOAD [Testing Area],
Reference,
SubField('$(sheetName)','_',2)as Year,
Jan,
Feb,
Mar,
Apr,
May,
Jun,
Jul,
Aug,
Sep,
Oct,
Nov,
Dec
FROM
[Multiple Excel sheet.xlsx]
(ooxml, embedded labels, table is $(sheetName));
next i
DROP Table Tables1;
Try the below script
LET vStartSheetNumber = 2016;
LET vEndSheetNumber = 2018;
LET vExcelFileName = 'Data';
// Generate Empty table
Data:
LOAD
'' AS Data.Field1,
'' AS Data.Field2
AutoGenerate(0);
FOR index = vStartSheetNumber TO vEndSheetNumber
Concatenate(Data)
LOAD
*
FROM
[Multiple Excel sheet.xlsx]
(ooxml, embedded labels, table is [Year_$(index)]);
NEXT
Hi,
Go for the for loop script for this a good for multiple file loading.
Regards,
Anand
Dear Ramya,
Please find the attached qlikview document.
I hope it is suitable for you.
ODBC CONNECT32 TO [Excel Files;DBQ=E:\NIGERA-DOCUMENT\Multiple Excel sheet.xlsx];
tables:
SQLTables;
DISCONNECT;
Tables1:
load *,
replace(TABLE_NAME,'$','') as NEW
resident tables;
DROP Table tables;
let var=NoOfRows('Tables1');
FOR i = 0 to $(var)-1
let sheetName=peek('NEW', i,'Tables1');
Data:
CrossTable(Month, Data, 3)
LOAD [Testing Area],
Reference,
SubField('$(sheetName)','_',2)as Year,
Jan,
Feb,
Mar,
Apr,
May,
Jun,
Jul,
Aug,
Sep,
Oct,
Nov,
Dec
FROM
[Multiple Excel sheet.xlsx]
(ooxml, embedded labels, table is $(sheetName));
next i
DROP Table Tables1;
Hi,
Close the thread by making an answer correct.
So that it will help to others.
Regards
I'm sorry some reason it doesn't show close option. In the Actions only" Mark as Helpful" option available.