article How to export more than 100000 cells using the Get Straight Table Data block in Qlik Application Automation in Official Support Articles

How to export more than 100000 cells using the Get Straight Table Data block in Qlik Application Automation

AfeefaTk — Tue, 02 May 2023 09:58:50 GMT

Currently, in Qlik Application Automation it is not possible to export more than 100,000 cells using the Get Straight Table Data block.

Content:

To overcome this limit, the workaround is to export records in batches from the Qlik Sense straight table to the cloud storage platform of your choice. The prerequisite is to have a unique numerical field in your dataset. If you don't have the unique field in your dataset, you can add it using RowNo() function in the load script as shown below. This counts the rows in the dataset.

In this example, we will export data from the Qlik Sense straight table to Dropbox as a CSV file.

You can also find an exported version of this automation and application attached to this article. More information on importing automation can be found here.

Full Automation

Automation Part 1

Automation Part 2

Automation Part 3

Steps to set up the variables

vAppId: Variable used to define your app ID.
vSheetId: Variable used to define your sheet ID.
vTableId: Variable used to define your straight table ID.
vFieldName: Variable used to define the field name on which the straight table data should be filtered based on the selection applied using the Select Field Value By Query block.

Automation structure

Add the Create Measure block to the automation and configure the Measure Expression parameter by providing an expression that calculates the number of rows in the dataset. In this example, I have added RowNo( ) as a unique field to the dataset, so the expression used is count([RowNo( )]).
Add the Get Measure block to get the number of rows in the dataset.
Store the number of rows obtained from the previous step in the vNRecords variable.
Delete the measure created using the Delete Measure block.
Add the vNLoops variable to calculate the number of loops that can be used to export straight table data in batches. Please note the formula used in the variable. Edit it to suit your needs. Change the 10000 value as you want but keep in mind that too many records could exceed the Get Straight Table Data block's 100,000 cells limit.
The Custom Code block creates a list with items 1 to n where n is the number of loops calculated in the previous step.
Add Create File on Dropbox block to create a new CSV file in Dropbox. You can select any other cloud storage connector, just remember to configure the relevant value in the Path parameter. The Qlik Sense straight table data will be exported to this file.
Add the Select Field Values By Query block to apply selection to the unique identifier created in the dataset to fetch only one record from the Get Straight Table Data block.
Add Write Line To File on Dropbox block to add straight table keys as column headers to the CSV file.
Add Loop block to loop over the list created using the Custom Code block.
Add Condition block to check if it's iterating over the 1st item, if yes set vRecordsStart variable value to 1, else set vRecordsStart variable value to vRecordsEnd variable. The vRecordsEnd variable represents the last record that needs to be fetched from the straight table during each iteration. Please note the formula used in the variable. The value under the Value 1 parameter in the Multiply formula should match the value provided in the Divide formula of the vNLoops variable.
Add the Select Field Values By Query block to apply selection to the unique identifier created in the dataset to fetch the records between vRecordsStart and vRecordsEnd variables from the Get Straight Table Data block.
Add Write Line To File on Dropbox block within the Get Straight Table Data block which writes each straight table data into the CSV file. Configure it to use the output from the Get Straight Table Data block as the Data parameter.
Add Save And Close File on Dropbox block and configure the file parameter to use the CSV file we have created.

Environment

Re: How to export more than 100000 cells using the Get Straight Table Data block in Qlik Application Automation

epatinos — Thu, 25 May 2023 16:25:51 GMT

Hey there @AfeefaTk , Im trying to use this automation, with the example dataset, and I can't make it work.. I got the same error: "Object data can't be bigger than 100.000 cells"..

Can you give me a hand with it? 😞

Re: How to export more than 100000 cells using the Get Straight Table Data block in Qlik Application Automation

AfeefaTk — Fri, 26 May 2023 03:37:26 GMT

Hi @epatinos

Refer step 5

In vNLoops variable , reduce the value under Value 2 which is 10000 to a lower number. For testing purposes replace 10000 with 3000.

Also make appropriate changes to the vRecordsEnd variable (Step 11). As mentioned Value 1 parameter in the Multiply formula should be the same as that of the Value 2 parameter in the Divide formula of the vNLoops variable.

Please let me know if that works.

Thanks

Re: How to export more than 100000 cells using the Get Straight Table Data block in Qlik Application Automation

epatinos — Mon, 29 May 2023 22:35:57 GMT

That work for me, I was also trying to select some fields before the measure, it seems that alterate the whole structure of the code..

Thank you!!! 🙂

Re: How to export more than 100000 cells using the Get Straight Table Data block in Qlik Application Automation

Jenser55 — Fri, 08 Sep 2023 08:28:00 GMT

I tried to build the example by using your files. (QVF and json)

I don't know, what to enter in the variable vFieldName.

Could you help me please.

(For a newbie like me this tutorial contains to less explanation.)

Re: How to export more than 100000 cells using the Get Straight Table Data block in Qlik Application Automation

skustes — Wed, 10 Jan 2024 22:21:36 GMT

Hello, I am having trouble with the "Get Measure" block. I have attempted this two ways, as you described above using the "Create Measure" block, as well as by manually creating the measure within the app. The measure is called "row_count" with a formula of "count(row_number)", which is the alias I gave the RowNo() column in the data load. When used within the app in a KPI, the measure correctly indicates that there are 143 rows.

