topic Re: Loading from different sheets of single excel in a single load in QlikView

Loading from different sheets of single excel in a single load

Pulkit_Thukral — Fri, 20 Nov 2020 18:02:30 GMT

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.

Re: Loading from different sheets of single excel in a single load

Anonymous — Thu, 28 Jan 2016 07:54:16 GMT

31 sheets contain same table data or Column name?

Re: Loading from different sheets of single excel in a single load

Anonymous — Thu, 28 Jan 2016 07:57:22 GMT

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

Re: Loading from different sheets of single excel in a single load

Pulkit_Thukral — Thu, 28 Jan 2016 08:11:02 GMT

Yes all the sheets have same column headers.

Re: Loading from different sheets of single excel in a single load

Pulkit_Thukral — Thu, 28 Jan 2016 08:13:03 GMT

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.

Re: Loading from different sheets of single excel in a single load

jagan — Thu, 28 Jan 2016 08:26:58 GMT

HI,

Check this file, hope it helps you.

Load all Excel files and all sheets in a folder

Regards,

jagan.

Re: Loading from different sheets of single excel in a single load

Anonymous — Thu, 28 Jan 2016 09:54:50 GMT

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;

Re: Loading from different sheets of single excel in a single load

jagan — Fri, 29 Jan 2016 00:20:19 GMT

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.