(maybe) JSON to CSV

[PythonNewbster]

Hello All,

I am an ABSOLUTE beginner and I am about to ramp things up by taking the MIT Open Courseware course. I also have "Python Crash Course" at the ready. I have some Unix command-line skills and I have cobbled together some very ugly Perl scripts in the past. However, I am in a bind at the moment. I've searched all over the web for some examples that address my situation and some come close but not close enough.

I have, what is supposed to be JSON output, in a file that I need to loop through and print out to csv. There are some values in epoch time that I'd like to convert while I am at it. So, it seems that using the json library and getting these values in to a dictionary would be perfect. In my hands any library might as well be an ashtray. I am too embarrassed to even offer up what I have to this point.

The file essentially looks like this...
[
{
"primaryAdmin": "padmin",
"passwordExpiryDate": 1549837845656,
"loginName": "test1",
"loginLive": true,
"lastSetDate": 1486679445656,
"loginCat" : "TESTCAT",
"contactEmail": "test@test.com",
"genreName": "Trailblazer",
"secondaryAdmin": "sadmin",
"contactName": "Test Contact",
"lastLoginTime" : 1486765411722,
"peEnabled" : true,
},
{
"primaryAdmin": "padmin2",
"passwordExpiryDate": 1549837845656,
"loginName": "test2",
"loginLive": true,
"lastSetDate": 1486679445656,
"loginCat": "TESTCAT",
"contactEmail": "test2@test.com",
"genreName": "Trailblazer",
"secondaryAdmin": "sadmin2",
"contactName": "Test Contact2",
"lastLoginTime": 1486765411722,
"peEnabled" : true,
}
]

I don't know ANYTHING about JSON but from the examples I have seen, there is typically a string after that first open bracket. There is not in this case. It just jumps right in to the curly bracket. Is it possible that this makes it NOT-JSON?
At any rate, I just want to print all of the values from each line to file with the first line of output effectively being a header for all of the rows of just values underneath it. Converting the epoch values like "passwordExpiryDate","lastSetDate", and "lastLoginTime" would be gravy. I could just convert in excel but I might as well TRY to be elegant. ANY help here would be greatly appreciated.

-PythonNewbster

[buran]

Hello and welcome to the forum.
We are glad to help, but we don't do it for you. Note that you are in very good position to learn, because you have real project on your hands.
That said, note that this is not properly formatted JSON, because of the comma at the end of each "peEnabled" : true, . Unless you removed part of it that you don't want on public forum and forgot to remove the trailing comma. You can validate JSON online. e.g. here https://jsonlint.com/
https://jsonformatter.curiousconcept.com/

If it was/is properly formatted, you need to use json module to load the data into dict and then csv module to write it. For both you will need to learn working with file. to convert datetime, you will need datetime module.

I would suggest you start writing some code and post back when you have specific questions or error.

[PythonNewbster]

Sadly, the output does indeed have commas at the end of each line. Do I need to whack the commas before I can even think to handle it with the json library?
And thank you for pushing me to take this on. It is the only way I am going to learn.

[buran]

it's not all commas, just the ones before each closing }, e.g.

Output:
[
{
"primaryAdmin": "padmin",
"passwordExpiryDate": 1549837845656,
"loginName": "test1",
"loginLive": true,
"lastSetDate": 1486679445656,
"loginCat" : "TESTCAT",
"contactEmail": "[email protected]",
"genreName": "Trailblazer",
"secondaryAdmin": "sadmin",
"contactName": "Test Contact",
"lastLoginTime" : 1486765411722,
"peEnabled" : true
},
{
"primaryAdmin": "padmin2",
"passwordExpiryDate": 1549837845656,
"loginName": "test2",
"loginLive": true,
"lastSetDate": 1486679445656,
"loginCat": "TESTCAT",
"contactEmail": "[email protected]",
"genreName": "Trailblazer",
"secondaryAdmin": "sadmin2",
"contactName": "Test Contact2",
"lastLoginTime": 1486765411722,
"peEnabled" : true
}
]

in order to use json module you need to clean the file. depending on file size and your RAM you may do this in memory - i.e. read the file in a string, clean it and then load the string with json. If the file is BIG, you may have to reprocess the file.

[PythonNewbster]

So after some mucking around I was able to actually load the data with json.load and print everything out to screen, which makes me feel like I am close to being able to actually do something with the data.

import json

fileName =“me.json”

with open(fileName, “r”) as fileHandler:
    data = json.load(fileHandler)

print (data)

I am struggling with only printing values. I keep getting a type error of list indices must be integers or slices, not str. Any advice? Is my output a list when it should be something else?

[nilamo]

Without seeing the actual error, or the code you're using, or the output you're getting... we'd just be guessing.

[buran]

data is list of dicts, so you can retrieve first element with data[0]. and values from first element dict with data[0]['primaryAdmin'] - example for primaryAdmin key

[PythonNewbster]

Thank you Buran!!! Is there an elegant way to print out the values on one line. I went with this to start...

for dicts in data:
    for key in dicts:
        print (dicts[key])

Weak. It did work but I would like the values for each dict on one line, separated by commas. This put each individual value on a line.

Should I be looking to use the CSV module at this point?

[nilamo]

Why do you keep bringing up the csv module?  You're not working with a csv file, so the module does nothing for you.  Just because you want to print some data to the screen with commas added?

You could do this in a few ways.  Right now, what you have is right, except you want all the items on the same line.  So, this would work:

for dicts in data:
    first = True
    for key in dicts:
        if not first:
            print(", ", end="")
        first = False
        print(dicts[key], end="")
    print()

Or, if you want to be clever, you can use the str.join() method and the dict.values() method to turn all that into just one line:

>>> items = {"foo": "bar", "spam": "eggs"}
>>> print(", ".join(items.values()))
eggs, bar

[PythonNewbster]

It was suggested in an an earlier post that I use CSV. I am clearly in no position to argue with anybody here. Just riding this out with all the set-backs and enjoying the mini successes.