Convert JSON to CSV

Question

I'm looking for software that will convert JSON formatted data into CSV formatted data with or without header values.

I'd prefer for this software to be able to take the JSON formatted file from a file and then convert it to a CSV file that I specify which works from command line passing arguments accordingly. If there's something that only works with URLs then I'm happy to look at these as well if the formatted data can then be exported to a flat file.

Requirements

1. Open source or free to use for commercial business
2. Must be able to run on current Windows OSes such as Windows 10, Server 2012, etc.
3. Be command line driven passing parameters in as arguments
   • Preferably as a compiled executable file but instruction how to run and pass arguments accordingly from command line otherwise from Windows would work as well

I've found a few things out there on GitHub in JS but I'm having trouble compiling the code with JSC getting error, etc. so I'm not sure if those are Windows compatible or not and I'm new to compiling JS code with Visual Studio or .NET JSC.exe so I may be going about it the wrong way; or just misunderstanding the process since I'm not really an application developer.

---

Sample Data Unexpected Results

    {
  "success": true,
  "devs": [
    {
      "id": 911911,
      "name": "Test Co",
      "tags": [
        {
          "id": 71111,
          "name": "Tank1PSI",
          "dataType": "Float",
          "description": "",
          "alarm": "",
          "value": 0,
          "quality": "good",
          "TagId": 9
        },
        {
          "id": 72222,
          "name": "Tank2",
          "dataType": "Float",
          "description": "",
          "alarm": "",
          "value": 0,
          "quality": "good",
          "TagId": 1
        },
        {
          "id": 73333,
          "name": "FillTank1",
          "dataType": "Bool",
          "description": "",
          "alarm": "",
          "value": 0,
          "quality": "good",
          "TagId": 3
        },
        {
          "id": 74444,
          "name": "FillTank2",
          "dataType": "Bool",
          "description": "",
          "alarm": "",
          "value": 0,
          "quality": "good",
          "TagId": 6
        },
        {
          "id": 75555,
          "name": "Line1",
          "dataType": "Bool",
          "description": "",
          "alarm": "",
          "value": 0,
          "quality": "good",
          "TagId": 7
        },
        {
          "id": 76666,
          "name": "Line2",
          "dataType": "Bool",
          "description": "",
          "alarm": "",
          "value": 0,
          "quality": "good",
          "TagId": 5
        },
        {
          "id": 77777,
          "name": "Tank2PSI",
          "dataType": "Float",
          "description": "",
          "alarm": "",
          "value": 0,
          "quality": "good",
          "TagId": 8
        }
      ],
      "lastSynchroDate": "2016-11-09T03:00:00Z"
    }
  ]
}

Answer 1

Python is:

• Free, Gratis & Open Source including commercial use
• Available for windows, along with just about everything else, although recent versions (post 2.5.4) have dropped Windows XP support and Windows 7 support has gone as of 3.9.
• Can definitely be command line driven
• Has a built in csv module/library that can output various dialects of csv file.
• Has a built in json module/library that can read and write json files or a number of json libraries available.
• Can potentially, with a very little work, access files that are:
• Zipped
• Tar format
• tar.gz format
• remote

Expanded

Reading the json is simple enough:

import json
data = json.load(open('filename'))

Then you hit a bit of a problem - your example data is not well structured for csv output. The data is a dictionary, (or fields), with a structure like:

• success: True
• 'devs': list len 1 of:
• 'id': int
• 'name': string
• 'tags': list len 7 of: records consisting of: 'id', 'name', 'dataType', 'description', 'alarm', 'value', 'quality', 'TagId'
• 'lastSynchroDate': datetime

I am having a very hard time%hist visualizing the look of a csv file for this maybe something sparse like:

success, devs:id, devs:name,devs:lastSynchroDate, devs:tags:id, devs:tags:name,...
True,,,,,,,,,
,911911,'Test Co',2016-11-09T03:00:00Z,,,,,,,,,,,,,
,,,,71111,'Tank1PSI','Float','','',0,'good',9
,,,,72222,'Tank2','Float','','',0,'good',1

Or duplicating the initial data columns for each entry:

success, devs:id, devs:name,devs:lastSynchroDate, devs:tags:id, devs:tags:name,...
True,911911,'Test Co',2016-11-09T03:00:00Z,71111,'Tank1PSI','Float','','',0,'good',9
True,911911,'Test Co',2016-11-09T03:00:00Z,72222,'Tank2','Float','','',0,'good',1

I am guessing that your top level has two entries with success = True & False then several devs entries each with If your data structure is fixed then you could write a structure specific script to output to csv or with a little more work you could generalize for this sort of nested structure.

If you don't need a header line your code could be as simple as:

success = data['success']
for dev in obj['devs']:
    ID = dev['id']
    name = dev['name']
    lastSynchroDate = dev['lastSynchroDate']
    for tag in dev['tags']:
        entry = [success, ID, name, lastSynchroDate, *tag.values()]
        text = ','.join([repr(s) for s in entry])
        print(text)

which produces:

True,911911,'Test Co','2016-11-09T03:00:00Z',71111,'Tank1PSI','Float','','',0,'good',9
True,911911,'Test Co','2016-11-09T03:00:00Z',72222,'Tank2','Float','','',0,'good',1
True,911911,'Test Co','2016-11-09T03:00:00Z',73333,'FillTank1','Bool','','',0,'good',3
True,911911,'Test Co','2016-11-09T03:00:00Z',74444,'FillTank2','Bool','','',0,'good',6
True,911911,'Test Co','2016-11-09T03:00:00Z',75555,'Line1','Bool','','',0,'good',7
True,911911,'Test Co','2016-11-09T03:00:00Z',76666,'Line2','Bool','','',0,'good',5
True,911911,'Test Co','2016-11-09T03:00:00Z',77777,'Tank2PSI','Float','','',0,'good',8

suitable for piping into a .csv file.

Answer 2

Since you are in a Windows environment have you considered PowerShell? It meets all of your criteria and you already have it available (no need to install anything). This command will do what you want:

# With headers
(Get-Content -Path $pathToJsonFile | ConvertFrom-Json) `
| ConvertTo-Csv -NoTypeInformation `
| Set-Content $pathToOutputFile

# Without headers
(Get-Content -Path $pathToJsonFile | ConvertFrom-Json) `
| ConvertTo-Csv -NoTypeInformation `
| Select-Object -Skip 1 `
| Set-Content $pathToOutputFile

Or from a DOS prompt:

powershell "(Get-Content -Path $pathToJsonFile | ConvertFrom-Json) | ConvertTo-Csv -NoTypeInformation | Select-Object -Skip 1 | Set-Content $pathToOutputFile"

Answer 3

Although not free, there's a cheap (~5$) option - Data Transformer (disclaimer - I'm its developer). It converts between CSV, JSON, XML, and YML locally.

It offers a number of conversion settings (with sensible defaults) so you can match the data for your purposes.

You can check it out on the Mac App Store or the Microsoft Store.