json output data in specified format in excel or csv

Question

I'm extracting each user's data into m4-$u.json file with the below shell script

#!/bin/bash

USERID=ricardo.sanchez
PASSWORD=password
PORT=2728

for u in `cat user-list.txt`;
   do echo $u; 
   curl --user $USERID:$PASSWORD http://198.98.99.12:46567/$PORT/protects/$u | jq '.' > m4-$u.json
done

One for the user's output m4-daniel.json file. From that few lines as follows.

[
  {
    "depotFile": "//ABND/JJEB/...",
    "host": "*",
    "isgroup": "",
    "line": "16",
    "perm": "open",
    "user": "5G_USER_GROUP"
  },
  {
    "depotFile": "//LIB/...",
    "host": "*",
    "isgroup": "",
    "line": "19",
    "perm": "write",
    "user": "6G_USER_GROUP"
  },
  {
    "depotFile": "//AND/RIO/...",
    "host": "*",
    "isgroup": "",
    "line": "20",
    "perm": "write",
    "user": "AND_USER_GROUP"
  },

Now from the json output files, I need covert to get it in excel (or) csv in below format. Additionally $PORT & $U shell variables need to be present in the excel (or) csv file. Any help will be appreciated.

balderman · Accepted Answer · 2021-08-05 07:55:28Z

1

See below (a csv file is generated). Note that there is no $PORT and $U in the data.

data = [
    {
        "depotFile": "//ABND/JJEB/...",
        "host": "*",
        "isgroup": "",
        "line": "16",
        "perm": "open",
        "user": "5G_USER_GROUP"
    },
    {
        "depotFile": "//LIB/...",
        "host": "*",
        "isgroup": "",
        "line": "19",
        "perm": "write",
        "user": "6G_USER_GROUP"
    },
    {
        "depotFile": "//AND/RIO/...",
        "host": "*",
        "isgroup": "",
        "line": "20",
        "perm": "write",
        "user": "AND_USER_GROUP"
    }
]
with open('out.csv', 'w') as f:
    headers = list(data[0].keys())
    f.write(','.join(headers) + '\n')
    for entry in data:
        tmp = []
        for field in headers:
            tmp.append(entry[field])
        f.write(','.join(tmp) + '\n')

out.csv

depotFile,host,isgroup,line,perm,user
//ABND/JJEB/...,*,,16,open,5G_USER_GROUP
//LIB/...,*,,19,write,6G_USER_GROUP
//AND/RIO/...,*,,20,write,AND_USER_GROUP

answered Aug 5, 2021 at 7:55

balderman

24k8 gold badges39 silver badges60 bronze badges

Sign up to request clarification or add additional context in comments.

16 Comments

balderman Over a year ago

Did you try to run my code "as is" and got this error? Dont call the file csv - it is a module in python. Call it json_to_csv.py. You need to modify the code and load the json created by the shell script into data.

balderman Over a year ago

see how you load a json file into a list of dicts. see programiz.com/python-programming/json

user4948798 Over a year ago

added below whereas no repose, could you please help me with this. import json import pandas as pd with open('*.json') as json_file: ` data = json.load(json_file)` df = pd.DataFrame(data)

balderman Over a year ago

no need for pandas. just load the file into a list of dicts. see the example in the link

user4948798 Over a year ago

Now instead of output in txt changed to json ok will try with the link.

|

Collectives™ on Stack Overflow

json output data in specified format in excel or csv

1 Answer 1

16 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

16 Comments

Your Answer

Sign up or log in

Post as a guest

Related