Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Pulkit_Thukral
Partner - Creator II
Partner - Creator II

Loading from different sheets of single excel in a single load

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.

1 Solution

Accepted Solutions
jagan
Partner - Champion III
Partner - Champion III

HI,

Check this file, hope it helps you.

Load all Excel files and all sheets in a folder

Regards,

jagan.

View solution in original post

7 Replies
Anonymous
Not applicable

31 sheets contain same table data or Column name?

Anonymous
Not applicable

Pulkit_Thukral
Partner - Creator II
Partner - Creator II
Author

Yes all the sheets have same column headers.

Pulkit_Thukral
Partner - Creator II
Partner - Creator II
Author

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
Partner - Champion III
Partner - Champion III

HI,

Check this file, hope it helps you.

Load all Excel files and all sheets in a folder

Regards,

jagan.

Anonymous
Not applicable

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
Partner - Champion III
Partner - Champion III

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.