Qlik Community

Qlik Application Automation

Announcements
June 28, 10AM ET: Qlik Nation and Qlik Community present: CyberSleuth REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
jheasley
Luminary Alumni
Luminary Alumni

QAA Update Excel rows from Table Data

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. 

 

Labels (3)
1 Solution

Accepted Solutions
MarkGeurtsen
Support
Support

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!

View solution in original post

9 Replies
Carl_Hunter
Partner
Partner

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! 

MLaverick
Luminary
Luminary

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.

MarkGeurtsen
Support
Support

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!

MLaverick
Luminary
Luminary

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.

jheasley
Luminary Alumni
Luminary Alumni
Author

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 

jheasley
Luminary Alumni
Luminary Alumni
Author

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.  

MarkGeurtsen
Support
Support

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.

MarkGeurtsen
Support
Support

@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.

karin_B_56
Employee
Employee

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.