Cinchoo ETL - Converting CSV to JSON with Nested Objects and Arrays

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

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 CSV input file.

Listing 3.1.1. Sample CSV Data Input File (Emp.csv)

id,name,nestedobject/id,nestedobject/name,nestedarray/0/name,
   nestedarray/0/city,nestedarray/1/name,nestedarray/1/city
1,Tomy,nestedobject_id1,nestedobject_name1,nestedarray0_name_1,
  nestedarray0_city_1,nestedarray1_name_1,nestedarray1_city_1
2,Mark,nestedobject_id2,nestedobject_name2,nestedarray0_name_2,
  nestedarray0_city_2,nestedarray1_name_2,nestedarray1_city_2

Above CSV is dynamic, may contain nested objects, arrays. CSV fields are separated by '/' character to represent complex data. This is not a normal CSV file, sometimes we may encounter such files to represent nested structures.

Listing 3.1.2. Expected JSON Output file (Emp.json)

[
  {
    "id": "1",
    "name": "Tomy",
    "nestedobject": {
      "id": "nestedobject_id1",
      "name": "nestedobject_name1"
    },
    "nestedarray": [
      {
        "name": "nestedarray0_name_1",
        "city": "nestedarray0_city_1"
      },
      {
        "name": "nestedarray1_name_1",
        "city": "nestedarray1_city_1"
      }
    ]
  },
  {
    "id": "2",
    "name": "Mark",
    "nestedobject": {
      "id": "nestedobject_id2",
      "name": "nestedobject_name2"
    },
    "nestedarray": [
      {
        "name": "nestedarray0_name_2",
        "city": "nestedarray0_city_2"
      },
      {
        "name": "nestedarray1_name_2",
        "city": "nestedarray1_city_2"
      }
    ]
  }
]

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 Conversion

The below sample code shows how to convert such complex CSV file into JSON format. No POCO object structure is needed.

Listing 3.2.1. CSV to JSON file conversion

private static void Conversion()
{
    string csv = @"id,name,nestedobject/id,nestedobject/name,
    nestedarray/0/name,nestedarray/0/city,nestedarray/1/name,nestedarray/1/city
1,Tomy,nestedobject_id1,nestedobject_name1,nestedarray0_name_1,
    nestedarray0_city_1,nestedarray1_name_1,nestedarray1_city_1
2,Mark,nestedobject_id2,nestedobject_name2,nestedarray0_name_2,
    nestedarray0_city_2,nestedarray1_name_2,nestedarray1_city_2";

    using (var w = new ChoJSONWriter(Console.Out)
        .Configure(c => c.DefaultArrayHandling = false)
        )
    {
        using (var r = ChoCSVReader.LoadText(csv).WithFirstLineHeader()
            .Configure(c => c.NestedColumnSeparator = '/')
            )
            w.Write(r);
    }
}

Create an instance of ChoCSVReader object for reading emp.csv file. Then create an instance of ChoJSONWriter for producing JSON (emp.json) file with below configuration settings:

• Configure(c => c.DefaultArrayHandling = false) - tells the JSON writer to turn off default array handling while writing objects
• Configure(c => c.NestedColumnSeparator = '/') - tells the CSV reader nested column separator

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

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 Code Project articles:

• Cinchoo ETL - CSVWriter
• Cinchoo ETL - JSONReader

History

• 7^th October, 2021: Initial version