However, using "Get Measure" in an Automation doesn't return anything. I put "row_count" into the Measure Id field, but nothing happens. When I do a test run, there is an "In" section in History, but no "Out" section. Trying to set the returned value to the vNRecords variable produces a value of 0. The "In" section of the history in the Variable block says "Set vNRecords equal to <empty>"

Why is "Get Measure" not working for me?

Re: How to export more than 100000 cells using the Get Straight Table Data block in Qlik Application Automation

AfeefaTk — Thu, 11 Jan 2024 22:49:36 GMT

Hi @skustes

Thanks for reaching out.

Could you please share the automation workspace and the app for us to investigate the issue further?

Steps on how to export automation can be found here (Section: Download Workspace).

Regards

Re: How to export more than 100000 cells using the Get Straight Table Data block in Qlik Application Automation

AfeefaTk — Fri, 12 Jan 2024 01:26:39 GMT

Hi @skustes
If you have the RowNo() function added in the load script of the app, the formula that you need to use while creating the measure is Count([RowNo( )]).

I see you are using the incorrect formula "count(row_number)".

Could you please give it a try with Count([RowNo( )]) formula in the Create Measure block and see if that works?

Please let me know your feedback.

Thanks

Afeefa TK

Re: How to export more than 100000 cells using the Get Straight Table Data block in Qlik Application Automation

skustes — Fri, 12 Jan 2024 14:34:39 GMT

Hi @AfeefaTk, I aliased the RowNo() function as row_number in the load. "RowNo() AS row_number". There is no problem within the app using "count(row_number)" to get the correct count. However, I opened a support case about the issue, so I will work through it via that channel. Thanks!

Re: How to export more than 100000 cells using the Get Straight Table Data block in Qlik Application Automation

paulcalvet — Fri, 19 Jan 2024 07:47:42 GMT

Hello,

I think this kind of automation works in shared space only.

Is there a workaround for managed space ?

thanks !

Re: How to export more than 100000 cells using the Get Straight Table Data block in Qlik Application Automation

paulcalvet — Tue, 23 Jan 2024 10:57:03 GMT

Hello @AfeefaTk

I try to do in different way and it works well.

The problem is it's very very long to export (52 minutes to export 16 000 lines) because it copy to file line by line.

Is there a way to improve performance like a bulk export ?

The goal is to allow users to automate their export data from corporate application to S3, and then they can use this dataset to build their own application in their shared space.

Thanks !

Re: How to export more than 100000 cells using the Get Straight Table Data block in Qlik Application Automation

random_user_3869 — Fri, 26 Jan 2024 14:51:38 GMT

Hello @AfeefaTk

Is it possible to export data to csv (as an output format).

Thank you

Regards

Re: How to export more than 100000 cells using the Get Straight Table Data block in Qlik Application Automation

Sonja_Bauernfeind — Thu, 01 Feb 2024 10:03:12 GMT

Hello @random_user_3869

The example in this article (including the one you can download) exports to .csv.

If you have a more generic question regarding available output formats for different blocks, please post about your requirement and question in the Qlik Application Automation forum.

All the best,
Sonja

Re: How to export more than 100000 cells using the Get Straight Table Data block in Qlik Application Automation

skustes — Tue, 05 Mar 2024 16:55:43 GMT

Hello @AfeefaTk, I'm trying to do an export of data that was input by system users in a textbox. Those text strings can contain double quotes, single quotes, and line breaks, like in this example with what should end up as two rows in Excel.

[["1","This string will break due to this line break and these "double quotes" and these 'single quotes'."],["2","This string is fine."]]

I understand why double quotes break the string. Unfortunately, so do single quotes and line breaks, resulting in the Excel output receiving no data. That means I can't replace double quotes with single quotes. Do you know of a way to safely sanitize text outputs to get around this issue?

The replace function doesn't seem to work for double or single quotes, however, the regexreplace function does. Nonetheless, I don't know what I can replace them with to maintain string readability that won't break the output.

Re: How to export more than 100000 cells using the Get Straight Table Data block in Qlik Application Automation

Napus — Wed, 02 Jul 2025 07:48:20 GMT

Hi @AfeefaTk ,

I'm trying to use this automation, My dataset has 10327 rows * 13 columns >> 134251 total cells. and I can't make it work. I got the same error: "Object data can't be bigger than 100.000 cells".

as I read in comments I also try to reduce the value of these variables ( vNLoops vRecordsEnd ) to 3000 but still doesn't work.

My second question is that I need to fetch the whole table and I don't need to filter my table. so can I just delete these following blocks >> Select Field Values By Query2,3 and vFieldName. if no can you tell me how I can set these variables?

Can you give me a hand with it?

Thanks

Re: How to export more than 100000 cells using the Get Straight Table Data block in Qlik Application Automation

AfeefaTk — Wed, 02 Jul 2025 09:51:22 GMT

@Napus

Could you please share the automation workspace and run logs?

Regarding your second question, with this approach, we are not filtering the actual straight table data, unless we apply a filter on the unique identifier to process the data in batches and export it to the destination.