Examples of general JSON to CSV conversion

General

The JSON format, XML and CSV text files are workhorses for exchanging IT data. One advantage that JSON has over CSV is that it can contain structured data. JSON is also much easier to use than XML. For this reason, the JSON format has now established itself as the preferred method for exchanges of information in the digital world. JSON is also often used in connection with HTTP REST web services. It goes without saying that you can make use of this with the FlowHeater HTTP REST API Adapter.

Not all systems are able to process JSON and/or CSV files, so these two formats often have to be combined or converted from one to the other. Several examples are set out below, which demonstrate how this can be achieved with FlowHeater using the JSON Adapter.

We start with a simple conversion from JSON to CSV. For comparison, we then convert back the CSV data this produces into a JSON file in a second step.

Convert JSON to CSV (simple)

JSON to CSV (simple)We have a JSON file and wish to convert this to CSV format. To accomplish this, we need the JSON Adapter on the READ side and the TextFile Adapter on the WRITE side.

We select the JSON file in the JSON Adapter, which automatically reads the structure of the data records. This structure is displayed under the “Fields/Data types” tab. The existing and recognized data types in the JSON format are automatically transferred. In the simple case, as in this example, it is a flat JSON file and you can transfer the fields verbatim to the WRITE side. The JSON Adapter reads each major element in the JSON file as a data record, which is then output as a single line to the CSV file. On the WRITE side, an AutoID Heater is used to count through the existing data records, see output.

This example is contained in the program folder under

“…FlowHeater V4\examples\JsonAdapter\general\1_JSON-to-CSV (simple).fhd ”

Convert CSV back to JSON (simple)

CSV to JSON (simple)We proceed as follows to convert the newly created CSV file back into JSON format. To achieve this we of course need the TextFile Adapter on the READ side, to read the CSV file, and the JSON Adapter on the WRITE side, to write a new JSON file.

Having selected the CSV file on the READ side, we can load the column names from the headings row in the first line and then connect the fields to the JSON file on the WRITE side. Since we want to create an identical JSON file as we started with, we drag all fields except for the ID field to the WRITE side. All we now have to do is tell the JSON Adapter what the JSON output filename should be, and we’re done.

The example may not appear spectacular, but it does demonstrate how simple it can be to convert between CSV files and the JSON format with FlowHeater.

This example Definition is contained in the program folder under

“…FlowHeater V4\examples\JSONAdapter\general\2_CSV-to-JSON (simple).fhd “

Convert JSON to CSV with more structure (complex)

JSON to CSV (complex)This time we want to read a JSON file that does not have a flat structure like the one above. This JSON file contains nested objects and arrays. There are also several arithmetic operations on 1:n array elements.

Let's start by creating a new definition with a JSON Adapter on the READ side and a TextFile Adapter on the WRITE side.
As soon as we select the file “JSON-to-CSV (complex).json” in the JSON Adapter, it will attempt to read in the existing JSON structure and displays whatever is recognized under “Fields / Data types”, see the image on the right.

The JSON Adapter reads all the relevant array elements from the first record and displays them. In this case these are the three product lines for the invoice, called here “positions”.

You could now connect these three positions to the CSV output page and everything would be fine. However, if you try to process the second example Definition “JSON-to-CSV (complex)-2.fhd” in the folder you will find that the JSON Adapter will only try to read three positions, as previously defined. When there are more than three, the others are ignored. How to dynamically handle this with multiple processing steps is explained in the final example “Reading from a nested JSON structure and creating a CSV file (expert)”, see below.

In this case, it is better to connect the “Positions” array directly to the CSV output so that the structure is dynamically transferred and written to the CSV file that results. The nested objects are separated from each other with special separators. In the default settings, curly braces “{#}” surround object elements, while square brackets “[#]” surround array elements. These separators can be customized in the JSON Adapter in the “Advanced” tab.

You can find the complete example in the program folder under

“…FlowHeater V4\examples\JSONAdapter\general\3_JSON-to-CSV (complex).fhd ”

Converting CSV to JSON with complex structures

CSV to JSON (complex)Now we dive deeper. Let's assume we receive a CSV file in which invoices with 1-n product lines (positions) for various customers are summarized. These CSV lines are to be converted into JSON format. Of course, each invoice and position should be represented by a separate JSON object. These JSON invoice objects must then be integrated into the respective invoice within a JSON array, see illustration on the right.

Configuring the TextFile Adapter for CSV file on the READ side should be obvious, so let's go straight to the JSON Adapter configuration on the WRITE side. We need to configure the following fields, see the illustration on the right, in order for the JSON Adapter to know which fields to use to group the data into the different objects, the “Key field for grouping” option must be checked against “InvNum ” and “ProdNum ” fields. Make sure that the field “Positions” is created as a JSON array type and the Position field as a JSON object type, see illustration on the right. This enables the JSON Adapter to output the required format.

You can find a complete example Definition in the program folder under

“…FlowHeater V4\examples\JSONAdapter\general\4_Convert-CSV-to-JSON.fhd”

Read a nested JSON structure and create a CSV file (Expert)

We now want to read the individual elements from the JSON file we just created and export them back to a CSV file.

The problem with this is that we cannot easily determine how many invoice items there are for each invoice in the JSON file. To solve this problem, all invoice items must be read dynamically from the JSON file. To do this, the JSON Adapter offers an option for dynamically reading all the contents of the JSON array “Positions” from the JSON file by specifying a “JPath”.

When you select a new file in the JSON Adapter, it attempts to read the structure of the JSON file. The possible options are then displayed in the JPath. By default, the complete JSON document is read and all the fields are displayed.

Now select the value “/Positions” from the list and click on “Reload from file”. The field list will now only display those fields that exist within the “Positions” array.

Now we encounter another problem, the fields “InvNum”, “Date ” and “CustNum ” are not available inside the array.

JSON to CSV (expert)The JSON Adapter offers an ingenious solution for this. You simply add three more fields to the field list and assign the values of higher-level fields to these. To do that, select one of the fields and click on “Accept value from parent node”. A dialog then opens that allows you to enter constant values or use the “...” button to conveniently select fields with the mouse, see illustration on the right.

In a dialog that opens next, you are only shown JSON elements on the parent level to the current node. Select the required JSON element from these and confirm the dialog with OK. Alternatively, you can double-click on the element and this would accept that value and close the dialog.
All that is left to do is to transfer the fields we want to the CSV file and run the Definition.

Note: In a similar way, it is possible to process more complex or nested JSON structures. It is also possible to extract more and in principle all values in several processing steps. This data can then be further enriched or processed using the InMemory Adapter.

You can find a complete example Definition in the program folder under

“…FlowHeater V4\examples\JSONAdapter\general\5_JSON-to-CSV (Expert).fhd”

Summary

It is very easy to read and write JSON files with FlowHeater’s JSON Adapter. Here we have described how the JSON Adapter works in general. When it is used in conjunction with the HTTP REST API Adapter, FlowHeater can dynamically call and query web services with JSON data. There will be further examples to illustrate this in the near future.