Convert a json file to csv using shell script without using jq?

Question

I want to convert a json file to csv using shell script without using jq. Is it possible?

Here is a json :

{
  "id": "0001",
    "type": "donut",
    "name": "Cake",
    "ppu": 0.55,
},
{
"id": "0002",
    "type": "donut2",
    "name": "Cake2",
    "ppu": 0.5522,
}

I don't want to use jq.

I want to store it in a csv file.

Good luck with that. Unless you can be sure that the file is formatted in a particular fashion, and there are no escape sequences that need to be processed specially, this will be difficult. — Barmar
– Barmar, Commented Nov 27, 2019 at 18:12
BTW, that's not valid JSON. If it's an array, it needs to have [] around it. — Barmar
– Barmar, Commented Nov 27, 2019 at 18:13
A shell script is not a good way to process most data; the shell's purpose is to run other commands that do the processing. — chepner
– chepner, Commented Nov 27, 2019 at 18:14
Can you make that code valid JSON and provide what the CSV output should look like? — Enlico
– Enlico, Commented Nov 27, 2019 at 18:17
jq -r '.[] | [.id, .type, .name, .ppu] | @csv' foo.json > foo.csv btw, assuming that's supposed to be an array of valid JSON objects. — Shawn
– Shawn, Commented Nov 27, 2019 at 18:46

Shawn · Accepted Answer · 2019-11-27 19:30:04Z

14

Bare-bones core-only perl one-liner version, to complement the python and ruby ones already given:

perl -MJSON::PP -0777 -nE '$,=","; say @$_{"id","type","name","ppu"} for @{decode_json $_}' input.json

A more robust one would use a more efficient non-core JSON parser and a CSV module to do things like properly quote fields when needed, but since your sample data doesn't include such fields I didn't bother. Can if requested.

And the unrequested jq version, because that really is the best approach whether you want it or not:

jq -r '.[] | [.id, .type, .name, .ppu] | @csv' input.json

edited Nov 27, 2019 at 19:30

answered Nov 27, 2019 at 19:14

Shawn

53.9k3 gold badges29 silver badges74 bronze badges

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

5 Comments

Matias Barrios Over a year ago

Sometimes I think I should really learn Perl. But then I see that syntax and... Naaaah! . Good answer though.

Shawn Over a year ago

@MatiasBarrios If it was a standalone script and not a one liner meant for including in a shell script, I'd write it out more verbosely and with fewer shortcuts (but as is it's no worse than your Python and Ruby versions to my eye). You should learn perl; it's a nifty language.

Shubham Over a year ago

Could you please explain the code. I want to write it according to the json file from the project I am working on. perl -MJSON::PP -0777 -nE '$,=","; say @$_{"id","type","name","ppu"} for @{decode_json $_}' input.json -- Doesnot work for me Error msg on bash -- unexpected end of string while parsing JSON string, at character offset 82 (before ",\r\n{\r\n"id": "000...") at -e line 1.

Shawn Over a year ago

@Shubham You need valid json; what you posted in the question isn't even if you wrap the objects in an array because of trailing commas in the objects. If that's what your real data is like, yeah, json parsers aren't going to accept it.

Shubham Over a year ago

"errors": false, "messages": [], "requestId": null, "results": { "id": "2c91808a6d982ce2016dacd4119876eb", "inactive": false, "deleted": false, "lockVersion": null, "lastModifiedDate": null, "lastModifiedBy": "MONITORING SERVICE", "createdDate": 1570562642328, "createdBy": "admin" } there is more to this json, please help me wih dynamic code.

Matias Barrios · Accepted Answer · 2019-11-27 18:38:17Z

0

Bash is not the tool to do this at all. But if for some reason you cannot install jq, you can simply use Python 3, which comes by default in most distros of Linux and in MacOS.

#!/usr/local/bin/python3

import json

objs=json.loads("""
[
    {
        "id": "0001",
        "type": "donut",
        "name": "Cake",
        "ppu": 0.55
    },
    {
        "id": "0002",
        "type": "donut2",
        "name": "Cake2",
        "ppu": 0.5522
    }
]
""")
for item in objs :
    print("{}{}{}{}{}{}{}".format(item['id'],",",item['type'],",",item['name'],",",item['ppu']))

If you do not have Python 3 either, you can then do it in Ruby, which also comes by default in most distros and MacOS :

#!/usr/bin/ruby

require 'json'

content = '
[
    {
        "id": "0001",
        "type": "donut",
        "name": "Cake",
        "ppu": 0.55
    },
    {
        "id": "0002",
        "type": "donut2",
        "name": "Cake2",
        "ppu": 0.5522
    }
]
'

JSON.parse(content).each { |item| puts "#{item['id']},#{item['type']},#{item['name']},#{item['ppu']}" }

You can then redirect the output to a file :

script.rb > output.csv

And thats it.

Nevertheless, if you can be completely sure of the format of your input, you can do some bash magic, specially using awk. But as others also said, please don't do that.

answered Nov 27, 2019 at 18:38

Matias Barrios

5,0864 gold badges31 silver badges71 bronze badges

1 Comment

glenn jackman Over a year ago

The ruby can be simpler if you use the csv module as well: require 'csv'; csv = CSV.new(STDOUT) then JSON.parse(content).each {|item| csv << item.values} -- if the quotes should always be output for each field, then csv = CSV.new(STDOUT, force_quotes: true)

Collectives™ on Stack Overflow

Convert a json file to csv using shell script without using jq?

2 Answers 2

5 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related