Parse Json data to Excel

Question

I have data in Json format availaible on this link: Json Data

What would be the best way to get this done? I know this could be done by Python but not sure how.

What is your question ? How to get data in Python from an URL ? How to parse a json ? How to create an excel document in python ? Please, be more specific. — Blusky, May 13 '15 at 10:59
You can use tablib if you want to write Json data to an Xls file. [Tablib](https://pypi.python.org/pypi/tablib/0.9.3) — Vaulstein, May 13 '15 at 11:11
http://stackoverflow.com/questions/1871524/convert-from-json-to-csv-using-python — jezrael, May 13 '15 at 11:14
@Blusky I am looking to parse the json data and and move it to an excel document. The json data is avalaible in the web browser as show in the link. — Tauseef Hussain, May 13 '15 at 12:45
In these days there is sophisticated tool for everything. If you want to simply automate your process of having excel sheets to represent your json data, you can use something thats already out there. For example https://www.ipushpull.com — Tomas, Jun 25 '15 at 09:32

Vivek Sable · Accepted Answer · 2015-05-13T16:05:44.310

Use urllib module to fetch details from the url.

import urllib

url = "http://www.omdbapi.com/?t=UN%20HOMME%20ID%C3%89AL"
res = urllib.urlopen(url)
print  res.code
data = res.read()

Parse data to JSON by json module.

import json
data1 = json.loads(data)

Use xlwt module to create xls file.

data =  {"Title":"Un homme idÃ©al","Year":"2015","Rated":"N/A",\
         "Released":"18 Mar 2015","Runtime":"97 min","Genre":"Thriller",\
         "Director":"Yann Gozlan","Writer":"Yann Gozlan, Guillaume Lemans, GrÃ©goire Vigneron",\
         "Actors":"Pierre Niney, Ana Girardot, AndrÃ© Marcon, Valeria Cavalli",\
         "Plot":"N/A","Language":"French","Country":"France","Awards":"N/A",\
         "Poster":"N/A","Metascore":"N/A","imdbRating":"6.3","imdbVotes":"214",\
         "imdbID":"tt4058500","Type":"movie","Response":"True"}

import xlwt
book = xlwt.Workbook(encoding="utf-8") 
sheet1 = book.add_sheet("AssetsReport0")

colunm_count = 0
for title, value in data.iteritems():
    sheet1.write(0, colunm_count, title)
    sheet1.write(1, colunm_count, value)
    colunm_count += 1

file_name = "test.xls"%()
book.save(file_name)

Get URL from User.

By Command Line Argument:

Use sys.argv to get arguments passed from the command.

Demo:

import sys
print "Arguments:", sys.argv

Output:

vivek:~/workspace/vtestproject/study$ python polydict.py arg1 arg2 arg3
Arguments: ['polydict.py', 'arg1', 'arg2', 'arg3']

By Raw_input() /input() method

Demo:

>>> url = raw_input("Enter url:-")
Enter url:-www.google.com
>>> url
'www.google.com'
>>>

Note:

Use raw_input() for Python 2.x

Use input for Python 3.x

Works perfect thank you. Also id there a way that i could dynamically change the URL? As in not having to edit the code everytime i am seraching a new URL. — Tauseef Hussain, May 13 '15 at 13:44
@TauseefHussain: Welcome, we can get url from the command line or by user. I will update answer according to take url from the User. — Vivek Sable, May 13 '15 at 15:55

score 3 · Answer 2 · edited May 13 '15 at 11:24

To get data in Python from an URL (and print it):

import requests

r = requests.get('http://www.omdbapi.com/?t=UN%20HOMME%20ID%C3%89AL')
print(r.text)

To parse a json in Python

import requests
import json

r = requests.get('http://www.omdbapi.com/?t=UN%20HOMME%20ID%C3%89AL')
json.loads(r.text)

You will have a JSON object.

To convert from JSON to tsv you may use tablib.

To create a excel document in Python you may use openpyxl (more tools at python-excel.org).

Parse Json data to Excel

2 Answers2