JSON to CSV and CSV to JSON in C#

Question

I have been searching and searching for a way to convert a json file to a csv and the vice versa using C#. I have searched google and have not come up with anything. Everything I've tried so far from the answers on stack overflow just do not work from me. Does anyone know of any tooling or tutorials I could have look at how to accomplish this with the .NET Framework? Usually I post what I've tried however I'm clearly far off here so it is pointless.

Answer 1

Besides using combination of multiple libraries to do the conversion of JSON to CSV and vice versa, Cinchoo ETL gives you unified interface to do the conversion between those 2 formats.

For a sample JSON file:

[
   {
      "Name" : "Xytrex Co.",
      "Description" : "Industrial Cleaning Supply Company",
      "AccountNumber" : "ABC15797531"
   },
   {
      "Name" : "Watson and Powell, Inc.",
      "Description" : "Law firm. New York Headquarters",
      "AccountNumber" : "ABC24689753"     
   }
]

To produce CSV file:

Name,Description,AccountNumber
Xytrex Co.,Industrial Cleaning Supply Company,ABC15797531
Watson and Powell Inc.,Law firm. New York Headquarters,ABC24689753

JSON to CSV:

using (var p = ChoJSONReader.LoadText(json))
{
    using (var w = new ChoCSVWriter(Console.Out)
        .WithFirstLineHeader()
        )
    {
        w.Write(p);
    }
}

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

CSV to JSON:

using (var p = ChoCSVReader.LoadText(csv)
        .WithFirstLineHeader()
    )
{
    using (var w = new ChoJSONWriter(Console.Out))
    {
        w.Write(p);
    }
}

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

Answer 2

like Jaxidian mentioned, the problem is, that json can have a hierarchy, csv not.

So, there are two solutions I could suggest you:

• create a hierarchical csv, shouldn't be much effort:

  "Id";"Name";"Age";"Type"
      "FriendId"
  1;"Mickey Mouse";20;"mouse"
      2
      3
      4
  2;"Pluto";7;"dog"
      1
  3;"Minnie";20;"mouse"
  4;"Donald";22;"duck"
  

• create multiple files, could be more effort, but is more beautiful and more dynamic, when you eg. export from/import into database. Maybe this link could help you: http://www.snellman.net/blog/archive/2016-01-12-json-to-multicsv

  • all.csv (store all characters)

    "Id";"Name";"Age";"Type"
    1;"Mickey Mouse";20;"mouse"
    2;"Pluto";7;"dog"
    3;"Minnie";20;"mouse"
    4;"Donald";22;"duck"
    

  • friends.csv (store all relations)

    "FriendKey1";"FriendKey2"
    1;2
    2;1
    1;3
    1;4
    

Answer 3

Compromises and Problems

You can accomplish this with the .NET Framework but there's not a clear and obvious way to just do this straight-up because of hierarchies and collections. What I mean by that is that CSV data is very flat and unstructured whereas JSON data is very organized and iterative. Let's take a simple chunk of JSON data that could look like this:

{
    "Data": [
        {
            "Name":"Mickey Mouse",
            "Friends":[ "Pluto", "Minnie", "Donald" ]
        },
        {
            "Name":"Pluto",
            "Friends":[ "Mickey" ]
        }
    ]
}

The most obvious CSV file for that could be:

Name,Friend
Mickey Mouse,Pluto
Mickey Mouse,Minnie
Mickey Mouse,Donald
Pluto,Mickey

That's the easier conversion but let's say you just have that CSV file. It's not so obvious what the JSON should look like. One could argue that the JSON should look like this:

{
    "Data": [
        { "Name":"Mickey Mouse", "Friend":"Pluto" },
        { "Name":"Mickey Mouse", "Friend":"Minnie" },
        { "Name":"Mickey Mouse", "Friend":"Donald" },
        { "Name":"Pluto", "Friend":"Mickey" },
    ]
}

That resulting JSON file is very different than the input JSON file. My point is that this isn't a simple/obvious conversion so any off-the-shelf or copy/paste solution will be imperfect. Whatever your solution is, you're going to have to make compromises or intelligent decisions.

---

.NET Framework Options

Now that we've gotten that out of the way, .NET gives you some capabilities to accomplish this out of the box and there are some good Nuget-supplied options as well. If you want to utilize pure .NET capabilities, you could use a combination of these two SO Answers:

1. Not perfect but this answer has some great code to get you started in the logic to generate a CSV file
2. This question and the resulting answers have some good info about generating JSON using just the .NET Framework and without any third-party utilities.

You should be able to apply the concepts in those two links PLUS the compromises and intelligent decisions you need to make from my first "Compromises and Problems" section of this post to accomplish what you need.

---

Something I've Done Before

I've done something similar where I actually used some functionality in the Microsoft.VisualBasic.FileIO namespace (works great in a C# app) in addition to Web API's serialization functionality to accomplish a CSV->JSON conversion using Dynamic objects (using the dynamic keyword) as an intermediary. The code is provided below. It's not terribly robust and makes some significant compromises but it has worked well for me. If you want to try this, you'll have to create your own version that goes in reverse, but as I mentioned in my first section, that's really the easy part.

using System.Collections.Generic;
using System.Dynamic;
using System.IO;
using System.Linq;
using System.Web.Http;

// NOTE: This is not purely my code. This was put together
// with the help of other SO questions that I wish I had the
// links to so I could credit them. You probably will find
// some chunk(s) of this code elsewhere on SO.

namespace Application1.Controllers
{
    public class Foo
    {
        public string Csv { get; set; }
    }
    public class JsonController : ApiController
    {
        [HttpPost]
        [Route("~/Csv/ToJson")]
        public dynamic[] ConvertCsv([FromBody] Foo input)
        {
            var data = CsvToDynamicData(input.Csv);
            return data.ToArray();
        }

        internal static List<dynamic> CsvToDynamicData(string csv)
        {
            var headers = new List<string>();
            var dataRows = new List<dynamic>();
            using (TextReader reader = new StringReader(csv))
            {
                using (var parser = new Microsoft.VisualBasic.FileIO.TextFieldParser(reader))
                {
                    parser.Delimiters = new[] {","};
                    parser.HasFieldsEnclosedInQuotes = true;
                    parser.TrimWhiteSpace = true;

                    var rowIdx = 0;

                    while (!parser.EndOfData)
                    {
                        var colIdx = 0;
                        dynamic rowData = new ExpandoObject();
                        var rowDataAsDictionary = (IDictionary<string, object>) rowData;

                        foreach (var field in parser.ReadFields().AsEnumerable())
                        {
                            if (rowIdx == 0)
                            {
                                // header
                                headers.Add(field.Replace("\\", "_").Replace("/", "_").Replace(",", "_"));
                            }
                            else
                            {
                                if (field == "null" || field == "NULL")
                                {
                                    rowDataAsDictionary.Add(headers[colIdx], null);
                                }
                                else
                                {
                                    rowDataAsDictionary.Add(headers[colIdx], field);
                                }

                            }
                            colIdx++;
                        }

                        if (rowDataAsDictionary.Keys.Any())
                        {
                            dataRows.Add(rowData);
                        }

                        rowIdx++;
                    }
                }
            }

            return dataRows;
        }
    }
}

If you want something more robust, then you can always leverage these great projects:

1. JSON.NET (This works VERY WELL with creating JSON from dynamic objects. Given that you're not using Web API, this would be the first place I would look to take the dynamic[] return value and convert it to JSON.)
2. CsvHelper