Load specific Excel sheet with variable name

[Nwinx1984]

Hello, I am new to Qlik app creation and I need to load many Excel files from a folder 

• Filename format: "TEXT - YYYY MMM.xlsx" (e.g. "Text - 2021 Jan", "Text - 2021 Feb", etc...)
• Sheet name format: "MMM YYYY" (e.g. "Jan 2021", "Feb 2021", etc...). This is always the second sheet in the file.

It should be easy to extract from this the year and the month to tell the load editor which Excel table to load, but somehow, the variable containing the sheet name is not being evaluated properly.

My code (based on a thousand forum answers) is the following:

-----------------------------------------

//Before this code I set the structure of the "Tickets" table, which contain the Distributor, Date, Month and Year fields

For each File in FileList('[lib://xxx/xxx/Statistic - *.xlsx]')

TempSheetName:

LOAD
right(subfield(filebasename( ), ' - ', -1), 3) & ' ' & left(subfield(filebasename( ), ' - ', -1), 4) as SheetName;
set vSheetName = SheetName;

Table1:
Concatenate (Tickets)

LOAD

//Material is the header of one of the columns in sheet 2
If(Material='4600081' OR Material='4600083', 'SJ',

If(Material='4608125', 'N/A - Sales Message (SM)/pc', Material
)

) As [Distributor],

//...

"Date",
Month("Date") as "Month",
Year("Date") as "Year"

From $(File)
(ooxml, embedded labels, table is [$(vSheetName)]);
DROP Table TempSheetName;

NEXT File;

-------------------------------

Explanation: I create a temp table (I'm not expert at all and I haven't figured out yet the importance of a resident load in this case) where I calculate and store the Excel sheet name based on the file name (I've tested this part separately and it works. Then I set a variable vSheetName that contains that value and I use that variable to specify the name of the Excel sheet to load. (I tried both SET and LET with no success)

When I try to load data, I get the error:

"The following error occurred: Field 'Material' not found" and at the bottom of the error report, where the full problematic code is displayed, it seems that the variable $(File) is correctly used but $(vSheetName) is not:

"(ooxml, embedded labels, table is ***)".

Please note I have tried every possible format to use the variable: $(vSheetName), [$vSheetName], [$(vSheetName)], @$vSheetName, @2 (meaning "Sheet 2", apparently deprecated now) and many more, but nothing is working (it seems like everybody on this forum uses a different format!!!)

It seems so strange to me that it is so hard to load the second sheet in an XLSX file! Am I missing something? Any help would be appreciated, thanks!

[MayilVahanan]

Hi @Nwinx1984 

Try like below while storing the sheet name in variable

Let vSheetName = Peek('SheetName');

Check the value in the variable.

[Nwinx1984]

Nothing changed unfortunately.

I've also added a line to load the variable in another existing column in the table:

[...]

'$(vSheetName)' as "Code"

[...]

but it is evaluated to ' ' (blank).

[MayilVahanan]

Can you paste ur full script?

[eddie_wagt]

Should the code not be like this? Looks like you omitted the source (see Bold text)

LOAD
right(subfield(filebasename(), ' - ', -1), 3) & ' ' & left(subfield(filebasename(), ' - ', -1), 4) as SheetName
FROM $(File); // Need to add this

LET vSheetName = peek('SheetName');

TRACE $(vSheetName); // Show result in log

[Nwinx1984]

I don't know if the FROM statement is needed or not (in this case I am not importing anything from that file, I am just storing part of the filename in a new column).

Anyway, I've tried your code and it does not work unfortunately. The log return a blank value for the variable. 😞

[eddie_wagt]

Hello @Nwinx1984 ,

I cannot replicate what is going wrong in your code, but maybe this post by @avinashelite  can contain a solution for you. I have implemented this code several times for reading Excel files.

https://community.qlik.com/t5/Documents/Loading-Multiple-Excel-Sheets-Dynamically-along-with-file-na... 

Regards Eddie

[Nwinx1984 · Accepted solution]

Alright, thank you everyone for helping! I spent some time trying to use an ODBC connection to load the Excel file, but I encountered technical problems in my organization so I could not really test it.

Nevertheless, I kept on modifying the code and discovered that the problem was "FileBaseName()" which was not returning anything and therefore the variable vSheetName remained empty.

The solution was to replace FileBaseName() with File, the variable I was already using for the For loop. Final working code below.

-------------------------

For each File in FileList('[lib://xxx/xxx Statistic - *.xlsx]')

let vSheetName = left(right(subfield('$(File)', ' - ', -1), 8), 3) & ' ' & left(subfield('$(File)', ' - ', -1), 4); 
TRACE $(vSheetName);

Table:
LOAD *

From $(File)
(ooxml, embedded labels, table is '$(vSheetName)';

NEXT File;