How to convert a dynamic JSON like file to a CSV file

Question

I have a file which looks exactly as below.

{"eventid" : "12345" ,"name":"test1","age":"18"}
{"eventid" : "12346" ,"age":"65"}
{"eventid" : "12336" ,"name":"test3","age":"22","gender":"Male"}

Think of the above file as event.json

The number of data objects may vary per line. I would like the following csv output. and it would be output.csv

eventid,name,age,gender
12345,test1,18
12346,,65
12336,test3,22,Male

Could someone kindly help me? I could accept the answer from an any scripting language (Javascript, Python and etc.).

GantTheWanderer · Accepted Answer · 2020-02-27 00:30:08Z

2

This code will collect all the headers dynamically and write the file to CSV.

Read comments in code for details:

import json

# Load data from file
data = '''{"eventid" : "12345" ,"name":"test1","age":"18"}
{"eventid" : "12346" ,"age":"65"}
{"eventid" : "12336" ,"name":"test3","age":"22","gender":"Male"}'''

# Store records for later use
records = [];

# Keep track of headers in a set
headers = set([]);

for line in data.split("\n"):
    line = line.strip();

    # Parse each line as JSON
    parsedJson = json.loads(line)

    records.append(parsedJson)

    # Make sure all found headers are kept in the headers set
    for header in parsedJson.keys():
        headers.add(header)

# You only know what headers were there once you have read all the JSON once.

#Now we have all the information we need, like what all possible headers are.

outfile = open('output_json_to_csv.csv','w')

# write headers to the file in order
outfile.write(",".join(sorted(headers)) + '\n')

for record in records:
    # write each record based on available fields
    curLine = []
    # For each header in alphabetical order
    for header in sorted(headers):
        # If that record has the field
        if record.has_key(header):
            # Then write that value to the line
            curLine.append(record[header])
        else:
            # Otherwise put an empty value as a placeholder
            curLine.append('')
    # Write the line to file
    outfile.write(",".join(curLine) + '\n')

outfile.close()

edited Feb 27, 2020 at 0:30

answered Jan 31, 2017 at 21:08

GantTheWanderer

1,2921 gold badge11 silver badges19 bronze badges

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

3 Comments

mmenschig Over a year ago

I'd probably go with this solution, now that you've added the handling of unknown headers to it. It would have been nice to know of that detail in advance.

user2539119 Over a year ago

{"eventid" : "12345" ,"name":"test1","age":18} {"eventid" : "12346" ,"age":65} {"eventid" : "12336" ,"name":"test3","age":22,"gender":"Male"} - If I pass age has integers it doesn't work and throws an error.

GantTheWanderer Over a year ago

@mmenschig Error says outfile.write(",".join(curLine) + '\n') TypeError: sequence item 0: expected string, int found. You should be able to determine why its failing from this message. Please try, what do you think this error means?

jq170727 · Accepted Answer · 2017-09-03 21:57:02Z

2

Here is a solution using jq.

If filter.jq contains the following filter

  (reduce (.[]|keys_unsorted[]) as $k ({};.[$k]="")) as $o   # object with all keys
| ($o  | keys_unsorted), (.[] | $o * . | [.[]])              # generate header and data
| join(",")                                                  # convert to csv

and data.json contains the sample data then

$ jq -Mrs -f filter.jq data.json

produces

eventid,name,age,gender
12345,test1,18,
12346,,65,
12336,test3,22,Male

answered Sep 3, 2017 at 21:57

jq170727

15k6 gold badges52 silver badges61 bronze badges

Comments

mmenschig · Accepted Answer · 2017-01-31 20:52:19Z

0

Here's a Python solution (should work in both Python 2 & 3). I'm not proud of the code, as there's probably a better way to do this (using the csv module) but this gives you the desired output.

I've taken the liberty of naming your JSON data data.json and I'm naming the output csv file output.csv.

import json

