CSV to Nested JSON C#

Question

I would like to convert a csv file into json with nested objects and nested array. The sample csv looks like below. My csv structure can be dynamic, but I am ok to keep it static to get the required format below. I know there are so many answers to similar questions, but none addressing my specific issue of nested objects.

Id,name,nestedobject/id,nestedarray/0/name
1,name,2,namelist
2,name1,3,namelist1

I want the json like below, specifically having trouble with column 3 & 4 (nested objects)

[{"Id": 1,
"name": "name",
"nestedobject": {
"id": 2
},
"nestedarray": [{
"name": "namelist"
}
]
},
{"Id": 2,
"name": "name1",
"nestedobject": {
"id": 3
},
"nestedarray": [
{"name": "namelist1"}]
}
]

Please note to keep the csv file structure dynamic I pass the datatype in the column name - for e.g.. "System.Int32:Id" will be my first column instead of "Id". How can I modify my code to account for the nested objects?

public static DataTable ReadCsvFile(string fileName)
        {
            DataTable oDataTable = new DataTable();
            StreamReader oStreamReader = new StreamReader(fileName);
            bool hasColumns = false;

            List<string> ColumnNames = new List<string>();

            string[] oStreamDataValues = null;

            while (!oStreamReader.EndOfStream)
            {
                String oStreamRowData = oStreamReader.ReadLine();
                if(oStreamRowData.Length > 0)
                {
                    oStreamDataValues = oStreamRowData.Split(',');
                    if(!hasColumns)
                    {
                        int i = 0;
                        foreach(string csvcolumn in oStreamRowData.Split(','))
                        {
                            string[] nameWithType = csvcolumn.Split(':');
                            DataColumn oDataColumn = new DataColumn(nameWithType[1], typeof(string));
                            ColumnNames.Add(nameWithType[1]);
                            var type = System.Type.GetType(nameWithType[0]);

                            oDataColumn.DataType = type;
                            oDataTable.Columns.Add(oDataColumn);
                            i = i + 1;
                        }
                        hasColumns = true;
                    }
                    else
                    {
                        DataRow oDataRow = oDataTable.NewRow();
                        for(int i = 0; i < ColumnNames.Count; i++)
                        {

                         oDataRow[ColumnNames[i]] = oStreamDataValues[i] == null ? string.Empty : oStreamDataValues[i].ToString();

                        }
                        oDataTable.Rows.Add(oDataRow);
                    }
                }
            }
            oStreamReader.Close();
            oStreamReader.Dispose();


            return oDataTable;
        }

I serialize the DataTable in the end

DataTable dt = ReadCsvFile(filePath);
                dt.CaseSensitive = true;
                var jsonData = JsonConvert.SerializeObject(dt);

https://joshclose.github.io/CsvHelper/ is what you want to use. Don't re-invent the wheel. — Matt, Mar 25 '20 at 19:09
@Matt - My question how do I the store nested objects to get the JSON format mentioned — sonofpaul, Mar 25 '20 at 19:50
You first have to parse that column as just text, then foreach entry, build your object as you want, then serialize to JSON. — Matt, Mar 25 '20 at 19:53

score 2 · Accepted Answer · answered Mar 29 '20 at 18:17

With Cinchoo ETL, an open source library, you can do it as follows

string csv = @"Id,name,nestedobject/id,nestedarray/0/name, nestedarray/0/city, nestedarray/1/name, nestedarray/200/city
1,name,2,namelist10, citylist10,namelist11, citylist11
2,name1,3,namelist20, citylist20,namelist21, citylist21";

StringBuilder json = new StringBuilder();
using (var w = new ChoJSONWriter(json))
{
    using (var r = ChoCSVReader.LoadText(csv).WithFirstLineHeader()
        .Configure(c => c.NestedColumnSeparator = '/')
        )
        w.Write(r);
}
Console.WriteLine(json.ToString());

Output:

[
 {
  "Id": 1,
  "name": "name",
  "nestedobject": {
    "id": 2
  },
  "nestedarray": [
   {
     "name": "namelist10",
     "city": "citylist10"
   },
   {
     "name": "namelist11"
   }
  ]
 },
 {
  "Id": 2,
  "name": "name1",
  "nestedobject": {
    "id": 3
  },
  "nestedarray": [
   {
     "name": "namelist20",
     "city": "citylist20"
   },
   {
     "name": "namelist21"
   }
  ]
 }
]

Can you tell me how the csv should be formatted if I want a list of strings using Cinchoo ETL? For e.g. my JSON will look like {id: 1, name: Tom, friends: [Dick, Harry]}. How should the source csv look like? — sonofpaul, May 10 '20 at 17:29
https://stackoverflow.com/questions/61717430/convert-csv-to-json-containing-liststring-c-sharp — sonofpaul, May 10 '20 at 19:14

CSV to Nested JSON C#

1 Answers1

Linked