Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

automating uploades from excel

Hi all,

I want to be able to automate an excel sheet upload into QV.  Basically a new sheet of data will be entered into  the same excel file on a monthly basis. Up until now each sheet has been uploaded to QV at the end of every month by copy-pasting the load command with a couple changes in time and date variables, for example:

'qlikviewtest':

LOAD 2013 as Year,

'Dec' as Month,

MakeDate (2013, 12, 1) as Date,

*

FROM

(ooxml, embedded labels, Table is Dec13);

Concatenate ('qlikviewtest')

Load 2014 as Year,

'Jan' as Month,

MakeDate (2014, 1, 1) as Date,

*

From

(ooxml, embedded labels, Table is Jan14);

Concatenate ('qlikviewtest')

Load 2014 as Year,

'Feb' as Month,

MakeDate (2014, 2, 1) as Date,

*

From

(ooxml, embedded labels, Table is Feb14);

I want a QV script that will automatically concatenate the tables in each sheet without having to copy-paste the latest month's table and make small changes to the variables. Thanks!

1 Solution

Accepted Solutions
Not applicable

Re: automating uploades from excel

Hi,

Check qvw and excel file attached.

5 Replies
Not applicable

Re: automating uploades from excel

Hi,

Check qvw and excel file attached.

Not applicable

Re: automating uploades from excel

works great, but can you help me understand lines 26-44 (practically all of it)

Not applicable

Re: automating uploades from excel

Sure, no prob.

To be able to loop on the different sheets, then we have to know all of their labels.

This can be done by reading the excel document with ODBC and then calling SQLTABLES.

The connection requires to specify the path of the excel document which I'm putting in variable vTargetPath (used also in the load statement).

You can add an "exit script" after the disconnect, reload and check the tableviewer (ctrl+t) to actually see the content of the created table.

Now that we have all the labels in a table, and since our sheets all have same structure, we only have to write a load statement once within a for..next and provide the sheet label in a variable instead of it being hardcoded, and the data will be concatenated automatically.

So in each iteration i, vSheet will contain the sheet label in the correct format (without the $, that's what the PurgeChar function does, it removes the characters that we specify, here it's the $).

To read the sheet label from the table, I use the peek function for which you provide the field name (TABLE_NAME), the index (i, so 0 is the first row, 1 the second, and so on) and the table from which we're reading (sheets that we loaded previously).

Now in this case, we also have to create a date from the sheet label that is formatted as MMMYY.

So I use the Date# function to provide the input format then the Date function with the needed output format (omit the output format and it takes it from the environment variable DateFormat specified at the very start of the script).

Actually it'll directly be treated as a start of month, so we can omit the MonthStart function in this case.

The Floor function removes the time part in a date (like DayStart function) and stores the value as a number (for example 41832 for July 12, 2014).

I personally always use it for storing dates as their format can be adjusted later on the UI and this saves from handling formats within expressions.

But you can omit this too if you want the values to appear as dates directly, in which case you just need to:

- redefine vDate as: let vDate = Date(Date#('$(vSheet)', 'MMMYY'));

- surround the $(vDate) in the load with single quotes like this '$(vDate)'.

The Load part is normal, in which we put our fields created from vDate (year, month, date) and the ones from excel (*).

vSheet and vDate are adjusted dynamically on each iteration.

Hope this clarified the steps.

If you feel something's still not clear and you need further detail, let me know.

Not applicable

Re: automating uploades from excel

wonderful explanation, I understand the bulk of whats going on now.  My only question (sine i'm still new to qlikview) is what is the purpose of the '$'?  Is it a way to anchor data similar to excel?

Not applicable

Re: automating uploades from excel

If you mean the $ that we are purging with PurgeChar, you can check the values of field TABLE_NAME, you'll see that you have Dec13$, Jan14$, Feb14$.

If we load the excel sheet with vSheet having those values, an error is raised.

In a normal load from an excel (without using a variable), you can notice that the sheet name is Dec13.

So what we do is to provide same naming to the variable by removing the $.

Otherwise if you mean the $ before the variable, it replaces it with its value (dollar sign expansion).

If it should be considered as string/characters, we surround it with single quotes.

Within the load statement, if we don't put $, vDate will be considered as a field name and an error is raised.

If you omitted the floor function in vDate (so you have it as Date(...)), its value would be 1/12/2013, in which case we should add the single quotes.

This can be a bit tricky, so I suggest you also check the below:

- search for "dollar sign expansion" in qlikview help

- this link http://community.qlik.com/blogs/qlikviewdesignblog/2013/11/04/the-magic-of-variables

- and this link http://community.qlik.com/blogs/qlikviewdesignblog/2013/11/18/dollar-expansions

Community Browser