3

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"
    }
  ]
}
  • You could try contatcing the programmer and requestign assistance with building (or even that they mail you an .EXE). I have always found freeware developers to be extremly helpful/ – Mawg Nov 8 '16 at 10:56
  • 1
    @Mawg I did reach out of the developers of the device (and its api's) I get the data from and the format I can get it from in JSON is the only way to get it but I resolved with Python just fine with a little work, research and testing. – Pimp Juice IT Nov 14 '16 at 14:08
  • I am glad that you got it resolved. Obviously, before you coded, you Googled for JSON to CSV conversion but found notthing. Maybe you could help others by posting your egneral purpose converter to GitHub and giving ht eURL here? – Mawg Nov 14 '16 at 14:21
  • 1
    See my post here with working examples on the answer I accepted to give you a rough idea of how to put the logic together in case anyone wants to see what I used. This should give you a rough idea of a method that should work just fine but manipulate it accordingly based on your data structure, etc.: stackoverflow.com/questions/40500948/… – Pimp Juice IT May 3 '18 at 16:28
3

Python is:

  • Free, Gratis & Open Source including commercial use
  • Available for windows, along with just about everything else, although recent versions have dropped Windows XP support.
  • Can definately 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.

  • Just referring to Python is hardly answer. Can you also give an example how this conversion would look like and how easy that script would be? – parvus Mar 13 at 9:49
  • 1
    @parvus - added some example code and discussion. – Steve Barnes Mar 14 at 6:25
3

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"
  • I get The term 'ConvertFrom-Json' is not recognized as the name of a cmdlet, function, script file, or operable program – golimar Oct 25 '17 at 13:30
  • 1
    Update PowerShell to version 5 – Brad Mar 16 at 3:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.