How to: convert JSON lists to CSV in Qlik Application Automation

[Emile_Koslowski]

This article explains how to convert a JSON list to a CSV file or string using Qlik Application Automation. 

Content

1. Converting a JSON list to a CSV file
2. Converting a JSON list to a CSV string
3. Exceptions and edge cases
   1. Missing keys across objects
   2. Nested lists
   3. Empty nested lists
   4. Special characters

The first two examples will use a "basic" JSON list. This means that every object in the list must contain all possible keys, even if the value for that key is null. In the 3rd part of this article, we'll cover lists that contain objects with varying keys.

Converting a JSON list to a CSV file

This example will convert a JSON list to a CSV file on Amazon S3. Every object in the list must contain all possible keys, even if the value for that key is null.

Steps:

1. Add a Create File block from the Cloud Storage connector and configure it to use Amazon S3.
   
   
2. Add a block that has a list of objects as output. In this example, we'll use the Get Straight Table Data block.
    
   
3. Add a Write Line To File block from the Cloud Storage connector inside the loop created by the Get Straight Table Data block, and configure it as follows:
   1. File -> the file from the Create File block
   2. Data -> current item in the loop from the Get Straight Table Data block
   3. Mode -> CSV
   4. Delimiter -> , (feel free to change this)
   5. Column names -> two options:
      1. Hard code to a comma-separated list of column names (these must be the same as the object's keys in the source list). Use this approach if not all objects contain the same keys or if you don’t want to include all keys in the CSV file.
      2. Set to the current item in the loop from the Get Straight Table Data block and apply the Get Keys formula. Then add the Implode formula on top of that to transform the list of keys into a comma-separated list.
         
         
         
         Tip: If your list contains nested lists (see the Nested lists part at the end of this article), then add a Flatten formula first before applying the CSV formula.
4. Add a Save And Close File block to save the CSV file to Amazon S3. You can now execute this automation to create the CSV file on Amazon S3.
   
   
   
   You’ll find an exported example of this automation at the end of this article as convert_json_list_to_csv_file.json.

Converting a basic JSON list to a CSV string

This example will convert a non-nested JSON list to a CSV string.

Steps:

1. Add a block that has a list of objects as output. In this example, we'll use the Get Straight Table Data block.
    
   
   
   
2. Create an empty variable "CSV" of type List.
   
   
3. Get the keys of the objects in the list and store them as a comma-separated list as the first string item in the CSV variable from the previous step. This will function as the headers for the CSV file. There are 2 ways to get the keys:
   
   
   1. Use the output from the Get Straight Table Data block as input for the Get Keys formula. This will output a list of all header names used in the first item of the list. Then add the Implode formula on top of that to convert the list of keys to a comma-separated string. This is the easiest but will only work if the first object in the list contains all keys.
      
      
      
   2. If the keys can vary from object to object, then it's best to not use the Get Keys formula but to hard code all possible keys as the first item in the CSV variable.
      
      
4. Add a Loop block and configure it to loop over the output of the Get Straight Table Data block. You could also add a new Get Straight Table Data block and use the loop included in that block but this approach will spare you an additional API call and make the automation more performant for bigger tables.
   
   
5. Add a new Variable block inside the Loop block's loop and configure it to add the current item in the loop to the CSV variable. Then add the CSV formula on top of the mapping to the current item.
   
   Tip: If your list contains nested lists (see the Nested List part at the end of this article) then add a Flatten formula first, before applying the CSV formula.
   
   
6. Use the Implode formula to transform the CSV list into a CSV string. In this example we're using the Output block to show the contents of the CSV string, but feel free to use this as input for another block. Make sure to specify the Linebreak formula as the delimiter in the Implode formula.
   
   
   
   
   
   
7. That's it. See the below image for an example of a generated CSV string in the automation's output.
   
   

You’ll find an exported example of this automation at the end of this article as convert_json_list_to_csv_string.json. 

Exceptions

Nested Lists.

By nested lists, we mean lists that contain keys that have another list as a value. See the example below:

[
{
id: 1,
name: "Alice",
roles: ["admin", "write", "read"]
},
{
id: 2,
name: "Bob",
roles: ["read"]
}
]

Before we can use such a list to generate a CSV data structure (file or string), we'll need to flatten every object in the list before we apply the CSV formula or before we write it to a file.

Apply the flatten formula on every object individually instead of doing it for the full list. Applying the Flatten formula on the first object of the above example lists gives the following result:

{
  "id": 1,
  "name": "Alice",
  "roles.0": "admin",
  "roles.1": "write",
  "roles.2": "read"
}

Note that the key names are now different, which will impact the CSV headers. Make sure you update the CSV formulas accordingly.

Empty nested lists

In the JSON structure below, the roles key has an empty list assigned as the value. This is what we call an empty nested list:

[
{
id: 1,
name: "Alice",
roles: []
}
]

If the Flatten formula is applied to the above item in the list, the following result is returned:

{
  "id": 1,
  "name": "Alice",
  "roles": []
}

If the above object is used as input for the CSV formula, the object is tranformed to the following string:

id,name,roles
1,Alice,Array

While the following would be more accurate:

id,name,roles
1,Alice,

There are multiple approaches on achieving this: you could treat the object as a JSON string and remove all occurrences of [], or you could also use the ‘Remove empty properties’ formula from the formula picker. Apply this formula between the Flatten formula and the CSV formula. Note that this formula will remove both the key and value for the empty property. This influences the output of the CSV formula which means the headers must be specified or else the missing value is ignored.

All formulas together should now look like this:

Special characters

The CSV formula can use different delimiters to separate strings. Use this functionality to handle special characters. For example, if you know your values contain commas, use a different delimiter than the comma symbol, for instance, a pipe symbol |.

See basic_exceptions_example.json at the beginning of this article for an example of these edge cases.

The information in this article is provided as-is and to be used at own discretion. Depending on tool(s) used, customization(s), and/or other factors ongoing support on the solution below may not be provided by Qlik Support.