skip to Main Content

Hoping that I may be able to get some assistance in regards to my code. I have spent the last couple of days crawling the site to try and find a response that would fit the requirements and while I found some excellent information, I am still struggling to implement a working version.

I have taken my code from this thread.

As the title suggests I am trying to take a nested JSON file received from a Shopify Webhook, flatten it and convert it to a CSV.

This is my JSON file:

{"id":788032119674292922,"title":"Example T-Shirt","body_html":null,"vendor":"Acme","product_type":"Shirts","created_at":null,"handle":"example-t-shirt","updated_at":null,"published_at":"2018-01-23T03:11:38-05:00","template_suffix":null,"published_scope":"global","tags":"mens t-shirt example","variants":[{"id":642667041472713922,"product_id":788032119674292922,"title":"","price":"19.99","sku":"example-shirt-s","position":0,"inventory_policy":"deny","compare_at_price":"24.99","fulfillment_service":"manual","inventory_management":null,"option1":"Small","option2":null,"option3":null,"created_at":null,"updated_at":null,"taxable":true,"barcode":null,"grams":200,"image_id":null,"inventory_quantity":75,"weight":200.0,"weight_unit":"g","inventory_item_id":null,"old_inventory_quantity":75,"requires_shipping":true},{"id":757650484644203962,"product_id":788032119674292922,"title":"","price":"19.99","sku":"example-shirt-m","position":0,"inventory_policy":"deny","compare_at_price":"24.99","fulfillment_service":"manual","inventory_management":"shopify","option1":"Medium","option2":null,"option3":null,"created_at":null,"updated_at":null,"taxable":true,"barcode":null,"grams":200,"image_id":null,"inventory_quantity":50,"weight":200.0,"weight_unit":"g","inventory_item_id":null,"old_inventory_quantity":50,"requires_shipping":true}],"options":[{"id":527050010214937811,"product_id":null,"name":"Title","position":1,"values":["Small","Medium"]}],"images":[{"id":539438707724640965,"product_id":788032119674292922,"position":0,"created_at":null,"updated_at":null,"width":323,"height":434,"src":"//cdn.shopify.com/s/assets/shopify_shirt-39bb555874ecaeed0a1170417d58bbcf792f7ceb56acfe758384f788710ba635.png","variant_ids":[]}],"image":null}

This is my code:

    import csv
    import json

    with open('product.json') as file:
         x = json.load(file)
        print x

    f = csv.writer(open("test.csv", "wb+"))

    f.writerow(["id", "title", "option1", "grams"])

    for x in x:
        f.writerow([x["id"],
                    x["title"],
                    x["variants"]["option1"],
                    x["variants"]["grams"]])

When running this code, I get the following error:

Traceback (most recent call last):
  File "parser5.py", line 17, in <module>
    f.writerow([x["id"],
 TypeError: string indices must be integers

I believe that the issue is to do with the fact that there is not an index reference ‘[0]’, though I am unsure how to assign the key correctly and have failed in all of my attempts to do so.

I am fairly new to Python, I have tried to read as many posts on the site to try not to duplicate the question – apologies if this was in vain.

The desired output that I am looking for from the file would be something like this:

id,title,option1,grams
788032119674292922,Example T-Shirt, Small, 200
null,null,Medium,200

Any help or guidance that you may be able to offer would be greatly appreciated.

3

Answers


  1. Chosen as BEST ANSWER

    Given the advice - this is the code that has worked for me on this problem:

    import csv
    import json
    
    with open('product.json') as file:
    x = json.load(file)
    print x
    
    f = csv.writer(open("test.csv", "wb+"))
    
    f.writerow(["url", "sku", "parent_id", "title", "vendor", "product type", 
    "tags", "body_html", "image"])
    
    f.writerow([x["handle"], x["id"],
        "",
        x["title"],
        x["vendor"],
        x["product_type"],
        x["tags"],
        x["body_html"],
        x["image"]])
    
    for item in x["variants"]:
        f.writerow(["",item["sku"], x["id"],x["title"],"","","","",
        item["option1"]])
    

  2. your json file contains a single dict(after json.load),
    making this line for x in x: unnecessary, unless your file contains many lines like the one you provided

    the other issue you have is the way you access x["variants"]["option1"]
    because x["variants"] returns a list of dicts! so your foreach should be there:

    for item in x["variants"]:
        item["option1"]
        #etc...
    
    Login or Signup to reply.
  3. I think it would be easier to parse the JSON first and then use the csv writer as follows (please note I just googled this since I was doing something similar and this solution worked for me). I am pasting the example from the site I found, link to source bellow 🙂

    import json
    import csv
    employee_parsed = json.loads(employee_data)
    emp_data = employee_parsed['employee_details']
    # open a file for writing
    employ_data = open('/tmp/EmployData.csv', 'w')
    # create the csv writer object
    csvwriter = csv.writer(employ_data)
    count = 0
    for emp in emp_data:
          if count == 0:
                 header = emp.keys()
                 csvwriter.writerow(header)
                 count += 1
          csvwriter.writerow(emp.values())
    employ_data.close()
    

    Source: http://blog.appliedinformaticsinc.com/how-to-parse-and-convert-json-to-csv-using-python/

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search