Convert CSV (nested objects) to JSON

Question

I need to convert a CSV data (with one line of header, and one line of data) in a JSON object. The CSV contains nested columns, there is an example:

id,name,category/id,category/name,category/subcategory/id,category/subcategory/name,description
0,Test123,15,Cat123,10,SubCat123,Desc123

And I want a JSON to look like this:

{
    "id": 0,
    "name": "Test123",
    "category": {
        "id": 15,
        "name": "Cat123",
        "subcategory": {
            "id": 10,
            "name": "SubCat123",
        }
    },
    "description": "Desc123"
}

I've tried CsvHelper and ChoETL libs, without success, because these libs, as far as I know, require that I have a class as model, but I don't have these classes, because the data is totally dynamic.

The site http://www.convertcsv.com/csv-to-json.htm is a good example to make this with success. Just paste the JSON that I have created above, go to Step 3 and check the option 'Recreate nested objects and arrays', then click 'CSV to JSON' in step 5.

But I need to make this in my application, without using external frameworks.

How can I make this to work?

Answer 1

If you do not have it, add the newtonsoft library(dll) and then add the folowing references

using Newtonsoft.Json;
using Newtonsoft.Json.Linq;

Add the following classes

   public class Rootobject
        {
            public int id { get; set; }
            public string name { get; set; }
            public Category category { get; set; }
            public string description { get; set; }
        }

        public class Category
        {
            public int id { get; set; }
            public string name { get; set; }
            public Subcategory subcategory { get; set; }
        }

        public class Subcategory
        {
            public int id { get; set; }
            public string name { get; set; }
        }

then use this code

 DataTable CSVData = new DataTable(); // your csv rows



            HashSet<Rootobject> MyObjectsList = new HashSet<Rootobject>(); //create hashset to handle your classes
            foreach(DataRow row in CSVData.Rows)
            {
                //change the indices in ItemArray with the right indices
                MyObjectsList.Add(new Rootobject() {
                    id = (int)row.ItemArray[0], name = (string)row.ItemArray[0], category = new Category() {
                        id = (int)row.ItemArray[0], name = (string)row.ItemArray[0], subcategory = new Subcategory() {
                            id = (int)row.ItemArray[0], name = (string)row.ItemArray[0]
                        }
                    }
                });


            }





            string _ResultObj = JsonConvert.SerializeObject(MyObjectsList);  //here get your json string

Answer 2

With the latest ChoETL.JSON 1.0.1.6, you can convert them easily. This will work only if the headers are simple texts, no spaces or special characters allowed.

using (var json = new ChoJSONWriter("nested.json")
           .SingleElement()
           .Configure(c => c.SupportMultipleContent = true)
           .Configure(c => c.DefaultArrayHandling = false)
          )
{
    using (var csv = new ChoCSVReader("nested.csv").WithFirstLineHeader())
        json.Write(csv.Select(i => i.ConvertToNestedObject('/')));
}

Output JSON will look at below

[
 {
  "id":0,
  "name":"Test123",
  "category": {
    "id": 15,
    "name": "Cat123",
    "subcategory": {
      "id": 10,
      "name": "SubCat123"
    }
  },
  "description":"Desc123"
 }
]

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

UPDATE:

Cinchoo ETL now supports native nested object support by just setting 'NestedColumnSeparator' configuration parameter to '/'. Sample below shows how to

using (var json = new ChoJSONWriter("nested.json")
           .SingleElement()
           .Configure(c => c.SupportMultipleContent = true)
           .Configure(c => c.DefaultArrayHandling = false)
          )
{
    using (var csv = new ChoCSVReader("nested.csv")
          .WithFirstLineHeader()
          .Configure(c => c.NestedColumnSeparator = '/')
        )
        json.Write(csv);
}

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