Cinchoo ETL - Quick Start: Converting CSV to JSON File

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 converting CSV file to JSON format file 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 CSV file. This sample CSV contains all possible combination of values with different format.

Listing 3.1.1 Sample CSV File (emp.json)

FirstName,LastName,Street,City,State,Zip 
John,Doe,120 jefferson st.,Riverside, NJ, 08075 
Jack,McGinnis,220 hobo Av.,Phila, PA,09119 
"John ""Da Man""",Repici,120 Jefferson St.,Riverside, NJ,08075 
Stephen,Tyler,"7452 Terrace ""At the Plaza"" road",SomeTown,SD, 91234 
,Blankman,,SomeTown, SD, 00298 
"Joan ""the bone"", Anne",Jet,"9th, at Terrace plc",Desert City,CO,00123

Expected JSON file would be (emp.json)

[
  {
    "FirstName": "John",
    "LastName": "Doe",
    "Street": "120 jefferson st.",
    "City": "Riverside",
    "State": "NJ",
    "Zip": "08075"
  },
  {
    "FirstName": "Jack",
    "LastName": "McGinnis",
    "Street": "220 hobo Av.",
    "City": "Phila",
    "State": "PA",
    "Zip": "09119"
  },
  {
    "FirstName": "John \"Da Man\"",
    "LastName": "Repici",
    "Street": "120 Jefferson St.",
    "City": "Riverside",
    "State": "NJ",
    "Zip": "08075"
  },
  {
    "FirstName": "Stephen",
    "LastName": "Tyler",
    "Street": "7452 Terrace \"At the Plaza\" road",
    "City": "SomeTown",
    "State": "SD",
    "Zip": "91234"
  },
  {
    "FirstName": null,
    "LastName": "Blankman",
    "Street": null,
    "City": "SomeTown",
    "State": "SD",
    "Zip": "00298"
  },
  {
    "FirstName": "Joan \"the bone\", Anne",
    "LastName": "Jet",
    "Street": "9th, at Terrace plc",
    "City": "Desert City",
    "State": "CO",
    "Zip": "00123"
  }
]

3.2 Install Library

Next, install ChoETL.JSON / ChoETL.JSON.NETStandard nuget package. To do this, run the following command in the Package Manager Console.

.NET Standard Framework

Install-Package ChoETL.JSON

.NET Core

Install-Package ChoETL.JSON.NETStandard

Now add ChoETL namespace to the program.

using ChoETL;

3.3 Quick Conversion

Lets use the library to convert the CSV file to JSON formatted file. It is as simple as can be done with few lines of code. No POCO class needed. It is fast, stream based, and consume low memory.

Listing 3.3.1. Quick CSV to JSON file conversion

private static void QuickConversion()
{
    using (var r = new ChoCSVReader("emp.csv")
           .WithFirstLineHeader()
           .MayHaveQuotedFields()
           )
    {
        using (var w = new ChoJSONWriter(Console.Out)
               .UseJsonSerialization()
              )
            w.Write(r);
    }
}

Create an instance of ChoJSONWriter for producing JSON (emp.json) file. Then create an instance of ChoCSVReader object for reading emp.csv file. As CSV comes with some fields with quotes, add MayHaveQuotedFields(). Finally call 'Write' method on JSONWriter to write the objects to the output file.

Sample Fiddle: https://dotnetfiddle.net/zZWBQK

3.4 Selective Columns Conversion

In some cases, you may want to take control of outputting selective columns in JSON output file. And you want to specify and treat the fields in the native format, so that the JSON output can be produced in cleaner way. 

In the demo below, we going to choose the 'firstName', 'lastName', and 'city' columns.

Listing 3.4.1. Quick CSV to JSON file conversion

private static void SelectiveColumnTest()
{
    using (var r = new ChoCSVReader("emp.csv"
           .WithFirstLineHeader()
           .MayHaveQuotedFields()
           .WithField("FirstName")
           .WithField("LastName")
           .WithField("City")
          )
    {
        using (var w = new ChoJSONWriter("emp.json")
               .UseJsonSerialization()
              )
            w.Write(r);
    }
}

In above, all the data elements are treated and converted as text. If you want to add type to some column and output them in native format, you can do so by using 'WithField' method.

Sample fiddle: https://dotnetfiddle.net/7xNC8f

Listing 3.4.2. The JSON output file

[
  {
    "FirstName": "John",
    "LastName": "Doe",
    "City": "Riverside"
  },
  {
    "FirstName": "Jack",
    "LastName": "McGinnis",
    "City": "Phila"
  },
  {
    "FirstName": "John \"Da Man\"",
    "LastName": "Repici",
    "City": "Riverside"
  },
  {
    "FirstName": "Stephen",
    "LastName": "Tyler",
    "City": "SomeTown"
  },
  {
    "FirstName": null,
    "LastName": "Blankman",
    "City": "SomeTown"
  },
  {
    "FirstName": "Joan \"the bone\", Anne",
    "LastName": "Jet",
    "City": "Desert City"
  }
]

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

public class Employee
{
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public string Street { get; set; }
    public string City { get; set; }
    public string Zip{ get; set; }
}

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

private static void UsingPOCO()
{
    using (var r = new ChoCSVReader<Employee>("emp.csv")
           .WithFirstLineHeader()
           .MayHaveQuotedFields()
          )
    {
        using (var w = new ChoJSONWriter<Employee>("emp.json")
               .UseJsonSerialization()
              )
            w.Write(r);
    }
}

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

Download the sample attached above, try it.

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

• Cinchoo ETL - Articles