If you are a Qlik developer, you know how to load data from Excel as it is a fairly straight forward process.
But, what if you need to extract each sheet name as you load the respective table from Excel?
Let’s explore the steps first.
- Load all tables first (All sheets within an Excel file)
- Loop through tables to extract sheet name
- Loop through all the sheets from step 2 and add sheet name to the respective table.
// 1. Load all tables first (All sheets within an Excel file)
// 2. Loop through tables to extract sheet name
/3. * Loop through all the sheets from step 2 and add sheet name to the respective table. */
Do you want to get Qlik certified and improve your skills and earnings?
Join our academy and let us help you with that!
(Photo credit: Unsplash)
If you have to hard-code the table names to load, then you already know the names. Why would you need a loop to collect the table names?
Hi Rob, great to see your comment. The script works without a hard-coding table name. Someone asked how to extract table names without any intervention so I thought of introducing a loop. I’d love to know if there is a better approach.
I agree with rob,
that was the first thing that popped into my head when I saw your script
you have your sheet names “hardcoded” in your example when you load each table
how else will you load the excel without “hardcoding” the sheet name?
the only way I know of getting sheet names included in the excel file is if you connect to the excel file via ODBC/OLEDB connection. and then use “sqltables statement”
looping-through-excel-sheets:
https://qlikviewcookbook.com/2008/09/loading-multiple-excel-sheets/
https://www.qlikviewaddict.com/2012/03/looping-through-excel-sheets.html
http://qlikviewmaven.blogspot.com/2008/09/loading-all-of-files-from-folder.html