Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
The requirement is as followed:
1. Loading data from 31 different sheets of same excel file in a single load (Sheet names are the dates(ex- 01-DEC-2015))
2. Adding a column on each sheet named 'Open date' that describes the same date as written in sheet name.
3. Appending each sheet to its next sheet present in the excel.(ex- 1st dec sheet entire data to be appended in 2nd dec sheet and so on.)
Thanks in advance.
31 sheets contain same table data or Column name?
Have you gone through these as well?
Loading Multiple Excel Sheets | Qlikview Cookbook
Yes all the sheets have same column headers.
Thanks for sharing these links Balraj,
Can you please help in forming a variable with the date as on the sheet name.?So that while loading from every sheet i use that variable.
Try also this:
Set sFile = 'C:\\Test.xls';
FileTable:
Load * Inline [
iSheet, sSheet, sSheetName, sShort,
1, "SheetName_1$", "Prosa Name 1", "PN1",
2, "SheetName_2$", "Prosa Name 2", "PN2",
3, "SheetName_3$", "Prosa Name 3", "PN3",
4, "SheetName_4$", "Prosa Name 4", "PN4",
5, "SheetName_5$", "Prosa Name 5", "PN5",
6, "SheetName_6$", "Prosa Name 6", "PN6"
];
UNQUALIFY*;
for i=0 to NoOfRows('FileTable')-1
let sSheetName = peek('sSheetName', $(i), 'FileTable');
let sSheet = peek('sSheet', $(i), 'FileTable');
let sShort = peek('sShort', $(i), 'FileTable');
Temp:
LOAD Col1,
Col2,
Col3,
'$(sSheetName)' as _SheetName,
'$(sShort)' as _Sheet
FROM $(sFile) (biff, embedded labels, header is 1 lines, table is $(sSheet));
next i;
Hi,
Did you tried this, it will read all excel files and sheets in a folder
Load all Excel files and all sheets in a folder
Regards,
jagan.