topic Re: QAA Update Excel rows from Table Data in Qlik Automate

QAA Update Excel rows from Table Data

jheasley — Tue, 04 Jan 2022 18:39:33 GMT

Can anyone provide support on how to use the Get Table Data block and write that to an excel worksheet? i have the attached Automation that can create an excel file from a blank template, but i am having trouble getting the straight table data to write out. interestingly, if you just read other excel data, this Automation works fine. There are also some steps i need to work out for determining how much data is present, but one step at a time.

@Carl_Hunter i think this applies to a comment i saw you make as well.

Re: QAA Update Excel rows from Table Data

Carl_Hunter — Tue, 04 Jan 2022 20:16:31 GMT

Hey @jheasley yes that was me, I posted on the Excel connector/block knowledgebase article - no reply yet!

FYI @MLaverick its similar to what we were trying to figure out!

Re: QAA Update Excel rows from Table Data

MLaverick — Wed, 05 Jan 2022 08:58:06 GMT

Hi both. Yes, I'm hitting a brick wall with this too. There's no documentation on the Excel blocks at all which is worrying.

Re: QAA Update Excel rows from Table Data

MarkGeurtsen — Wed, 05 Jan 2022 10:26:15 GMT

Hi @jheasley , @Carl_Hunter , @MLaverick ,

The issue here is probably with the format the row is provided in. Get straight table data returns each row as a json object as key value pairs. e.g. { "mykey" : "value", "header2" : "another_value" }.

The block Add Row to Excel Table should receive an array containing values. The attached automation creates a variable "row" in which values of the objects are being added and subsequently this row is written to the table.

Let me know if this helps!

Re: QAA Update Excel rows from Table Data

MLaverick — Wed, 05 Jan 2022 10:59:45 GMT

I'll give this a go and let you know how it goes. It does seem a bit overkill to have to put the data into a variable and then add it line by line. Qlik dashboards have the capability to export to excel so i had hoped that feature would be available via automations - simply dump all straight table data into an Excel worksheet. Do you know why this is not possible? Thanks.

Re: QAA Update Excel rows from Table Data

jheasley — Wed, 05 Jan 2022 11:42:41 GMT

Ill give this a try. sounds like a block needs to be build to convert table output for different formats

Convert Table Data:

Input: Get Straight Table Block

output format:

- excel (worksheet)
- excel (table)
- google sheet
- csv

Alternatively, this could be added to the settings of the Get Straight Table

Re: QAA Update Excel rows from Table Data

jheasley — Wed, 05 Jan 2022 12:13:39 GMT

so took your example and modified it work with writing to a worksheet rather than a table, and got it working, but this isnt really a scalable solution. iterating through each row is fine if there are maybe 100-200 rows, but if i want to use this to generate a report it is just too slow. ideal solution would be the ability to output from the Get Straight Table Data block in the format required for Excel Sheets, Excel Tables, Google Sheets, or CSV. from there, the flexibility would exist to keep going. Additionally, the output of the straight table data should include the number of rows and columns as selectable values in later blocks.

Re: QAA Update Excel rows from Table Data

MarkGeurtsen — Wed, 05 Jan 2022 12:19:05 GMT

Right now this is not possible, but thanks for the suggestion!

We will explore options to make it easier to export this data to other platforms as it's a common usecase.

Re: QAA Update Excel rows from Table Data

MarkGeurtsen — Thu, 06 Jan 2022 11:04:56 GMT

@jheasley This might be a better way of doing it with larger amounts of data and scales better. It requires some custom code.

See attachment.

Re: QAA Update Excel rows from Table Data

karin_B_56 — Thu, 12 May 2022 10:43:46 GMT

I found a rootcause using get straight table in Table data block where I tried to get the output of a comment field

sometimes it worked sometimes it didn't . The solution was to check what I had in the comment field and then I used: =purgechar([Comment],'",\',) as a filter in my expression. This worked out and I was able to get all comments into the excel sheet.

Re: QAA Update Excel rows from Table Data

aares — Fri, 07 Jun 2024 08:01:15 GMT

Hi,

Would it be posible to add a new table to the report? I tried it but I didn't get any result.