storing excel files into qvd files

[124psu]

I have a script that is storing the imported excel spreadsheets as a qvd file. I'd like to do this all in one shot as I have about 10 years worth of data. 

My real question: can I create multiple scripts in the same app and bring in the spreadsheets I want and generate a qvd file for each table for that given year? 

Just to clarify, I have a snapshot of a diagram. 

I'll have the same qvd generating script for each separate year, that'll have all the excel sheets I am converting and then also changing the file path so that it reflects to that year. I envision that it will go through each sheet/table from excel and store it in 2011 folder, 2012 folder, etc. 

Still waiting for the files but wanted to have all my ducks in a row and wanted to get some feedback if this will be fine and is this the optimized way to do so?

[Mauritz_SA]

Hi there

Just a comment from my side. Consider creating a sub called something like StoreExcelAsQVD. I found this article on it which explains the basics. In this you can define parameters in variable formats (not explained in the article) such as the source file (or path), the sheet name (Qlik Sense cannot dynamically detect the sheet names in an Excel sheet) and the store path and name. You can then easily manage the process using another table which has these values (sourcepath, name, sheetname, storepath and name) for each file.

The benefit of this approach is that you can re-use the sub and hence remove the need for a new section every time there is a new file. The table with the information should ideally be stored outside of the Qlik app so that you can change/update it without having to change the script. If the sheet name is always going to be the same (or if it can be deduced from the file name like when you have a file called 2019Financials.xlsx and the sheet is called 2019_Sales) then you can easily load all of the information dynamically by getting the list of files in the source folder(s), checking if there is a QVD for them and creating one if there isn't or if the source file has been updated after the QVD was created.

Obviously it depends on whether this is a once off project (load) or not. If it is, then you can add the scripts like you wanted to, but it would be much more scalable if you spend some time building an architecture similar to the one I described above. Regardless of what you choose, if the extraction process is the same then I would at the very least put that procedure in a sub.

Hope this helps.

Mauritz

[rubenmarin]

Hi, maybe a FOR bucle can help to avoid repeting similiar code:

SET vStartYear = 2011;

SET vEndYear = 2019;

FOR vYear=$(vStartYear) to $(vEndYear)

  // Do stuff, use $(vYear) to replace year numbers

  STORE tempTable into TableName_$(vYear).qvd(qvd);

  DROP tempTable

NEXT