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
– Blusky, Commented May 13, 2015 at 10:59
You can use tablib if you want to write Json data to an Xls file. Tablib — Vaulstein
– Vaulstein, Commented May 13, 2015 at 11:11
@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
– Tauseef Hussain, Commented May 13, 2015 at 12:45

Vivek Sable · Accepted Answer · 2015-05-13 16:05:44Z

3

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

edited May 13, 2015 at 16:05

answered May 13, 2015 at 11:15

Vivek Sable

10.3k6 gold badges45 silver badges63 bronze badges

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

2 Comments

Tauseef Hussain Over a year ago

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.

Vivek Sable Over a year ago

@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 · Accepted Answer · 2015-05-13 11:24:20Z

3

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).

edited May 13, 2015 at 11:24

Vivek Sable

10.3k6 gold badges45 silver badges63 bronze badges

answered May 13, 2015 at 11:17

maggick

1,42214 silver badges23 bronze badges

Collectives™ on Stack Overflow

Parse Json data to Excel

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related