vinay20045 / json-to-csv Goto Github PK

View Code? Open in Web Editor NEW

283.0 22.0 220.0 14 KB

Nested JSON to CSV Converter

Home Page: https://askvinay.com/post/converting-nested-json-to-csv-8-december-2013.html

License: MIT License

Python 100.00%

json-to-csv json csv csv-converter python

json-to-csv's Introduction

json-to-csv

Nested JSON to CSV Converter.

This python script converts valid, preformatted JSON to CSV which can be opened in excel and other similar applications. This script can handle nested json with multiple objects and arrays. Please see the explanation below and the sample files to understand how this works. It can handle non similar objects too. But, more the similarity of the objects, prettier the output.

Written in Python 2.7. Last tested in Python 3.6.3.

Usage

python /path/to/json_to_csv.py node json_in_file_path csv_out_file_path

Source Specification

The script expects the json to be given via a file containing

A valid JSON
The JSON can be an Array of node Object Ex:-

    {
        "node":[
            {
                "item_1":"value_11",
                "item_2":"value_12",
                "item_3":"value_13",
                "item_4":["sub_value_14", "sub_value_15"],
                "item_5":{
                    "sub_item_1":"sub_item_value_11",
                    "sub_item_2":["sub_item_value_12", "sub_item_value_13"]
                }
            },
            {
                "item_1":"value_21",
                "item_2":"value_22",
                "item_4":["sub_value_24", "sub_value_25"],
                "item_5":{
                    "sub_item_1":"sub_item_value_21",
                    "sub_item_2":["sub_item_value_22", "sub_item_value_23"]
                }
            }
        ]
    }

The JSON can be a list of dictionaries
If your JSON is a list of dictionaries, the first argument node can be any relevant string

    [
        {
            "item_1":"value_11",
            "item_2":"value_12",
            "item_3":"value_13",
            "item_4":["sub_value_14", "sub_value_15"],
            "item_5":{
                "sub_item_1":"sub_item_value_11",
                "sub_item_2":["sub_item_value_12", "sub_item_value_13"]
            }
        },
        {
            "item_1":"value_21",
            "item_2":"value_22",
            "item_4":["sub_value_24", "sub_value_25"],
            "item_5":{
                "sub_item_1":"sub_item_value_21",
                "sub_item_2":["sub_item_value_22", "sub_item_value_23"]
            }
        }
    ]

Gotchas

I have written a JSON generator which will take care of encoding issues and generate a valid JSON for this tool. ~~However, If you find yourself in the character encoding hell, drop me a mail and I will add support for this~~ This is now fixed.

Visit converting nested json to csv post on my blog to read more about this script.

json-to-csv's People

Contributors

Stargazers

Watchers

Forkers

bhargav08 knirmalraman naterh rupndrsingh cawhite78 leafiy pathcl jfllado davoodharun codenamejason nimboya typeofgraphic prasantapal georgelu alex-ramos jenningp bettymaryjoseph apoorvamolls rgarofalo yuqi92 timpanosian projectcheshire prateekmehta anthonyceltra lsyaseen devpp sravyapolisetty ammy2020 seakintruth colinsongf rahulrawat11 thebzeerajalal john-owens natrajl anudeepbabu chevarria sshort123 judethedude lloopp-dev yil17 rossbernet alexandragunder fuxkinglife ashh007 joshuaebenezer wangmeihong bourgois heindrik ganeshmandarapu mfhodges steveermish professor0x minhhuynh25 zrg1993 xkuang tushardesale geonux danielfiltereasy 7125messi fs-rd-yu-fukuzawa baixuexue123 spoorthya mldeveloper01 cthakar mr-brody charlesben1 xtinaschelin shabarish591 anilshrish bbartik gh-osama simarpreets shivani-tamu barrycug thisistimothychen anjijava16 nghinv panamu moonlesss apurvazemoso susz007 ukwatte kharthigeyan udm25263 shadefactor shashishailaj shaxami ravikuc koutilyagowtham chrissteel bkaathi victorioberra leon-ramco mzpqnxow jhonjairoroa87 therosser nokia6102 waizuwolf krkredde abhikal

json-to-csv's Issues

Share an example of the path and how to use it

can you make it more json instagram friendly ?!

https://superuser.com/questions/1412477/how-can-i-view-my-instagram-backup-data-in-a-friendly-way

Nested Json to csv in python 3

I am trying to convert JSON data into a CSV in Python3, but it no longer works with this script, giving me different errors. Anyone know how to fix for Python 3? Thanks.

Below is my JSON data:

{
"fruit": [
{
"name": "Apple",
"binomial name": "Malus domestica",
"major_producers": [
"China",
"United States",
"Turkey"
],
"nutrition": {
"carbohydrates": "13.81g",
"fat": "0.17g",
"protein": "0.26g"
}
},
{
"name": "Orange",
"binomial name": "Citrus x sinensis",
"major_producers": [
"Brazil",
"United States",
"India"
],
"nutrition": {
"carbohydrates": "11.75g",
"fat": "0.12g",
"protein": "0.94g"
}
},
{
"name": "Mango",
"binomial name": "Mangifera indica",
"major_producers": [
"India",
"China",
"Thailand"
],
"nutrition": {
"carbohydrates": "15g",
"fat": "0.38g",
"protein": "0.82g"
}
}
]
}

Invalid syntax when executing script

When executing the script with or without arguments, using Python 2.7 or 3.6.3 on a Mac:
File "jsoncsv.py", line 51
elif type(value) is dict:
^
SyntaxError: invalid syntax

Convert multiple json structure in one json file

Hi, can you advice me how to convert the json file if i have more than one json structure inside a .json
Example :

