How to create a loop to add sheet names to the table in the load editor

[michelle22]

Hi all,

I have a number of Excel files with different sheet names (and number of sheets) that I am trying to pull into Qlik  and also create a field with the sheet name. Each of the sheets have the same fields: URL, Date, and Content.

For example, say I have three files with the following sheets:

File Name	Sheet Name(s)
File(1)	Blue, Red
File(2)	Blue
File(3)	Yellow

I would like a situation whereby I can load all of the files into a table with the fields: URL, Date, Content, Sheet Name. This is because there is information in the Sheet Name that I would like to use to create a calculated field/dimension. I was trying to write a loop to do so but was just tripling all the records:

For each vSheet in 'Blue','Red', 'Yellow'

[Load]:
LOAD
    URL,
    Date,
    Content,
    '$(vSheet)' as [Sheet]
FROM [lib://Folder/*.xlsx]
(ooxml, embedded labels, header is 4 lines); 
next vSheet

I would be open to first loading each of the different sheets together (all Blues, Reds and Yellows) and then concatenating if that is the only option. This way I could create a field based on the Table Name. However, again I tried below and while I get three separate tables, each of the tables contain all the data from each Blue, Red and Yellow sheet.

For each vSheet in 'Blue','Red','Yellow'

'$(vSheet)':
LOAD
    "URL" as ['$(vSheet)'URL],
    Date as ['$(vSheet)'Date],
    Content as ['$(vSheet)'Content]
FROM [lib://Folder/*.xlsx]
(ooxml, embedded labels, header is 4 lines);
next vSheet

The file names do not provide any information to help identify them or else I would have used that (i.e. loading FROM all files names with 'Blue').

[Aditya_Chitale]

its because you have removed the sheet name part from your load script code (table is [sheet_name]). 

Hence the loop becomes meaningless. QlikSense will fetch data from all sheets from all present excel files and just add variable flag as a hardcoded entry.

Here, in your case, when you are adding 'vSheet' as sheet name in your table load script, it is simply a plain text stored in the variable and is not linked with or have any relation with sheet name of your file.

Hope this clears your doubt.

Regards,

Aditya

[michelle22]

Thanks, Aditya - that makes perfect sense.

I had removed this as each file (in the folder) has different sheets, so Qlik throws an error once it reaches the file without that sheet. Ideally, I'd like Qlik to fetch the data from all files and store the sheet name it is from.

Alternatively, is there a way to specify the sheet/table name in the load script but for Qlik to ignore or pass over any files without that sheet name?

[marcus_sommer]

You will need to specify from which sheet you want to load - therefore you couldn't skip this part. To fetch possible errors you may use ERRORMODE to ignore them. The IMO better way would be to load these data per ODBC which enables to read the meta-data at first to see which sheets exists before you access them and here the howto:

Solved: Loading from multiple Excel files and multiple she... - Qlik Community - 372841

[Aditya_Chitale]

@michelle22 

You will have to store the sheet names in a separate sheet with uniform sheet names & column names for all excel files

once done with that, you can use below code to dynamically fetch data from available sheets irrespective of sheet names.

Try using below code in script editor:

// Path where all excels are stored
let vFilePath='lib://AIF & Offshore BI/Qlik Community*.*';

FOR Each vFileName in FileList('$(vFilePath)')

// storing all excel names in a table

FileList:

LOAD

subfield(subfield('$(vFileName)','/',4),'.xlsx',1) as Name

autogenerate 1;

NEXT vFileName

// looping through excel name (1 excel at a time)

let vFileCount=NoOfRows('FileList')-1;

for i=0  to '$(vFileCount)'

let vExcel=Peek('Name',$(i),'FileList');

// Storing sheet names of first excel from loop in a table

// here sheet names are fetched from manually created column as mentioned above. Make sure to keep sheet & column name uniform for all excels

SheetCount:

LOAD

    SheetNames as [Sheet Name]

FROM [lib://AIF & Offshore BI/$(vExcel).xlsx]

(ooxml, embedded labels, table is Sheets);

// looping one by one through all sheets from excels from earlier loop (1 sheet at a time)

let vSheetCount=NoOfRows('SheetCount')-1;

for j=0 to '$(vSheetCount)'

let vSheetName=peek('Sheet Name',$(j),'SheetCount');

// consolidated table cotaining data from all excels

mainTable:

LOAD

    "URL",

    Date(num#("Date"),'DD-MM-YYYY') as Date,

    Content,

    filename() as FileName

    $(vSheetName) as SheetName

FROM [lib://AIF & Offshore BI/$(vExcel).xlsx]

(ooxml, embedded labels, table is [$(vSheetName)]);

next j

Drop table SheetCount;

next i

drop table FileList;

Output:

Regards,

Aditya