header = ['eventid', 'name', 'age', 'gender']

with open('data.json', 'r') as infile, \
     open('outfile.csv', 'w+') as outfile:

    # Writes header row
    outfile.write(','.join(header))
    outfile.write('\n')

    for row in infile:
        line = ['', '', '', ''] # I'm sure there's a better way
        datarow = json.loads(row)

        for key in datarow:
            line[header.index(key)] = datarow[key]

        outfile.write(','.join(line))
        outfile.write('\n')

Hope this helps.

answered Jan 31, 2017 at 20:52

mmenschig

1,12814 silver badges24 bronze badges

2 Comments

user2539119 Over a year ago

Hi, this is great with one big problem (Which I wasn't probably clear about). How about if I don't know of the possible headers? let's say my next line of the data.json file has "address" now. Is there a way to tackle that ?

mmenschig Over a year ago

Ha, that changes the entire nature of this exercise. So you're saying you don't know what the actual header row is and if it's a new entry to append it to the end?

Saniya syed qureshi · Accepted Answer · 2018-01-08 07:58:48Z

Using Angularjs with ngCsv plugin we can generate csv file from desired json with dynamic headers.

Run in plunkr

// Code goes here

 var myapp = angular.module('myapp', ["ngSanitize", "ngCsv"]);

 myapp.controller('myctrl', function($scope) {
   $scope.filename = "test";
   $scope.getArray = [{
     label: 'Apple',
     value: 2,
     x:1,
   }, {
     label: 'Pear',
     value: 4,
     x:38
   }, {
     label: 'Watermelon',
     value: 4,
     x:38
   }];


   $scope.getHeader = function() {
    var vals = [];
    for( var key in $scope.getArray ) {
    for(var k in $scope.getArray[key]){
      vals.push(k);
     }
     break;
    }
    return vals;
    
   };

 });

<!DOCTYPE html>
<html>
  <head>
    <link href="https://netdna.bootstrapcdn.com/bootstrap/3.0.0/css/bootstrap.min.css" rel="stylesheet">

    <script src="https://ajax.googleapis.com/ajax/libs/angularjs/1.4.7/angular.min.js"></script>

   <script src="https://ajax.googleapis.com/ajax/libs/angularjs/1.4.7/angular-sanitize.min.js"></script>
   
	<script src="https://cdnjs.cloudflare.com/ajax/libs/ng-csv/0.3.6/ng-csv.min.js"></script>
   

  </head>


  <body>

    <div ng-app="myapp">

      <div class="container" ng-controller="myctrl">

        <div class="page-header">

          <h1>ngCsv <small>example</small></h1>

        </div>
       
        

        <button class="btn btn-default" ng-csv="getArray" csv-header="getHeader()" filename="{{ filename }}.csv" field-separator="," decimal-separator=".">Export to CSV with header</button>

       
      </div>
    </div>
  </body>
</html>

tonythewest · Accepted Answer · 2017-01-31 20:30:08Z

-1

var arr = $.map(obj, function(el) { return el });
var content = "";
for(var element in arr){
    content += element + ",";
}

var filePath = "someFile.csv";
var fso = new ActiveXObject("Scripting.FileSystemObject");
var fh = fso.OpenTextFile(filePath, 8, false, 0);
fh.WriteLine(content);
fh.Close();

edited Jan 31, 2017 at 20:30

answered Jan 31, 2017 at 20:25

tonythewest

5264 silver badges14 bronze badges

2 Comments

user2539119 Over a year ago

Sorry, I am not sure what you meant about. I want to dynamically capture the column headers.

user2539119 Over a year ago

initial file would be something like events.json and the output will be in output.csv. Do you follow ? Where would I pass the location of the event.json file on this script ?

Collectives™ on Stack Overflow

How to convert a dynamic JSON like file to a CSV file

5 Answers 5

3 Comments

Comments

2 Comments

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

3 Comments

Comments

2 Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related