4

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?

Cinchoo
  • 6,088
  • 2
  • 19
  • 34
Danilo Alonso
  • 41
  • 1
  • 2
  • 2
    If the data is completely dynamic and you have no reasonable expectation of structure, _how_ can you expect to turn the unknown into a known structure? – maccettura Oct 09 '17 at 20:28
  • 1
    For one, you can try building a dictionary Dictionary where key is name of the property and the value is either well... the value or another dictionary for nested objects. Tricky part is deciding whether the value is string or int. Later, you can probably serialize it with JSON.Net without much effort. – orhtej2 Oct 09 '17 at 20:33

2 Answers2

2

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
Ioannis G.
  • 244
  • 1
  • 8
0

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

Cinchoo
  • 6,088
  • 2
  • 19
  • 34