Cinchoo ETL - Merging JSON Array Values to Single CSV Column

• Download Cinchoo ETL source
• Download Cinchoo ETL binary (.NET Core)
• Download Cinchoo ETL binary (.NET Framework)

1. Introduction

ChoETL is an open source ETL (extract, transform and load) framework for .NET. It is a code based library for extracting data from multiple sources, transforming, and loading into your very own data warehouse in .NET environment. You can have data in your data warehouse in no time.

This tip talks about generating CSV file from JSON format using Cinchoo ETL framework. It is very simple to use, with few lines of code, the conversion can be done. You can convert large files as the conversion process is stream based, quite fast and with low memory footprint.

2. Requirement

This framework library is written in C# using .NET 4.5 / .NET Core Frameworks.

3. How to Use

3.1 Sample Data

Let's begin by looking into a simple example of converting the below JSON input file.

Listing 3.1.1. Sample JSON Data Input File (Emp.json)

{
  "id": 2,
  "name": "I.1.A.2",
  "activeFlag": true,
  "recipients": [ "idenity1", "idenity2" ]
}

As JSON message is hierarchical and structural format, you will have to flatten out in order to produce CSV file. In above 'recipients' is a JSON array, which needs to be converted to single CSV column as shown below. Also, the values needs to be separated by:

Listing 3.1.2. CSV Data Output File (Emp.csv)

id,name,activeFlag,recipients
2,I.1.A.2,True,idenity1;idenity2

The first thing to do is to install ChoETL.JSON / ChoETL.JSON.NETStandard nuget package. To do this, run the following command in the Package Manager Console.

.NET Framework

Install-Package ChoETL.JSON

.NET Core

Install-Package ChoETL.JSON.NETStandard

Now add ChoETL namespace to the program.

using ChoETL;

3.2 Quick Conversion

This approach shows how to convert JSON file to CSV format with little piece of code. No setup / POCO class are needed.

Listing 3.2.1. Quick JSON to CSV file conversion

private static void QuickConversion()
{
    StringBuilder csv = new StringBuilder();

    using (var r = ChoJSONReader.LoadText(json))
    {
        using (var w = new ChoCSVWriter(csv)
            .WithFirstLineHeader()
            .UseNestedKeyFormat(false)
            .Configure(c => c.ArrayValueSeparator = ';')
            )
        {
            w.Write(r);
        }
    }
    Console.WriteLine(csv.ToString());
}

Create an instance of ChoJSONReader object for reading emp.json file. Then create an instance of ChoCSVWriter for producing CSV (emp.csv) file with the below configuration settings:

• UseNestedKeyFormat(false) - tells the CSV writer to not to use nested key format on array / collection fields. This will trigger to combine the collection values.
• Configure(c => c.ArrayValueSeparator = ';') - tells the CSV writer to use specified separator when combining collection / array values.

Sample fiddle: https://dotnetfiddle.net/ukpyj6

3.3 Using POCO Object

This approach shows you how to define POCO entity class and use them for the conversion process. This approach is more type safe and fine control over the conversion process. Once the objects are populated with json values, using LINQ to compose the data for the desired CSV format.

Listing 3.3.1. Mapping Class

public class Emp
{
    public int Id { get; set; }
    public string Name { get; set; }
    public bool ActiveFlag { get; set; }
    public string[] Recipients { get; set; }
}

Then use this class as below to do the conversion of the file.

Listing 3.3.2. Using POCO object to convert JSON to CSV file

private static void UsingPOCO()
{
    StringBuilder csv = new StringBuilder();

    using (var r = ChoJSONReader<Emp>.LoadText(json)
        )
    {
        using (var w = new ChoCSVWriter<Emp>(csv)
            .WithFirstLineHeader()
            .UseNestedKeyFormat(false)
            .Configure(c => c.ArrayValueSeparator = ';')
            )
        {
            w.Write(r);
        }
    }
    Console.WriteLine(csv.ToString());
}

Sample fiddle: https://dotnetfiddle.net/dWVPvA

3.4 Using Projection

This approach shows how to use the LINQ projection method to convert the JSON file to CSV file. As you may know, JSON is a hierarchical, relational, and structured data, and CSV is not. If you have the objects produced from JSON reader in hierarchical format, you must flatten out using LINQ projection and feed them to CSV writer to create the CSV file. The sample below shows how to do it.

Listing 3.4.1. Using Projection to convert JSON to CSV file

private static void UsingProjection()
{
    StringBuilder csv = new StringBuilder();

    using (var r = ChoJSONReader.LoadText(json)
        )
    {
        using (var w = new ChoCSVWriter(csv)
            .WithFirstLineHeader()
            )
        {
            w.Write(r.Select(r1 =>
            {
                return new
                {
                    r1.id,
                    r1.name,
                    r1.activeFlag,
                    recipients = String.Join(";", r1.recipients)
                };
            }
            ));
        }
    }
    Console.WriteLine(csv.ToString());
}

Sample fiddle: https://dotnetfiddle.net/FpNPq9

3.5 Using Selection

This approach shows how to use the selection method in combination with flattening node to convert the JSON file to CSV file. As you may know, JSON is a hierarchical, relational, and structured data, and CSV is not. This approach is useful when your JSON is complex, you want to cherry pick the items using JSONPath to produce expected CSV file. The sample below shows how to do it.

Listing 3.5.1. Sample JSON Data Input File (Emp.json)

{
  "id": 2,
  "name": "I.1.A.2",
  "activeFlag": true,
  "recipients": [
    {
      "id": 3,
      "identityName": "idenity1",
      "fullName": "FullName1"
    },
    {
      "id": 4,
      "identityName": "identity2",
      "fullName": "FullName2"
    }
  ]
}

Listing 3.5.2. Using Selection to convert JSON to CSV file

private static void UsingSelection()
{
    StringBuilder csv = new StringBuilder();

    using (var r = ChoJSONReader.LoadText(json)
        .WithField("id")
        .WithField("name")
        .WithField("activeFlag")
        .WithField("recipients", jsonPath: "$..recipients[*]..identityName")
        )
    {
        using (var w = new ChoCSVWriter(csv)
            .WithFirstLineHeader()
            .UseNestedKeyFormat(false)
            .Configure(c => c.ArrayValueSeparator = ';')
            )
        {
            w.Write(r);
        }
    }
    Console.WriteLine(csv.ToString());
}

Sample fiddle: https://dotnetfiddle.net/cEuD3x

Please refer to other similar articles for conversion of JSON to CSV

• Cinchoo ETL - Quick Start: Converting JSON to CSV File
• Cinchoo ETL - Converting Complex Nested JSON to CSV

For more information about Cinchoo ETL, please visit the below CodeProject articles:

• Cinchoo ETL - CSVWriter
• Cinchoo ETL - JSONReader

History

• 14^th September, 2021: Initial version