Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
.png "Former Employee"){kind=icon}
Last Update:
Feb 9, 2023 10:39:11 AM
Updated By:
Created date:
Jan 19, 2022 8:06:23 AM
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.
- 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.
- Condition checking the index returned to decide if we need to create or update
- Add row to table: specifying the row variable
- 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:
