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

[AfeefaTk]

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

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 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 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 table's headers should be the keys from the Get Straight Table Data block response as shown in the screenshot below.
   
   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 excel
   
   
6. Use Loop batch block to loop over all items from the straight table and processes them in batch.
   
   
7. Initialize a variable called 'RowsString' of type string which is used to generate payload as an array of rows containing the values for the Add Rows To Table (Batch) block from the Microsoft Excel connector.
   
   
8. Use another Loop block to loop over each item in the batch and initialize another variable called 'SingleRow' of type string which is used to build a single row that corresponds to an item from the straight table.
   
   
9. Use the Add Rows To Table (Batch) block from the Microsoft Excel connector to insert an array of rows 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 Content

How to : Getting started with Microsoft Excel in Qlik Application Automation

[Visual_Intelligence_Support]

Stuck at the step 1.  You didn't explain how to set up "Create workbook session" but straight go to the second step to add worksheet.

In step 1, it asks drive ID and item ID. Where do we have them?

Thanks

Fei

[moshea]

what is the item ID?

[ccaporaso]

Hi @AfeefaTk 

Thanks for your article.

I have a similar use case to the one described in your post. The difference is that the straight table content must be copied and updated to an SQL Table.

¿Are you aware of a similar solution? ¿Any warning or advice?

Thanks in advance for your help.

Cecilia.

[Sonja_Bauernfeind]

Hello @ccaporaso 

Please post your question and requirements in the Qlik Application Automation forum to get access to not only our active userbase, but our active support agents.

All the best,
Sonja 

[ccaporaso]

Thanks, @Sonja_Bauernfeind ! I'll do it!

Regads,

Cecilia.

[AfeefaTk]

Hi @Visual_Intelligence_Support 

You can use the do look up functionality to choose value from available options.

Please read through the help text below the input parameters to get a better understanding of how to use it.

Regards

Afeefa TK