A very useful code to know in order to develop a QlikView dashboard is how to loop through files and tabs in Excel sheets. In this post I’ll share the codes with you!
During my time at Deloitte, I built a lot of dashboards. Most of the time, I used QlikView to create dashboards. Even though I’m a freelance data analyst/scientist now, I still use QlikView, but in a different way. Even when I’m in a data science assignment, I keep using QlikView occasionally.
My latest project was for an SME company. They were not very familiar with data analyses. Some people did some kind of analyses, which was mainly done in (not surprisingly) Excel. Nothing wrong with Excel to some extend (although many analyst and scientist might disagree 😉 ), I think it’s a great tool to do some ad-hoc and one-off analyses. When an analyses needs to be repeated or more and more people want to use the results, you might want think of another solution. So far, my thoughts on Excel 😉
Since a lot of data is available in Excel with logic from other business users, I often use QlikView to load data from different Excel sheets, connect data, perform some quick analyses and create visuals easily. So how do you read multiple Excel files from one folder with one easy script? And if the Excel file contains multiple tabs, how do you read them with a single script? I’ll tell you how!
A small note: the code below is an example with Excel files. Of course, you can also use csv files. Just make sure you change xlsx into csv and the file’s meta information (ooxml, embedded labels, table is Sheet1).
First thing to get in place is the data storage. For this tutorial let’s store all the files in a folder named ‘Data’. Add historical Excel files per month in this folder, starting with the year followed by the number of the month. This is what it looks like in a folder:
Keep in mind that all the files should have the same columns. They don’t necessarily should be in the same order.
Loop through files
I can write a whole story about the code, but I’ll keep it short. This is the code you’ll need:
Data: LOAD * INLINE [ FirstColumnName ]; FOR EACH File IN FILELIST ('...\data\*.xlsx') Concatenate(Data) LOAD *, filename() FROM [$(File)] (ooxml, embedded labels, table is Sheet1); next File;
You can change FirstColumnName into one of the column names in your Excel sheets. The code first creates an empty table with one column. Then it starts to iterate through all the Excel files in the folder. The Concatenate() function adds the data from the Excel sheets to the table.
Loop through tabs
Again, this is the code:
Sheet_names: LOAD * INLINE [ SHEET_NAME SheetName1 SheetName2 ]; LET vSheets = NoOfRows('Sheet_names'); FOR i = 0 TO $(vSheets)-1 LET vSheetName = subfield(peek('SHEET_NAME', i,'Sheet_names'),'$',1); Data: LOAD peek('SHEET_NAME', $(i),'Sheet_names') as [Sheet name], * FROM ['...\Data\201710.xlsx'] (ooxml, embedded labels, table is Sheet1); NEXT i;
Just fill the table Sheet_names with all the names of the tabs in your Excel sheet. The remaining code loops through these tabs and creates a table named Data with the columns from the tab and the sheetname.
You can also combine both codes and loop through files and tabs. Good luck!