Loading from different sheets of single excel in a single load

[Pulkit_Thukral]

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.

[Anonymous]

31 sheets contain same table data or Column name?

[Anonymous]

Have you gone through these as well?

Loading Multiple Excel Sheets | Qlikview Cookbook

Load Multiple excel sheets using For loop

Load multiple sheets and excel files

[Pulkit_Thukral]

Yes all the sheets have same column headers.

[Pulkit_Thukral]

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.

[jagan · Accepted solution]

HI,

Check this file, hope it helps you.

Load all Excel files and all sheets in a folder

Regards,

jagan.

[Anonymous]

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;

[jagan]

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.