{
"image_id": "W0pVzdW6XjPFu2fuazKODA==",
"request_id": "1515662622,c9c626f1-11c7-4584-ba99-b731b7046c44",
"time_used": 250,
"faces": [
{
"landmark": {
"contour_chin": {
"y": 269,
"x": 73
},
"left_eye_upper_left_quarter": {
"y": 135,
"x": 67
},
"mouth_lower_lip_right_contour1": {
"y": 230,
"x": 98
},
"left_eye_bottom": {
"y": 143,
"x": 72
},
"mouth_lower_lip_right_contour2": {
"y": 238,
"x": 104
},
"contour_left7": {
"y": 176,
"x": 49
},
"contour_left6": {
"y": 166,
"x": 49
},
"contour_left5": {
"y": 157,
"x": 51
},
"contour_left4": {
"y": 148,
"x": 54
},
"contour_left3": {
"y": 139,
"x": 58
},
"contour_left2": {
"y": 131,
"x": 61
},
"contour_left1": {
"y": 124,
"x": 64
},
"left_eye_lower_left_quarter": {
"y": 139,
"x": 66
},
"contour_right1": {
"y": 167,
"x": 206
},
"contour_right3": {
"y": 191,
"x": 201
}
}
]
}

If will always prompt error right after it successfully done the first json structure. Thanks

Can u share similar code for java

converting json to csv with keys and dictionary

i am downloading an API from a website, download it as export.json. now i am trying to convert that to .csv but with only certain keys or dictionarys. i think its nested but not sure. see attached .json file. i want to convert only the fieldId8, fieldId9, and fieldId2 into the csv file.
Export.zip

Python 3 CSV NewLine

In Python3 there is a know issue when writing line with csv lib.
https://stackoverflow.com/questions/3348460/csv-file-written-with-python-has-blank-lines-between-each-row

I added newline ='' to the file open function to solve this.

Line 92 open(csv_file_path, 'w+',newline ='')

Thanks for sharing this code

Unable to get the desired response

JSON File:
{

"1c21528060728955": {
	"2018-06-04": 1,
	"2018-06-03": 0,
	"2018-06-02": 0,
	"2018-06-01": 0
},
"ffa1528090909651": {
	"2018-06-04": 1,
	"2018-06-03": 0,
	"2018-06-02": 0,
	"2018-06-01": 0
}

}
Result by using json-to-csv
""
"1c21528060728955"
"ffa1528090909651"

Expected result:
2018-06-04 2018-06-03 2018-06-02 2018-06-01
1c21528060728955 1 0 0 0
ffa1528090909651 1 0 0 0

Doesn't work for dictionary containing a dictionary

First of all, This is great code and I appreciate your effort. This save our days of efforts actually.
Saying that, When I tried for below json file

{"nodes":{"node":[{"rack":"123","state":"KILLED","id":"google:2345","nodeHostName":"dbxcq43ty67q","nodeHTTPAddress":"dbxcq43ty67q:9999","lastHealthUpdate":1522315755208,"version":"2.6.0","healthReport":"healthy","numContainers":52,"usedMemoryMB":98,"availMemoryMB":107520,"usedVirtualCores":4,"availableVirtualCores":30},{"rack":"123","state":"KILLED","id":"google:2345","nodeHostName":"dbxcq43ty67q","nodeHTTPAddress":"dbxcq43ty67q:9999","lastHealthUpdate":1522315755208,"version":"2.6.0","healthReport":"healthy","numContainers":52,"usedMemoryMB":98,"availMemoryMB":107520,"usedVirtualCores":4,"availableVirtualCores":30},{"rack":"123","state":"KILLED","id":"google:2345","nodeHostName":"dbxcq43ty67q","nodeHTTPAddress":"dbxcq43ty67q:9999","lastHealthUpdate":1522315755208,"version":"2.6.0","healthReport":"healthy","numContainers":52,"usedMemoryMB":98,"availMemoryMB":107520,"usedVirtualCores":4,"availableVirtualCores":30}]}}

It didn't work. Actually I had to delete the very first dictionary nodes and then give <'node'> as . Please look if you can solve this or if i'm wrong. Correct me.

Thanks bud for everything

NameError: global name 'reduced_item' is not defined

This is the format of my JSON file:

I want to unpack the results to CSV file with the script.
Basically it should be 12 rows of data in CSV.

The script fails (Python 2.7):

    reduced_item[to_string(key)] = to_string(value)
NameError: global name 'reduced_item' is not defined

I can't convert a file

Hello,

Thank you, it's an excellent script.

I have a problem:
I'm trying to convert a valid json file, and I get this error:
_csv.Error: need to escape, but no escapechar set

What can I do?

cdx

not run

No "node" name

Hi Vinay, I wanted to try your script but unfortunately my (large) JSON does not have the name for the node I want to trasform in a csv row. Basically, my JSON is a list of dictionaries, and I want the script to take all the dictionaries.

Header sort improvement

Current the header sorts alphabetically and only takes into account the first digit e.g.

header_0, header_1, header_10, header_11, header_2, header_3 ....

A solution has been found and applied using the following stackoverflow answer.

I've added this code to my own branch so far and it is working fine for me.

example of command

A brilliant script.

Can you give an example of how to run the script?
I didn't understand what the "node_name" parameter should contain

i wanna apply this function in my json!

but i don't know how do i do it
can i help me?

a = {
"medications":[{
"aceInhibitors":[{
"name":"lisinopril",
"strength":"10 mg Tab",
"dose":"1 tab",
"route":"PO",
"sig":"daily",
"pillCount":"#90",
"refills":"Refill 3"
}],
"antianginal":[{
"name":"nitroglycerin",
"strength":"0.4 mg Sublingual Tab",
"dose":"1 tab",
"route":"SL",
"sig":"q15min PRN",
"pillCount":"#30",
"refills":"Refill 1"
}],
"anticoagulants":[{
"name":"warfarin sodium",
"strength":"3 mg Tab",
"dose":"1 tab",
"route":"PO",
"sig":"daily",
"pillCount":"#90",
"refills":"Refill 3"
}],
"betaBlocker":[{
"name":"metoprolol tartrate",
"strength":"25 mg Tab",
"dose":"1 tab",
"route":"PO",
"sig":"daily",
"pillCount":"#90",
"refills":"Refill 3"
}],
"diuretic":[{
"name":"furosemide",
"strength":"40 mg Tab",
"dose":"1 tab",
"route":"PO",
"sig":"daily",
"pillCount":"#90",
"refills":"Refill 3"
}],
"mineral":[{
"name":"potassium chloride ER",
"strength":"10 mEq Tab",
"dose":"1 tab",
"route":"PO",
"sig":"daily",
"pillCount":"#90",
"refills":"Refill 3"
}]
}
],
"labs":[{
"name":"Arterial Blood Gas",
"time":"Today",
"location":"Main Hospital Lab"
},
{
"name":"BMP",
"time":"Today",
"location":"Primary Care Clinic"
},
{
"name":"BNP",
"time":"3 Weeks",
"location":"Primary Care Clinic"
},
{
"name":"BUN",
"time":"1 Year",
"location":"Primary Care Clinic"
},
{
"name":"Cardiac Enzymes",
"time":"Today",
"location":"Primary Care Clinic"
},
{
"name":"CBC",
"time":"1 Year",
"location":"Primary Care Clinic"
},
{
"name":"Creatinine",
"time":"1 Year",
"location":"Main Hospital Lab"
},
{
"name":"Electrolyte Panel",
"time":"1 Year",
"location":"Primary Care Clinic"
},
{
"name":"Glucose",
"time":"1 Year",
"location":"Main Hospital Lab"
},
{
"name":"PT/INR",
"time":"3 Weeks",
"location":"Primary Care Clinic"
},
{
"name":"PTT",
"time":"3 Weeks",
"location":"Coumadin Clinic"
},
{
"name":"TSH",
"time":"1 Year",
"location":"Primary Care Clinic"
}
],
"imaging":[{
"name":"Chest X-Ray",
"time":"Today",
"location":"Main Hospital Radiology"
},
{
"name":"Chest X-Ray",
"time":"Today",
"location":"Main Hospital Radiology"
},
{
"name":"Chest X-Ray",
"time":"Today",
"location":"Main Hospital Radiology"
}
]
}

but what about this json which has no node??

{"created_at":"Sun Jul 02 03:09:52 +0000 2017","id":881349174587002880,"id_str":"881349174587002880","text":"RT @Lutho__m: If you using mtn join AfriHost #MTNisTrash \nRT to help fellow tweeps\n\n500MB - R29\n1Gig - R58\n2Gig - R99\n3Gig - R145\n4Gig - R1\u2026","source":"\u003ca href="http://twitter.com/download/android" rel="nofollow"\u003eTwitter for Android\u003c/a\u003e","truncated":false,"in_reply_to_status_id":null,"in_reply_to_status_id_str":null,"in_reply_to_user_id":null,"in_reply_to_user_id_str":null,"in_reply_to_screen_name":null,"user":{"id":2806021774,"id_str":"2806021774","name":"van wyk money","screen_name":"agneshleli","location":"in my zone","url":null,"description":"doing the damn thing.","protected":false,"verified":false,"followers_count":343,"friends_count":167,"listed_count":3,"favourites_count":315,"statuses_count":8483,"created_at":"Sat Oct 04 18:29:06 +0000 2014","utc_offset":null,"time_zone":null,"geo_enabled":true,"lang":"en","contributors_enabled":false,"is_translator":false,"profile_background_color":"C0DEED","profile_background_image_url":"http://abs.twimg.com/images/themes/theme1/bg.png","profile_background_image_url_https":"https://abs.twimg.com/images/themes/theme1/bg.png","profile_background_tile":false,"profile_link_color":"1DA1F2","profile_sidebar_border_color":"C0DEED","profile_sidebar_fill_color":"DDEEF6","profile_text_color":"333333","profile_use_background_image":true,"profile_image_url":"http://pbs.twimg.com/profile_images/841333464087572481/6o6xWxL0_normal.jpg","profile_image_url_https":"https://pbs.twimg.com/profile_images/841333464087572481/6o6xWxL0_normal.jpg","profile_banner_url":"https://pbs.twimg.com/profile_banners/2806021774/1441472196","default_profile":true,"default_profile_image":false,"following":null,"follow_request_sent":null,"notifications":null},"geo":null,"coordinates":null,"place":null,"contributors":null,"retweeted_status":{"created_at":"Sat Jul 01 09:44:15 +0000 2017","id":881086035022761984,"id_str":"881086035022761984","text":"If you using mtn join AfriHost #MTNisTrash \nRT to help fellow tweeps\n\n500MB - R29\n1Gig - R58\n2Gig - R99\n3Gig - R145\n4Gig - R197\n5Gig - R247","source":"\u003ca href="http://twitter.com/download/android" rel="nofollow"\u003eTwitter for Android\u003c/a\u003e","truncated":false,"in_reply_to_status_id":null,"in_reply_to_status_id_str":null,"in_reply_to_user_id":null,"in_reply_to_user_id_str":null,"in_reply_to_screen_name":null,"user":{"id":835234814924029952,"id_str":"835234814924029952","name":"\ud83d\udc51 XHOSA PRINCE\ud83c\udf6b","screen_name":"Lutho__m","location":"Cape Town // Engcobo","url":"http://atripwithlutho.wordpress.com","description":"Xhosa Choco \ud83c\udf6b| #TTZA i rep \ud83d\udc80 \ud83d\udcc8 | My tweets are mostly my thoughts \ud83d\ude0e | Check Likes \ud83d\udd25| my life's story is in my blog go check it out if you interested \u263a\ud83d\ude0a","protected":false,"verified":false,"followers_count":18608,"friends_count":16875,"listed_count":16,"favourites_count":1374,"statuses_count":8828,"created_at":"Fri Feb 24 21:07:52 +0000 2017","utc_offset":null,"time_zone":null,"geo_enabled":true,"lang":"en","contributors_enabled":false,"is_translator":false,"profile_background_color":"F5F8FA","profile_background_image_url":"","profile_background_image_url_https":"","profile_background_tile":false,"profile_link_color":"1DA1F2","profile_sidebar_border_color":"C0DEED","profile_sidebar_fill_color":"DDEEF6","profile_text_color":"333333","profile_use_background_image":true,"profile_image_url":"http://pbs.twimg.com/profile_images/865206311922749440/ol8Aj4As_normal.jpg","profile_image_url_https":"https://pbs.twimg.com/profile_images/865206311922749440/ol8Aj4As_normal.jpg","profile_banner_url":"https://pbs.twimg.com/profile_banners/835234814924029952/1496065852","default_profile":true,"default_profile_image":false,"following":null,"follow_request_sent":null,"notifications":null},"geo":null,"coordinates":null,"place":null,"contributors":null,"is_quote_status":false,"retweet_count":1209,"favorite_count":633,"entities":{"hashtags":[{"text":"MTNisTrash","indices":[31,42]}],"urls":[],"user_mentions":[],"symbols":[]},"favorited":false,"retweeted":false,"filter_level":"low","lang":"en"},"is_quote_status":false,"retweet_count":0,"favorite_count":0,"entities":{"hashtags":[{"text":"MTNisTrash","indices":[45,56]}],"urls":[],"user_mentions":[{"screen_name":"Lutho__m","name":"\ud83d\udc51 XHOSA PRINCE\ud83c\udf6b","id":835234814924029952,"id_str":"835234814924029952","indices":[3,12]}],"symbols":[]},"favorited":false,"retweeted":false,"filter_level":"low","lang":"en","timestamp_ms":"1498964992554"}
{"created_at":"Sun Jul 02 03:10:13 +0000 2017","id":881349262835101696,"id_str":"881349262835101696","text":"RT @flxriantheonly: avant qd j'\u00e9tais petit le moment d'aller me coucher j'le d\u00e9testais j'comprend pas pq pcq mtn c'est devenu mon moment pr\u2026","source":"\u003ca href="http://twitter.com/download/android" rel="nofollow"\u003eTwitter for Android\u003c/a\u003e","truncated":false,"in_reply_to_status_id":null,"in_reply_to_status_id_str":null,"in_reply_to_user_id":null,"in_reply_to_user_id_str":null,"in_reply_to_screen_name":null,"user":{"id":3732973155,"id_str":"3732973155","name":"Ju","screen_name":"justine_dwd","location":"JUL ","url":null,"description":"DYLAN.\u2661","protected":false,"verified":false,"followers_count":232,"friends_count":95,"listed_count":3,"favourites_count":1322,"statuses_count":14335,"created_at":"Mon Sep 21 23:28:52 +0000 2015","utc_offset":null,"time_zone":null,"geo_enabled":false,"lang":"fr","contributors_enabled":false,"is_translator":false,"profile_background_color":"C0DEED","profile_background_image_url":"http://abs.twimg.com/images/themes/theme1/bg.png","profile_background_image_url_https":"https://abs.twimg.com/images/themes/theme1/bg.png","profile_background_tile":false,"profile_link_color":"1DA1F2","profile_sidebar_border_color":"C0DEED","profile_sidebar_fill_color":"DDEEF6","profile_text_color":"333333","profile_use_background_image":true,"profile_image_url":"http://pbs.twimg.com/profile_images/881234496519065600/0WefTQuj_normal.jpg","profile_image_url_https":"https://pbs.twimg.com/profile_images/881234496519065600/0WefTQuj_normal.jpg","profile_banner_url":"https://pbs.twimg.com/profile_banners/3732973155/1444091879","default_profile":true,"default_profile_image":false,"following":null,"follow_request_sent":null,"notifications":null},"geo":null,"coordinates":null,"place":null,"contributors":null,"retweeted_status":{"created_at":"Mon Jun 19 17:11:43 +0000 2017","id":876849989691076608,"id_str":"876849989691076608","text":"avant qd j'\u00e9tais petit le moment d'aller me coucher j'le d\u00e9testais j'comprend pas pq pcq mtn c'est devenu mon moment pr\u00e9f\u00e9r\u00e9 de la journ\u00e9e","source":"\u003ca href="http://twitter.com" rel="nofollow"\u003eTwitter Web Client\u003c/a\u003e","truncated":false,"in_reply_to_status_id":null,"in_reply_to_status_id_str":null,"in_reply_to_user_id":null,"in_reply_to_user_id_str":null,"in_reply_to_screen_name":null,"user":{"id":2316935620,"id_str":"2316935620","name":"snap : florianlmh86","screen_name":"flxriantheonly","location":"Niort,Poitiers ","url":null,"description":"Sinon \u00e7a fais moche sans bio mdr","protected":false,"verified":false,"followers_count":21536,"friends_count":7605,"listed_count":87,"favourites_count":13096,"statuses_count":1063,"created_at":"Fri Jan 31 21:43:29 +0000 2014","utc_offset":7200,"time_zone":"Paris","geo_enabled":false,"lang":"fr","contributors_enabled":false,"is_translator":false,"profile_background_color":"FF6699","profile_background_image_url":"http://abs.twimg.com/images/themes/theme11/bg.gif","profile_background_image_url_https":"https://abs.twimg.com/images/themes/theme11/bg.gif","profile_background_tile":true,"profile_link_color":"FAB81E","profile_sidebar_border_color":"FFFFFF","profile_sidebar_fill_color":"E5507E","profile_text_color":"362720","profile_use_background_image":true,"profile_image_url":"http://pbs.twimg.com/profile_images/876236500589060096/Ra0dSeKK_normal.jpg","profile_image_url_https":"https://pbs.twimg.com/profile_images/876236500589060096/Ra0dSeKK_normal.jpg","profile_banner_url":"https://pbs.twimg.com/profile_banners/2316935620/1497745958","default_profile":false,"default_profile_image":false,"following":null,"follow_request_sent":null,"notifications":null},"geo":null,"coordinates":null,"place":null,"contributors":null,"is_quote_status":false,"retweet_count":1001,"favorite_count":515,"entities":{"hashtags":[],"urls":[],"user_mentions":[],"symbols":[]},"favorited":false,"retweeted":false,"filter_level":"low","lang":"fr"},"is_quote_status":false,"retweet_count":0,"favorite_count":0,"entities":{"hashtags":[],"urls":[],"user_mentions":[{"screen_name":"flxriantheonly","name":"snap : florianlmh86","id":2316935620,"id_str":"2316935620","indices":[3,18]}],"symbols":[]},"favorited":false,"retweeted":false,"filter_level":"low","lang":"fr","timestamp_ms":"1498965013594"}
{"created_at":"Sun Jul 02 03:10:48 +0000 2017","id":881349407802888192,"id_str":"881349407802888192","text":"MoMoPay Arrives in Africa via MTN Benin and Youtap https://t.co/yCBg5MVrrK #payments https://t.co/wUO774x1Aq\u2026 https://t.co/V4oFnbuEJv","display_text_range":[0,140],"source":"\u003ca href="https://www.socialoomph.com" rel="nofollow"\u003eSocialOomph\u003c/a\u003e","truncated":true,"in_reply_to_status_id":null,"in_reply_to_status_id_str":null,"in_reply_to_user_id":null,"in_reply_to_user_id_str":null,"in_reply_to_screen_name":null,"user":{"id":50151799,"id_str":"50151799","name":"CardFlash News","screen_name":"CardFlash","location":"Naples, Florida","url":"http://www.cardflash.com","description":"CARDFLASH\u00ae is THE ORIGINAL News Service Reporting on the Payment Card Industry Daily Since 1986. Over 200,000 Paid Subscribers !","protected":false,"verified":false,"followers_count":856,"friends_count":1219,"listed_count":172,"favourites_count":2,"statuses_count":7620,"created_at":"Wed Jun 24 00:18:32 +0000 2009","utc_offset":-14400,"time_zone":"Eastern Time (US & Canada)","geo_enabled":true,"lang":"en","contributors_enabled":false,"is_translator":false,"profile_background_color":"A3A3A3","profile_background_image_url":"http://pbs.twimg.com/profile_background_images/558286616/cardflash_twitter_bg.png","profile_background_image_url_https":"https://pbs.twimg.com/profile_background_images/558286616/cardflash_twitter_bg.png","profile_background_tile":false,"profile_link_color":"20C200","profile_sidebar_border_color":"C0DEED","profile_sidebar_fill_color":"DDEEF6","profile_text_color":"333333","profile_use_background_image":true,"profile_image_url":"http://pbs.twimg.com/profile_images/2239046993/cardflash_squarelogo_normal.png","profile_image_url_https":"https://pbs.twimg.com/profile_images/2239046993/cardflash_squarelogo_normal.png","default_profile":false,"default_profile_image":false,"following":null,"follow_request_sent":null,"notifications":null},"geo":null,"coordinates":null,"place":null,"contributors":null,"is_quote_status":false,"extended_tweet":{"full_text":"MoMoPay Arrives in Africa via MTN Benin and Youtap https://t.co/yCBg5MVrrK #payments https://t.co/wUO774x1Aq #CardFlash https://t.co/eDN3ZT3iSl","display_text_range":[0,119],"entities":{"hashtags":[{"text":"payments","indices":[75,84]},{"text":"CardFlash","indices":[109,119]}],"urls":[{"url":"https://t.co/yCBg5MVrrK","expanded_url":"http://dld.bz/fQHTN","display_url":"dld.bz/fQHTN","indices":[51,74]},{"url":"https://t.co/wUO774x1Aq","expanded_url":"http://www.cardflash.com","display_url":"cardflash.com","indices":[85,108]}],"user_mentions":[],"symbols":[],"media":[{"id":881349403872808960,"id_str":"881349403872808960","indices":[120,143],"media_url":"http://pbs.twimg.com/media/DDsunPzXUAAZBOJ.jpg","media_url_https":"https://pbs.twimg.com/media/DDsunPzXUAAZBOJ.jpg","url":"https://t.co/eDN3ZT3iSl","display_url":"pic.twitter.com/eDN3ZT3iSl","expanded_url":"https://twitter.com/CardFlash/status/881349407802888192/photo/1","type":"photo","sizes":{"thumb":{"w":150,"h":150,"resize":"crop"},"large":{"w":1186,"h":1132,"resize":"fit"},"medium":{"w":1186,"h":1132,"resize":"fit"},"small":{"w":680,"h":649,"resize":"fit"}}}]},"extended_entities":{"media":[{"id":881349403872808960,"id_str":"881349403872808960","indices":[120,143],"media_url":"http://pbs.twimg.com/media/DDsunPzXUAAZBOJ.jpg","media_url_https":"https://pbs.twimg.com/media/DDsunPzXUAAZBOJ.jpg","url":"https://t.co/eDN3ZT3iSl","display_url":"pic.twitter.com/eDN3ZT3iSl","expanded_url":"https://twitter.com/CardFlash/status/881349407802888192/photo/1","type":"photo","sizes":{"thumb":{"w":150,"h":150,"resize":"crop"},"large":{"w":1186,"h":1132,"resize":"fit"},"medium":{"w":1186,"h":1132,"resize":"fit"},"small":{"w":680,"h":649,"resize":"fit"}}}]}},"retweet_count":0,"favorite_count":0,"entities":{"hashtags":[{"text":"payments","indices":[75,84]}],"urls":[{"url":"https://t.co/yCBg5MVrrK","expanded_url":"http://dld.bz/fQHTN","display_url":"dld.bz/fQHTN","indices":[51,74]},{"url":"https://t.co/wUO774x1Aq","expanded_url":"http://www.cardflash.com","display_url":"cardflash.com","indices":[85,108]},{"url":"https://t.co/V4oFnbuEJv","expanded_url":"https://twitter.com/i/web/status/881349407802888192","display_url":"twitter.com/i/web/status/8\u2026","indices":[110,133]}],"user_mentions":[],"symbols":[]},"favorited":false,"retweeted":false,"possibly_sensitive":false,"filter_level":"low","lang":"en","timestamp_ms":"1498965048157"}
{"created_at":"Sun Jul 02 03:10:51 +0000 2017","id":881349422910779392,"id_str":"881349422910779392","text":"C'est bon je viens te faire chier L\u00e9a, mtn je peux essayer de dormir mdrrr. Elle va gueuler mais c'est pas grave \u00e0 force j'ai l'habitude","source":"\u003ca href="http://twitter.com/download/iphone" rel="nofollow"\u003eTwitter for iPhone\u003c/a\u003e","truncated":false,"in_reply_to_status_id":null,"in_reply_to_status_id_str":null,"in_reply_to_user_id":null,"in_reply_to_user_id_str":null,"in_reply_to_screen_name":null,"user":{"id":4734361875,"id_str":"4734361875","name":"Shanel Dreze\u2665","screen_name":"DrezeShanel","location":null,"url":null,"description":"Marion/Margot\u2764\ufe0f Ali,L\u00e9a\u2764\ufe0f 15 ans\ud83c\udf82","protected":false,"verified":false,"followers_count":387,"friends_count":504,"listed_count":0,"favourites_count":812,"statuses_count":827,"created_at":"Thu Jan 07 12:23:12 +0000 2016","utc_offset":null,"time_zone":null,"geo_enabled":false,"lang":"fr","contributors_enabled":false,"is_translator":false,"profile_background_color":"F5F8FA","profile_background_image_url":"","profile_background_image_url_https":"","profile_background_tile":false,"profile_link_color":"1DA1F2","profile_sidebar_border_color":"C0DEED","profile_sidebar_fill_color":"DDEEF6","profile_text_color":"333333","profile_use_background_image":true,"profile_image_url":"http://pbs.twimg.com/profile_images/876577186865700864/PcS4dpiX_normal.jpg","profile_image_url_https":"https://pbs.twimg.com/profile_images/876577186865700864/PcS4dpiX_normal.jpg","profile_banner_url":"https://pbs.twimg.com/profile_banners/4734361875/1496366327","default_profile":true,"default_profile_image":false,"following":null,"follow_request_sent":null,"notifications":null},"geo":null,"coordinates":null,"place":null,"contributors":null,"is_quote_status":false,"retweet_count":0,"favorite_count":0,"entities":{"hashtags":[],"urls":[],"user_mentions":[],"symbols":[]},"favorited":false,"retweeted":false,"filter_level":"low","lang":"fr","timestamp_ms":"1498965051759"}

use as a python lib

could you please also release a python lib version, so that we can use it within python intuitively, many thanks

Json to multipule CSV - Feature request

Hi,

I have the following JSON

Nested Json with arrays that might also contain Jsons.

The current script creates single CSV file. however if arrays with Json are involved the CSV file is unreadable. As you can see in the image my Json has 3 rows : {0}, {1}, {2} each row contains the JSON to map key to columns and value to value. However when array are involved it gets ugly. {0} can have array of 5 items while {1} can have array of {2} items. this means many blank cells. now imagine one of the rows has 1000 items. It will create 1000 columns just for one row. The rest are blank.

The solution for such issue is whenever array is encountered unpack it to a new CSV file. In such approach there won't be columns mixed with row values such as: NODE_items_0_itemid , NODE_items_1_itemid it will have only NODE_items_itemid.

This is a major enhancement to the script and I'm aware that not all users want such behavior so you could offer two versions of the script. Let me know if you think it's a good idea.

Python 3.6 incompatibility

Receiving error messages when using the latest version of python

improvement: call from terminal

Hi there.
I am suggesting to add a sheebang to be able to call the function easily from the terminal
#!/usr/bin/env python

Also, I think the 'node' option is not clear in the help. I am not even sure I understood exactly why it is required. Anyway, some improved help would be good:

print ("\nUsage: python json_to_csv.py <node> <json_in_file_path> <csv_out_file_path>\n\n\tnode: node name, prefix for columns names\n")

Node_name: What is it?

python /path/to/json_to_csv.py node_name json_in_file_path csv_out_file_path

what is the node_name?

Changing delimiter to semicolon and removing quotation marks

Changing the delimiter to semicolon and removing quotation marks will help to open the csv file in excel.
Can you please add an option for that? Also in comment would be very helpful - so everyone can use what he needs :)

Array of complex objects?

What would that look like?

IE:

[
    {
        "name":"Apple",
        "binomial name":"Malus domestica",
        "major_producers":[
            {
                "name": "China",
                "amount": 100,
            }, 
            {
                "name": "United States",
                "amount": 200,
            }, 
            {
                "name": "Turkey",
                "amount": 300,
            }, 
        ]
    },
]

Would there just be a number added to the columns (fruit_major_producers_1_name, fruit_major_producers_1_amount)? What if fruit 2 (not pictured) had greater or less than fruit 1 number of major producers? Null columns?

json tocsv on my file throws back errors at me

Hi Vinay,

My json file is 1GB and when i run this code on it, these are the following errors below. How can i fix this error, It is very crucial that i decode this json to csv, anyhelp to resolve this and get a good output will be really appreciated. thanks!
Traceback (most recent call last):
File "/Users/pv197/json-to-csv/json_to_csv.py", line 72, in
raw_data = json.loads(json_value)
File "/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/json/init.py", line 310, in loads
return _default_decoder.decode(s)
File "/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/json/decoder.py", line 346, in decode
obj, end = self.raw_decode(s, idx=_w(s, 0).end())
File "/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/json/decoder.py", line 364, in raw_decode
raise ValueError("No JSON object could be decoded")
ValueError: No JSON object could be decoded

Looking forward to your reply.
sincerely,
PV

MemoryError

I getting this when trying to process a 209 mb json file:

Traceback (most recent call last):
File "json_to_csv.py", line 85, in
header += reduced_item.keys()
MemoryError

What can I do?

pip build can't find README.rst

When I try to install via pip I get a file not found error for README.rst:

pip install json-to-csv
Collecting json-to-csv
  Using cached json_to_csv-1.2.9.zip
    Complete output from command python setup.py egg_info:
    Traceback (most recent call last):
      File "<string>", line 1, in <module>
      File "/private/var/folders/4l/9j724xnn16s2s1bhv9_7l_v00000gn/T/pip-build-0AJu9Q/json-to-csv/setup.py", line 22, in <module>
        with open(path.join(here, 'README.rst'), encoding='utf-8') as f:
      File "/usr/local/Cellar/python/2.7.13/Frameworks/Python.framework/Versions/2.7/lib/python2.7/codecs.py", line 896, in open
        file = __builtin__.open(filename, mode, buffering)
    IOError: [Errno 2] No such file or directory: '/private/var/folders/4l/9j724xnn16s2s1bhv9_7l_v00000gn/T/pip-build-0AJu9Q/json-to-csv/README.rst'

    ----------------------------------------
Command "python setup.py egg_info" failed with error code 1 in /private/var/folders/4l/9j724xnn16s2s1bhv9_7l_v00000gn/T/pip-build-0AJu9Q/json-to-csv/```

not working for my json

{

    "ASSETS" : { "ASSET" :[{
            
    "BND_CORP" : {
            
    "ACCRUAL_DT" : "12/22/2016",
    "AMT_ISU" : "400000000",
    "ANNOUNCE_DT" : "12/8/2016",
    "CALC_TYPE" : "1",
    "CALL_TYPE" : "A",
    "CD_INSTMT_TYPE" : "UNKNOWN",
    "CHANGE_DT" : "2/1/2017 16:07:29.203",
    "COMPOUND_FLAG" : "0",
    "COUNTRY" : "US",
    "COUPON_FIX" : "5.25",
    "COUP_FREQ" : "S",
    "CPN_TYPE" : "F",
    "CURRENCY" : "USD",
    "CUSIP" : "00081TAJ7",
    "CUSIP2_set" : { "CUSIP2_record" :[{
            
    "CODE" : "A",
    "IDENTIFIER" : "00081TAJ7"
},{
            
    "CODE" : "B",
    "IDENTIFIER" : "AL6700754"
},{
            
    "CODE" : "C",
    "IDENTIFIER" : "BZ78G47"
},{
            
    "CODE" : "I",
    "IDENTIFIER" : "US00081TAJ79"
},{
            
    "CODE" : "R",
    "IDENTIFIER" : "0x00102c5485480bae"
}] },
    "CUSIP_ALIAS_set" : { "CUSIP_ALIAS_record" :[{
            
    "CODE" : "8001",
    "IDENTIFIER" : "825328116",
    "PURPOSE" : "DEBTID"
},{
            
    "CODE" : "145986",
    "IDENTIFIER" : "F_12176095",
    "PURPOSE" : "PPMG"
},{
            
    "CODE" : "145986",
    "IDENTIFIER" : "F_12182768",
    "PURPOSE" : "PPMG"
}] },
    "CUSIP_TYPE" : "CUSIP",
    "DATE_CONV" : "D30360",
    "DESC_INSTMT" : "ACCO BRANDS CORP",
    "END_ADJ_FLAG" : "N",
    "FIRST_PAY_DT" : "6/15/2017",
    "FIRST_SETTLE_DT" : "12/22/2016",
    "FLAG_144A" : "Y",
    "FLAG_CCN" : "N",
    "FLAG_CONVERT" : "N",
    "FLAG_EOM" : "N",
    "FLAG_ERISA" : "N",
    "FLAG_PERFORMING" : "Y",
    "FLAG_REG_RIGHTS" : "N",
    "ISSUER_ID" : "C08578",
    "ISSUE_DT" : "12/22/2016",
    "ISSUE_EXCHANGES_set" : {
            "SIZE" : "1",

    "ISSUE_EXCHANGES_record" : {
            
    "EXCHANGE" : "NSD"
}
},
    "ISSUE_PRICE" : "100",
    "ISSUE_YIELD" : "5.25",
    "LEH_INDUSTRY" : "CONS",
    "LIQUIDITY" : "L",
    "MAKE_WHOLE_CALL" : "Y",
    "MAKE_WHOLE_SPREAD" : "0.5",
    "MARKET" : "US",
    "MARKET_ISSUE" : "PUBLIC",
    "MATURITY" : "12/15/2024",
    "MAX_CALL_NOTICE" : "60",
    "MIN_CALL_NOTICE" : "30",
    "MIN_LOT_SIZE" : "1000",
    "MIN_TRD_SIZE" : "2000",
    "MODIFIED_BY" : "sm_timestamp",
    "MTN" : "N",
    "NOTES_set" : { "NOTES_record" :[{
            
    "EFF_DT" : "12/9/2016",
    "INSTMT" : "144A_RegRights exempt",
    "SEQ_NUM" : "1",
    "USER_ID" : "mgalati"
},{
            
    "EFF_DT" : "12/22/2016",
    "INSTMT" : "Reuters defined: Not yet Issued",
    "SEQ_NUM" : "0",
    "USER_ID" : "EjvDescr"
},{
            
    "EFF_DT" : "2/1/2017",
    "INSTMT" : "risk country per PPMG",
    "SEQ_NUM" : "10",
    "USER_ID" : "ntodorov"
},{
            
    "EFF_DT" : "2/21/2017",
    "INSTMT" : "144A_RegRights exempt",
    "SEQ_NUM" : "500",
    "USER_ID" : "cmay"
}] },
    "NTL_FLAG" : "N",
    "PC_DAY_CONV" : "C",
    "PMT_ADJ_METH" : "F",
    "PMT_CAL" : "NY_Bank",
    "PMT_FREQ_TYPE" : "0",
    "PMT_LOCATION" : "D",
    "PRICE_AS_PCT" : "Y",
    "PUTCALL_set" : { "PUTCALL_record" :[{
            
    "DT" : "12/15/2019",
    "PRICE" : "103.938",
    "TYPE" : "C"
},{
            
    "DT" : "12/15/2020",
    "PRICE" : "102.625",
    "TYPE" : "C"
},{
            
    "DT" : "12/15/2021",
    "PRICE" : "101.313",
    "TYPE" : "C"
},{
            
    "DT" : "12/15/2022",
    "PRICE" : "100",
    "TYPE" : "C"
}] },
    "PUT_CALL" : "C",
    "RATING_set" : { "RATING_record" :[{
            
    "AGY" : "1",
    "DATE" : "12/8/2016",
    "VALUE" : "B1"
},{
            
    "AGY" : "2",
    "DATE" : "12/8/2016",
    "VALUE" : "BB-"
},{
            
    "AGY" : "3",
    "DATE" : "12/8/2016",
    "VALUE" : "BB"
},{
            
    "AGY" : "14",
    "DATE" : "3/20/2017",
    "VALUE" : "BB-"
},{
            
    "AGY" : "45",
    "DATE" : "12/8/2016",
    "VALUE" : "3(55)"
}] },
    "REVIEWED_BY" : "jedinger",
    "RISK_COUNTRY" : "US",
    "ROUND_FLAG" : "R",
    "ROUND_PRECISION" : "9",
    "SAL_INDUSTRY" : "CONS",
    "SECTOR_set" : { "SECTOR_record" :[{
            
    "CODE" : "CONS",
    "LEVEL" : "1",
    "SECTOR_SOURCE" : "P",
    "TYPE" : "BARC_SECT"
},{
            
    "CODE" : "CONP",
    "LEVEL" : "1",
    "TYPE" : "ISS_ML"
}] },
    "SEC_TYPE" : "BND_CORP",
    "SETTLE_LOCATION" : "D",
    "SM_SEC_GROUP" : "BND",
    "SM_SEC_TYPE" : "CORP",
    "STRUCTURE" : "SENIOR",
    "TICKER" : "ACCO",
    "UDF_set" : { "UDF_record" :[{
            
    "LABEL" : "EXP_DFLT_MARGIN",
    "PURPOSE" : "PPMG",
    "UDF_SOURCE" : "PPMG",
    "VALUE" : "139.28000000000000"
},{
            
    "LABEL" : "FINANCIAL_SEC",
    "PURPOSE" : "MNG",
    "UDF_SOURCE" : "MNG",
    "VALUE" : "Non-Financial"
},{
            
    "LABEL" : "PPMG_SEDOL",
    "PURPOSE" : "PPMG",
    "UDF_SOURCE" : "PPMG",
    "VALUE" : "9885662"
}] },
    "UNITS" : "BONDS",
    "WI_FLAG" : "N"
}
},{
            
    "BND_CORP" : {
            
    "ACCRUAL_DT" : "6/4/2015",
    "AMT_ISU" : "1000000000",
    "ANNOUNCE_DT" : "5/28/2015",
    "CALC_TYPE" : "1",
    "CALL_MANDATORY" : "N",
    "CD_INSTMT_TYPE" : "UNKNOWN",
    "CHANGE_DT" : "2/1/2017 16:07:15.670",
    "COMPOUND_FLAG" : "0",
    "COUNTRY" : "NL",
    "COUPON_FIX" : "2.45",
    "COUP_FREQ" : "S",
    "CPN_TYPE" : "F",
    "CURRENCY" : "USD",
    "CUSIP" : "00084DAJ9",
    "CUSIP2_set" : { "CUSIP2_record" :[{
            
    "CODE" : "B",
    "IDENTIFIER" : "EK9442097"
},{
            
    "CODE" : "C",
    "IDENTIFIER" : "BYR8K93"
},{
            
    "CODE" : "I",
    "IDENTIFIER" : "US00084DAJ90"
}] },
    "CUSIP_ALIAS_set" : { "CUSIP_ALIAS_record" :[{
            
    "CODE" : "8001",
    "IDENTIFIER" : "824536165",
    "PURPOSE" : "DEBTID"
},{
            
    "CODE" : "145986",
    "IDENTIFIER" : "F_10345013",
    "PURPOSE" : "PPMG"
}] },
    "CUSIP_TYPE" : "CUSIP",
    "DATE_CONV" : "D30360",
    "DESC_INSTMT" : "ABN AMRO BANK NV",
    "END_ADJ_FLAG" : "N",
    "FIRST_PAY_DT" : "12/4/2015",
    "FIRST_SETTLE_DT" : "6/4/2015",
    "FLAG_144A" : "Y",
    "FLAG_EOM" : "N",
    "FLAG_ERISA" : "N",
    "FLAG_PERFORMING" : "Y",
    "FLAG_REG_RIGHTS" : "N",
    "ISSUER_ID" : "E97445",
    "ISSUE_DT" : "6/4/2015",
    "ISSUE_EXCHANGES_set" : { "ISSUE_EXCHANGES_record" :[{
            
    "EXCHANGE" : "AEX"
},{
            
    "EXCHANGE" : "NSD"
}] },
    "ISSUE_PRICE" : "99.78",
    "LEH_INDUSTRY" : "BANK",
    "LIQUIDITY" : "L",
    "MARKET" : "US",
    "MATURITY" : "6/4/2020",
    "MIN_LOT_SIZE" : "1000",
    "MIN_TRD_SIZE" : "200000",
    "MODIFIED_BY" : "sm_timestamp",
    "MTN" : "Y",
    "NOTES_set" : { "NOTES_record" :[{
            
    "EFF_DT" : "6/1/2015",
    "INSTMT" : "144A_RegRights exempt",
    "SEQ_NUM" : "2",
    "USER_ID" : "alogan"
},{
            
    "EFF_DT" : "1/1/2016",
    "INSTMT" : "144A_RegRights exempt",
    "SEQ_NUM" : "2",
    "USER_ID" : "alogan"
},{
            
    "EFF_DT" : "2/1/2017",
    "INSTMT" : "risk country per PPMG",
    "SEQ_NUM" : "10",
    "USER_ID" : "ntodorov"
}] },
    "NTL_FLAG" : "N",
    "PC_DAY_CONV" : "B",
    "PMT_ADJ_METH" : "F",
    "PMT_CAL" : "NY_Bank",
    "PMT_FREQ_TYPE" : "0",
    "PMT_LOCATION" : "D",
    "PRICE_AS_PCT" : "Y",
    "PUT_CALL" : " ",
    "PUT_MANDATORY" : "N",
    "RATING_set" : { "RATING_record" :[{
            
    "AGY" : "1",
    "DATE" : "5/20/2016",
    "VALUE" : "A1"
},{
            
    "AGY" : "2",
    "DATE" : "5/29/2015",
    "VALUE" : "A"
},{
            
    "AGY" : "3",
    "DATE" : "2/24/2017",
    "VALUE" : "A+"
},{
            
    "AGY" : "14",
    "DATE" : "3/20/2017",
    "VALUE" : "A-"
}] },
    "REVIEWED_BY" : "mtang",
    "RISK_COUNTRY" : "NL",

..................................................................................................

tried with ASSETS, ASSET and BND_CORP nodes but still getting output with only 1 column

encoidng error

Traceback (most recent call last):
File "json_to_csv.py", line 92, in
reduce_item(node, item)
File "json_to_csv.py", line 68, in reduce_item
reduce_item(key+'_'+str(sub_key), value[sub_key])
File "json_to_csv.py", line 72, in reduce_item
reduced_item[str(key)] = str(value)
UnicodeEncodeError: 'ascii' codec can't encode character u'\u201c' in position 49: ordinal not in range(128)