• Documentation
  • Workato Blog
  • Product Blog
  • Return to Workato
  • Login
  • Automation Institute
  • Documentation
  • Community Forums
New Topic
Discussions Recipe Building Recipe Building Questions
S

Saul Macht

started a topic over 3 years ago

Convert list or array data with nested objects and fields to csv

I have a list of data records that contain nested objects and fields. I want to convert the data to a csv. I also want to filter the records based on certain fields to make sure only the relevant data is included in the csv. 


How can I achieve this?


Amanda Wong

said over 3 years ago

Hi Saul,


There are a few ways to go about it. 


1) Loop through each record from source list and add filtered records to a Workato list. This list of filtered records can then be used to create csv using "CSV by Workato" or "Collection by Workato".

  1. Create an empty list to store filtered records. 
  2. Iterate through the list of records using repeat action, use conditional actions to filter records
  3. Add filtered records to the empty list created in (i)
  4. Using the list from (i), create csv using CSV by Workato or Collection by Workato (use the latter for large data sets >50k lines)


2) Using Workato formula mode, filter records and select relevant fields for CSV. You can do nested filter and nested selection of fields.

Recipe example: 

  1. On your array/list of records, apply formula .where to filter records by field values (supports filtering by nested object fields). Note that .where returns an array of records matching criteria
  2. Append resulting formula string from (i) with formula .pluck to select relevant fields for inclusion in data output. Note that .pluck returns an array of records containing an array of "plucked" fields.
  3. Append resulting formula string from (ii) with formula .to_csv to convert output (array of arrays) into csv string


3) Using Collection by Workato, create a temporary data table to store source list records, then write SQL queries to filter records. You will need to flatten the data structure.

  1. Add source data to Collection tables using one of the actions available: create from list, create from csv, query lists (write custom query)
  2. Flatten data structure using one of above methods i.e. compile in a Workato list or create csv string using formula


Check this recipe to see the above 3 methods in action: https://www.workato.com/recipes/991259


Let us know if it works for you!


1 person likes this
Login to post a comment

Still can't find your solution?

Send us a ticket, we will try our best to assist you with your problem

Documentation
Developer's Library
Tutorials
eBooks
Product Hour
Product Blog
Workato Blog
Product updates
Customer Stories
© Workato 2022   Privacy   Terms   +1 (844) 469-6752
  • Documentation
  • Workato Blog
  • Product Blog
  • Solutions
  • Forums
  • Tickets
  • Log in
  • Return to Workato
Topic views count