Click here to Skip to main content
15,032,967 members
Articles / Programming Languages / C#
Article
Posted 27 Jun 2017

Tagged as

Stats

10.5K views
212 downloads
3 bookmarked

Cinchoo ETL - Quick Start: Converting JSON to CSV File

Rate me:
Please Sign up or sign in to vote.
2.83/5 (3 votes)
9 Sep 2021CPOL3 min read
Quick tutorial about converting JSON to CSV file using Cinchoo ETL

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 article 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 3.x Framework.

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 Employee JSON Data Input File (emp.json)
[
    {
        "firstName": "John",
        "lastName": "Smith",
        "age": 25,
        "address": {
            "streetAddress": "21 2nd Street",
            "city": "New York",
            "state": "NY",
            "postalCode": "10021"
        },
        "phoneNumber": [
            {
                "type": "home",
                "number": "212 555-1234"
            },
            {
                "type": "fax",
                "number": "646 555-4567"
            }
        ]
    },
    {
        "firstName": "Tom",
        "lastName": "Mark",
        "age": 50,
        "address": {
            "streetAddress": "10 Main Street",
            "city": "Edison",
            "state": "NJ",
            "postalCode": "08837"
        },
        "phoneNumber": [
            {
                "type": "home",
                "number": "732 555-1234"
            },
            {
                "type": "fax",
                "number": "609 555-4567"
            }
        ]
    }
]

As JSON message is hierarchical and structural format, you will have to flatten out in order to produce CSV file.

Let's say you wanted to produce the below CSV formatted output.

Listing 3.1.2. Employee CSV Data Output File (emp.csv)
FirstName,LastName,Age,StreetAddress,City,State,PostalCode,Phone,Fax
John,Smith,25,21 2nd Street,New York,NY,10021,212 555-1234,646 555-4567
Tom,Mark,50,10 Main Street,Edison,NJ,08837,732 555-1234,609 555-4567

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
JavaScript
private static void QuickConversion()
{
    using (var csv = new ChoCSVWriter("emp.csv").WithFirstLineHeader())
    {
        using (var json = new ChoJSONReader("emp.json")
            .WithField("FirstName")
            .WithField("LastName")
            .WithField("Age", fieldType: typeof(int))
            .WithField("StreetAddress", jsonPath: "$.address.streetAddress", isArray: false)
            .WithField("City", jsonPath: "$.address.city", isArray: false)
            .WithField("State", jsonPath: "$.address.state", isArray: false)
            .WithField("PostalCode", jsonPath: "$.address.postalCode", isArray: false)
            .WithField("Phone", jsonPath: "$.phoneNumber[?(@.type=='home')].number", isArray: false)
            .WithField("Fax", jsonPath: "$.phoneNumber[?(@.type=='fax')].number", isArray: false)
        )
        {
            csv.Write(json);
        }
    }
}

Create an instance of ChoCSVWriter for producing CSV (emp.csv) file. Then create an instance of ChoJSONReader object for reading emp.json file. Using 'WithField' method, define the specs of each field with optional JSONPath as shown above. Voilà, CSV file conversion happened with this little piece of code.

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

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 like doing property validation, consuming callback machanism, etc.

First, create a class with properties along with JSONPath expression to it in a flat out structure. Below, you will find an example of a class which does this.

Listing 3.3.1. Mapping Class
JavaScript
public class Employee
{
    [ChoJSONRecordField]
    public string FirstName { get; set; }
    [ChoJSONRecordField]
    public string LastName { get; set; }
    [ChoJSONRecordField]
    public int Age { get; set; }
    [ChoJSONRecordField(JSONPath = "$.address.streetAddress")]
    public string StreetAddress { get; set; }
    [ChoJSONRecordField(JSONPath = "$.address.city")]
    public string City { get; set; }
    [ChoJSONRecordField(JSONPath = "$.address.state")]
    public string State { get; set; }
    [ChoJSONRecordField(JSONPath = "$.address.postalCode")]
    public string PortalCode { get; set; }
    [ChoJSONRecordField(JSONPath = "$.phoneNumber[?(@.type=='home')].number")]
    public string Phone { get; set; }
    [ChoJSONRecordField(JSONPath = "$.phoneNumber[?(@.type=='fax')].number")]
    public string Fax { 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
JavaScript
private static void UsingPOCO()
{
    using (var csv = new ChoCSVWriter("emp.csv").WithFirstLineHeader())
    {
        using (var json = new ChoJSONReader<Employee>("emp.json"))
        {
            csv.Write(json);
        }
    }
}

Sample fiddle: https://dotnetfiddle.net/0x07tb

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
JavaScript
private static void UsingProjection()
{
    using (var csv = new ChoCSVWriter("emp.csv").WithFirstLineHeader())
    {
        using (var json = new ChoJSONReader("emp.json"))
        {
            csv.Write(json.Select(i => new {
                FirstName = i.firstName,
                LastName = i.lastName,
                Age = i.age,
                StreetAddress = i.address.streetAddress,
                City = i.address.city,
                State = i.address.state,
                PostalCode = i.address.postalCode,
                Phone = i.phoneNumber[0].number,
                Fax = i.phoneNumber[1].number
            }));
        }
    }
}

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

Download the sample attached above, try it.

 

For more advanced conversion of complex nested JSON to CSV, please visit this article

Cinchoo ETL - Converting Complex Nested JSON to CSV

 

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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Cinchoo
United States United States
No Biography provided

Comments and Discussions

 
-- There are no messages in this forum --