Tag: CSV

A customer was looking for help how to convert big JSON data to CSV using JSONBuddy. In general, the JSON editor offers several functionalities without the need to load the document into the tool (like pretty-printing and removing any whitespace). This allows the processing of much larger data as usually supported by other software. The JSON input of the customer was about 360 MB with millions of lines. For this article, I’m using a sample document of more than 400 MB.

So this is my recommendation on how to convert a large input file using JSONBuddy:

• Open the JSON document in the editor using the Large File view. This way you can check the structure of the JSON data and you can see the JSONPointer location to set the starting point for the conversion. In addition, the conversion also supports converting JSON input starting with a top-level array.
• Use the “JSON to CSV file…” command from the built-in File Explorer window. In this case, the editor doesn’t load the whole document at once. However, it can take a while until the following conversion dialog is displayed. My middle-class PC shows the dialog after 80 seconds for the 438 MB sample file below:

• The conversion dialog is used to set the starting point, the columns, and the output format. Please note that the list of JSON values to the right is only updated after you moved the input focus away from the JSONPointer edit field at the left:
  

• After clicking “Convert”, the output CSV is written as a new document next to the JSON input with .csv as the file extension. This takes about 110 seconds on my system and generates 130 MB of CSV data.
• Afterward, you can load the CSV as plain text into the editor. Again using the Large File view.

JSONBuddy already supports converting JSON data to CSV (and also back from CSV to JSON…). You can learn more about the standard JSON to CSV dialog on the help pages. Starting with JSONBuddy 5, there is a new way to generate CSV output from JSON input in the editor: The “Convert JSON to pointer/value pairs” command.

To illustrate this method, we take the following JSON input as an example:

And we use the “JSON | Convert JSON to pointer/value pairs” command from the editor, which is applied to the active document, to get the following CSV output:

This CSV output contains all the data from the JSON input to create the original JSON structure again.

1. The left column is the JSONPointer where the actual value is located. The pointers contain the objects, arrays and property name information of the JSON input.
2. The right column is the JSON value. This also means the number of lines in the CSV output equals the number of single JSON values in the input data.

Of course, you can edit and save the CSV data in the JSON editor and take it as input to other tools like Excel.