write json output to a csv file

​@DanC25 You will use the Logging: Log text to file action. This will write data to virtually any type of file. It will not change the data from raw variable values to JSON, but we have other ways of making that happen too.   :)

https://docs.automationanywhere.com/bundle/enterprise-v2019/page/enterprise-cloud/topics/aae-client/bot-creator/commands/cloud-log-to-file-command.html

​@Aaron.Gleason 

I tried “Log to File”, but we prefer CSV since it’s easier to work with and manipulate later.

I also thought about using the Python script approach with a wrapper function, but the JSON output has 4+ fields/variables we need. The problem is the function argument dropdown only lets me pass one variable — but we need to pass multiple. How should we handle that? thanks

​@DanC25 To answer your second question, you can pass a List variable with multiple values to Python, which is then treated as an array.

Passing a List with these values: “Aaron”, “Texas”, “867-5309”...

def myFunction(x):
    return x[0]

…will return “Aaron”.   (without quotes)

​@Aaron.Gleason 

Great, it works with $sjobidn$, $sjobStatus$, $sAgent$, $sType$, $subclientname$.

How do I add column headers when using “Log text to file”? Thanks! I’ll try the second approach later.

'JobId' : $sjobidn$ , 
'BackupStatus': $sjobStatus$, 
'Agent': $sAgent$, 
'BackupType': $sType$, 
'Subclient': $subclientname$  


 

​@DanC25 Get ready for it.

Add a Log text to file action at the beginning of your automation with your headers:

Job ID,Backup Status,Agent,Backup Type,Sub-Client

Sometimes it’s the easiest things we miss. 😅

Don’t feel bad -- when I first joined Automation Anywhere, I had to ask how to add a blank line. No, it’s not \n, \r, %0d or anything… just press ENTER.

​@Aaron.Gleason 

Cool !

But every time we generate a new file, we want the filename to follow this format:

backupSummary_ddmmyyyy_hhmm

How can we auto-generate/create the filename we want every time the bot runs?
We need it to follow above naming convention.

thanks

​@DanC25 Okay, this one is a two parter: First, we have to make a date. Then we have to concatenate everything together. There are no operators for concatenation -- just put the strings and/or variables together. (That was another thing I had to ask!)  😅

And there you go. A custom date for your output filename in the string variable.

​@Aaron.Gleason 

Awesome, you rock! All good now — thanks!
 

​@DanC25,

You can use a Python script to convert JSON to CSV. The script takes two arguments: the JSON file path and the CSV file path. Based on these inputs, the bot will create the CSV file (or overwrite it if it already exists).

The bot works by reading the keys in the JSON and creating corresponding columns in the CSV. It then writes the data for each node into those columns.

Additionally, you can define a schema in the script if you want to include only specific columns in the CSV, or if you want to enforce strict column names and order.

 

import json

import csv

import os

import re

from collections import OrderedDict

def jsontocsv(argIn):

    try:

        json_path = os.path.abspath(argIn[0])

        csv_path = os.path.abspath(argIn[1])

        with open(json_path, 'r', encoding='latin-1') as f:

            raw = f.read()

        match = re.search(r'"list"\s*:\s*(

\[[\s\S]*?\]

)', raw)

        if not match:

            return

        list_content = match.group(1)

        try:

            records = json.loads(list_content)

        except json.JSONDecodeError:

            return

        if not isinstance(records, list) or not records:

            return

        all_keys = list(OrderedDict.fromkeys(k for item in records for k in item.keys()))

        with open(csv_path, 'w', newline='', encoding='utf-8') as f:

            writer = csv.DictWriter(f, fieldnames=all_keys)

            writer.writeheader()

            for item in records:

                writer.writerow(item)

    except:

        return

Thanks.

​@Om Keskar There are an infinite number of ways to solve this problem. Your Python script isn't formatted the way that Automation Anywhere needs it. It must always be in a function format, and we only support one variable at a time being passed to the function. 

It's good to have another suggestion like this, but build it and test it to ensure it works. That's why I add screen shots to my examples -- to show that I tested it and it works.