Skip to main content

Official Support Articles

Search or browse our knowledge base to find answers to your questions ranging from account questions to troubleshooting error messages. The content is curated and updated by our global Support team

Announcements
QlikWorld 2023, a live, in-person thrill ride. Save $300 before February 6: REGISTER NOW!

Qlik Application Automation: Copy data to Microsoft excel sheets using automations (small datasets)

cancel
Showing results for 
Search instead for 
Did you mean: 
AfeefaTk
Support
Support

Qlik Application Automation: Copy data to Microsoft excel sheets using automations (small datasets)

Attachments

This article describes the templates available in Qlik Application Automation which allow you to synchronize data from a Qlik Sense straight table to Microsoft Excel.

We have two templates currently available

  1. Copy and update data from a Qlik Sense straight table to an Excel sheet (for more info check Copy and update data to Microsoft Excel sheet using automations (small datasets))
  2. Copy data from a Qlik Sense straight table to an Excel sheet

 

Let's discuss the automation configured in Qlik Application Automation to copy data from the Qlik Sense straight table to Microsoft Excel.

Similar to the 'Copy and update data from a Qlik Sense straight table to an Excel sheet' template, we'll use a Qlik Sense straight table as the source data set.

The main difference between these two templates is that in the 'Copy data from a Qlik Sense straight table to an Excel sheet' template we don't sync data to an already existing Microsoft Excel worksheet or table, instead, we will create a new worksheet and table within this worksheet with the required headers.

 

Full automation

Screenshot of  full automationScreenshot of full automation

 

Step by step description

  1. Start your automation with the Create Workbook Session block from the Microsoft Excel connector. Use the session id that's returned by this block in all subsequent blocks that have a Session Id input field. The session id field can be found by navigating to the settings of Microsoft Excel blocks as shown in the below screenshot. 
    Note that when using a Microsoft Excel workbook session in an automation, the session must be closed at the end of the automation by adding a
    Close Workbook Session block.
    Session id for Microsoft Excel blocksSession id for Microsoft Excel blocks
  2. Create a new worksheet using the Add Worksheet block from the Microsoft Excel connector.
  3. Use the Get Straight Table Data block from the Qlik Cloud Services connector to get data from the straight table for a specific app that we want to sync to the Microsoft Excel worksheet.
    Note the limit of 100 000 records that can be retrieved through the Get Straight Table Data block. 
  4. The response from the Get Straight Table Data block will be as shown in the below screenshot. The keys will act as headers and the values will act as rows corresponding to the keys in the Microsoft Excel table.

    Response from qlik sense straight tableResponse from qlik sense straight table

  5. Create a new table in the worksheet created in the previous step using the Create Excel Table With Headers block from the Microsoft Excel connector. As mentioned in the above step, the headers of the table should be the keys from the Get Straight Table Data block response as shown in the below screenshot.
    Keep in mind that the number of columns selected using the start column and end column has to match with the headers array. E.g. C-E as column start/end for an array of 3 headers. If the headers array doesn't match with the column range added as an input(Start Column, End Column) to the 'Create Table With Headers' block, this block fails to execute with an error message "The number of rows or columns in the input array doesn't match the size or dimensions of the range." 

    Create table in excelCreate table in excel

  6. Use Loop block to loop over all items from the straight table.
  7. Initialize a variable called 'row' of type list and use another Loop block to loop over all the keys from the straight table.
  8. Add this 'row' variable within the loop to get each column value for the corresponding key from the straight table as an item of this list variable which ultimately gives a single row with values for all the keys in each iteration.
  9. Use Add Row To Table block from the Microsoft Excel connector to add each row to the table.
  10. Make sure to close the workbook session by adding the Close Workbook Session block from the Microsoft Excel connector at the end of the automation.

Please check out the attached JSON file containing the above automation.

Follow the steps provided in this article Upload Automation Workspace to import the automation from the copy-data-qlik-sense-excel.json file that's attached to this article. 

Related Resouces

Labels (2)
Version history
Last update:
‎2022-07-19 12:50 AM
Updated by: