article How to copy and update data to Microsoft Excel sheet using Qlik Automations (small datasets) in Official Support Articles

How to copy and update data to Microsoft Excel sheet using Qlik Automations (small datasets)

PietMichielRappelet — Thu, 09 Feb 2023 15:39:11 GMT

This article elaborates on keeping a Microsoft Excel table in sync with data from e.g. a cloud system or a Qlik Sense table.

Content:

Qlik Application Automations can be used, however I would like to share some important need-to-knows and limitations of the Microsoft Excel connector:

Limitations

The Microsoft Excel connector in Automations is not designed for data movement of large datasets. The performance of the 'upsert' pattern is not great because there is no native support to find table indexes for matching id's. The used 'find row in sheet' block lists and loops through all records to find a match causing bad performance.
It is advised to work inside a worksheet session for performance reasons and to ensure there is no synchronization conflict when executing the automation.
An alternative is using a platform like Airtable that supports a native 'get table record by field' block.

Prerequisites

In the shared example below and attached export, we use a Qlik Sense straight table as source data set. You can obviously also use any other supported clouds system, native file storage sytem or external source through the Call url block.
- In the below example we used the claims table from the QlikSense demo project ‘Insurance claims’ (https://demos.qlik.com/)
You’ll need a Microsoft Excel file in a OneDrive folder and one table with required headers where the data will be synced to:

The Automation

We apply a selection on our QlikSense sheet and retrieve the data we want to sync to the Excel worksheet. Please note that there is a limit of 100 000 records that can be retrieved through the ‘Get Straight table’ block.
Use a variable of type ‘list’ to create the rows, selecting the data you want in the correct sequence. Note that you’ll need to add the exact number of columns from the Excel table. You can add blanks for columns you do not want to update. A consequence of this behavior is that you cannot make cells blank.
In this upsert pattern, we create a row for new records and update existing data. We’ll use the first field (Claim Id) as unique identifier.
1. Find row in excel worksheet table: will check the unique identifier is found in the list and if so return the row index. If not found the index -1 is returned.
2. Condition checking the index returned to decide if we need to create or update
3. Add row to table: specifying the row variable
4. Update row to table: specifying the row variable and the index
Optionally we’ve added some output blocks to support debugging purposes - indicating if record has been found or not and which index was used for the upsert pattern.

Additional notes

Workbook session: it is advised to use a specific session for performance and synchronization conflict reasons.
- We noticed during our tests that it can take up several seconds before a change in the webinterface of Excel online is picked up by the automation blocks (API).
- To work inside one session, you can start your automation with ‘create workbook session’ block, add that session-id in all subsequent blocks, and end with the ‘close workbook session’ block.
To save backups of previous versions, you can easily write the data to another sheet before starting this upsert pattern:

Re: How to copy and update data to Microsoft Excel sheet using Qlik Automations (small datasets)

mck-mthomas — Wed, 26 Apr 2023 22:17:41 GMT

@PietMichielRappelet I am confused, the automation is to push straight table data out to excel; however, I have a table built within the app I am targeting. This automation seems to make me select each field within the data sets of the application, not the field within the table I already created?

Am I using the wrong automation template if I just want to sync my table data to an excel sheet?

Re: How to copy and update data to Microsoft Excel sheet using Qlik Automations (small datasets)

Sonja_Bauernfeind — Mon, 08 May 2023 07:05:41 GMT

Hello @mck-mthomas

Please drop your question and needs over in our active Qlik Application Automation forum. We have support agents monitoring this forum to help - as well as a lot of active users of Qlik Application Automation!

All the best,
Sonja

Re: How to copy and update data to Microsoft Excel sheet using Qlik Automations (small datasets)

mck-mthomas — Mon, 08 May 2023 14:37:07 GMT

@Sonja_Bauernfeind how do I move this comment, or do I just add as a new over in that forum? It was really a question about these specific instructions, not sure dropping a new comment without the context of the above is going to make sense to anybody?

Re: How to copy and update data to Microsoft Excel sheet using Qlik Automations (small datasets)

Sonja_Bauernfeind — Mon, 08 May 2023 15:18:09 GMT

Hello @mck-mthomas

Let me see if I can get you an answer to this, I'll get back to you soon as I can.

Otherwise, though: asking the question with a reference to the article is perfectly valid.

All the best,
Sonja

Re: How to copy and update data to Microsoft Excel sheet using Qlik Automations (small datasets)

AfeefaTk — Tue, 09 May 2023 12:56:31 GMT

Hi @mck-mthomas

Could you please let me know which template are you using among these two?

Copy and update data from a Qlik Sense straight table to an Excel sheet -> In this template straight table data is exported to an existing table of the Excel sheet.
Copy data from a Qlik Sense straight table to an Excel sheet -> This will create a new Excel sheet and table within the Excel sheet to which the straight table data is exported. More info can be found here

Also, could you please explain a bit more about the use case?

Thanks