QAA Update Excel rows from Table Data

[jheasley]

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. 

[Carl_Hunter]

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]

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 · Accepted solution]

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]

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]

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]

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]

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]

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

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.