Convert JSON files to CSV files using Python (Idle)

Question

This question piggybacks a question I had posted yesterday. I actually got my code to work fine. I was starting small. I switched out the JSON in the Python code for multiple JSON files outside of the Python code. I actually got that to work beautifully. And then there was some sort of catastrophe, and my code was lost.

I have spent several hours trying to recreate it to no avail. I am actually using arcpy (ArcGIS's Python module) since I will later on be using it to perform some spatial analysis, but I don't think you need to know much about arcpy to help me out with this part (I don't think, but it may help).

Here is one version of my latest attempts, but it is not working. I switched out my actual path to just "Pathname." I actually have everything working up until the point when I try to populate the rows in the CSV (which are of latitude and longitude values. It is successfully writing the latitude/longitude headers in the CSV files). So apparently whatever is below dict_writer.writerows(openJSONfile) is not working:

import json, csv, arcpy
from arcpy import env

arcpy.env.workspace = r"C:\GIS\1GIS_DATA\Pathname"

workspaces = arcpy.ListWorkspaces("*", "Folder")
for workspace in workspaces:

    arcpy.env.workspace = workspace
    JSONfiles = arcpy.ListFiles("*.json")

    for JSONfile in JSONfiles:

        descJSONfile = arcpy.Describe(JSONfile)
        JSONfileName = descJSONfile.baseName

        openJSONfile = open(JSONfile, "wb+")
        print "JSON file is open"

        fieldnames = ['longitude', 'latitude']
        with open(JSONfileName+"test.csv", "wb+") as f:
            dict_writer = csv.DictWriter(f, fieldnames=fieldnames)
            dict_writer.writerow(dict(zip(fieldnames, fieldnames)))
            dict_writer.writerows(openJSONfile)

        #Do I have to open the CSV files? Aren't they already open?
        #openCSVfile = open(CSVfile, "r+")

    for row in openJSONfile:
         f.writerow( [row['longitude'], row['latitude']] )

Any help is greatly appreciated!!

What actually doesn't work? That is, is it crashing? Are you expecting it to do X but actually it does Y? It is doing nothing? — Katriel, Dec 13 '12 at 13:41
Use the `writeheader()` method of the `dict_writer` to write the header, then use its `writerow()` to write the rows. Each row will have to be a dictionary of fieldnames mapped their values. — martineau, Dec 13 '12 at 13:49
@katrielalex The very last line of my explanation above says what is not working: that, although the code IS successfully populating the headers in the CSV file, it is not populating the actual values from the JSON files into the CSV's rows (below the headers). So I am getting CSVs that look like this: Latitude, Longitude (but no values below). — Kristen G., Dec 13 '12 at 13:49
@martineau Would you mind rewriting and pasting that block of code for me so I can see what you mean? I'm a newbie so I'm not exactly sure where to put the code you're suggesting. — Kristen G., Dec 13 '12 at 13:57

stranac · Answer 1 · 2012-12-13T13:55:12.157

0

You're not actually loading the JSON file.
You're trying to write rows from an open file instead of writing rows from json.

You will need to add something like this:

rows = json.load(openJSONfile)

and later:

dict_writer.writerows(rows)

The last two lines you have should be removed, since all the csv writing is done before you reach them, and they are outside of the loop, so they would only work for the last file anyway(they don't write anything, since there are no lines left in the file at that point).

Also, I see you're using with open... to open the csv file, but not the json file.
You should always use it rather than using open() without the with statement.

edited Dec 13 '12 at 13:55

answered Dec 13 '12 at 13:49

stranac

26,638
5
25
30

I have tried that :( When I add `rows = json.loads(openJSONfile)` above the line `fieldnames = ['longitude', 'latitude']` I get the following error: `TypeError: expected string or buffer` – Kristen G. Dec 13 '12 at 13:53
Oh, sorry, it should be `json.load()`. – stranac Dec 13 '12 at 13:55
Would I write something like `with open(JSONfile, "wb+") as openJSONfile:` ? When I do, I get the error: `"No JSON object could be decoded"` (there's nothing indented after the colon... not sure what to put there). – Kristen G. Dec 13 '12 at 14:06
with just `json.load(openJSONfile)` I'm getting the `"No JSON object could be decoded"`error – Kristen G. Dec 13 '12 at 14:10

score 0 · Answer 2 · answered Dec 13 '12 at 14:16

0

You should use a csv.DictWriter object to do everything. Here's something similar to your code with all the Arc stuff removed because I don't have it, that worked when I tested it:

import json, csv

JSONfiles = ['sample.json']

for JSONfile in JSONfiles:

    with open(JSONfile, "rb") as openJSONfile:
        rows = json.load(openJSONfile)

    fieldnames = ['longitude', 'latitude']
    with open(JSONfile+"test.csv", "wb") as f:
        dict_writer = csv.DictWriter(f, fieldnames=fieldnames)
        dict_writer.writeheader()
        dict_writer.writerows(rows)

It was unnecessary to write out each row because your json file was a list of row dictionaries (assuming it was what you had embedded in your linked question).

answered Dec 13 '12 at 14:16

martineau

119,623
25
170
301

now I'm receiving this error: `IOError: [Errno 2] No such file or directory: u'Bus02_00_00_LP.json'` (that's the name of my first json file) – Kristen G. Dec 13 '12 at 15:02
I tried `print os.path.isfile(r"C:\GIS\1GIS_DATA\MyPath\FileName.json")` and got `True`, so it's there... – Kristen G. Dec 13 '12 at 15:24
Opening the file has nothing to do with the processing I'm suggesting which assumes the file can be opened normally. – martineau Dec 13 '12 at 15:43
your help is greatly appreciated. do you have any idea why I'm getting the error that there is no such file or directory after the lines `with open(JSONfile, "rb") as openJSONfile:` and `rows = json.load(openJSONfile)` ? I'm searching everywhere... nothing. – Kristen G. Dec 13 '12 at 15:53
Can you add a copy of the traceback from when it occurs to your question. This should also show exactly what line it's on when the error occurs. – martineau Dec 13 '12 at 16:19

score 0 · Answer 3 · answered Dec 13 '12 at 16:23

I can't say I know for sure what was wrong, but putting all of the .JSON files in the same folder as my code (and changing my code appropriately) works. I will have to keep investigating why, when trying to read into other folders, it gives me the error:

IOError: [Errno 2] No such file or directory:

For now, the following code DOES work :)

import json, csv, arcpy, os
from arcpy import env

arcpy.env.workspace = r"C:\GIS\1GIS_DATA\MyFolder"

JSONfiles = arcpy.ListFiles("*.json")
print JSONfiles

for JSONfile in JSONfiles:
    print "Current JSON file is: " + JSONfile

    descJSONfile = arcpy.Describe(JSONfile)
    JSONfileName = descJSONfile.baseName

    with open(JSONfile, "rb") as openJSONfile:
        rows = json.load(openJSONfile)      
        print "JSON file is loaded"

    fieldnames = ['longitude', 'latitude']
    with open(JSONfileName+"test.csv", "wb") as f:
        dict_writer = csv.DictWriter(f, fieldnames = fieldnames)
        dict_writer.writerow(dict(zip(fieldnames, fieldnames)))
        dict_writer.writerows(rows)
        print "CSVs are Populated with headers and rows from JSON file.", '\n'

Thanks everyone for your help.

I suspect is has something to do with the value of `JSONfileName`. Not sure why the name of the folder would matter, though. Why do you use `JSONfileName` instead of just `JSONfile`? — martineau, Dec 13 '12 at 16:31

Convert JSON *files* to CSV *files* using Python (Idle)

3 Answers3

Convert JSON files to CSV files using Python (Idle)