Transform JSON in CSV format to table

Question

I have already extracted the string of JSON data from a url and stored this in a CSV file.

The problem I am facing now is that I would like to represent this data in a table format so that I can upload this to my DB.

An example of my raw JSON data below:

{"pizza":[{"url":"www.pizza.com/1.json","id":1,"via":{"channel":"web","source":{"from":{"[email protected]"},"to":{"[email protected]"},"rel":null}},"created_at":"2013-07-09T08:38:35Z","updated_at":"20132-08-13T09:33:00Z","type":"incident"}]

An example of my CSV formatted JSON:

http://s18.postimg.org/alwadflsp/my_Code.png

An example of my desired output:

http://s18.postimg.org/4md4niqe1/myoutput.png

Would really appreciate help regarding this.

Thank you

While the links above may contain the desired inout and output now, it is better to include the essential parts of the question here and provide the link for reference. Links can become invalid if the linked page changes. — Bob Gilmore
– Bob Gilmore, Commented Oct 28, 2013 at 17:17

miku · Accepted Answer · 2013-10-28 16:57:38Z

2

There are (numerous) small command line utilities written for that task, maybe you find one of those helpful:

https://github.com/spullara/json2csv (java)
https://github.com/zeMirco/json2csv (node)
https://github.com/jehiah/json2csv (go)
https://github.com/danmandle/JSON2CSV (php)
https://github.com/vladikk/json2csv (python, blog post)

answered Oct 28, 2013 at 16:57

miku

189k47 gold badges314 silver badges317 bronze badges

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

Comments

ajc · Accepted Answer · 2013-10-28 17:01:29Z

1

If your final goal is to save your data obtained from JSON in a database.

My approach would be - 1) Extract the relevant info from the JSON using JSONObject. JSONOBJECT

2) Create a bean with all the info.

3) Pass that bean to the Database class to store the respective values.

answered Oct 28, 2013 at 17:01

ajc

1,73514 silver badges36 bronze badges

Comments

jq170727 · Accepted Answer · 2017-09-03 17:09:18Z

Here is a solution using jq. However this part of your data isn't legal JSON

"source":{"from":{"[email protected]"},"to":{"[email protected]"},

Assuming you remove the surrounding {} from the from and to members

"source":{"from":"[email protected]","to":"[email protected]",

then if filter.jq contains the following filter

[
    .pizza[]
  | {url, id} + (.via | {channel} + (.source | {from, to})) + {created_at, updated_at}
]
| (.[0] | keys_unsorted)
, (.[]  | [.[]])
| @csv

and data.json contains your sample data then the command

$ jq -M -r -f filter.jq data.json

produces

"url","id","channel","from","to","created_at","updated_at"
"www.pizza.com/1.json",1,"web","[email protected]","[email protected]","2013-07-09T08:38:35Z","20132-08-13T09:33:00Z"

Collectives™ on Stack Overflow

Transform JSON in CSV format to table

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related