csv to json file format

Question

I want to convert my csv file into .json format using c#. here what i have tried:

var lines = @"text,intentName,entityLabels
        1,2,null
        2,1,null".Replace("\r", "").Split('\n');

    var csv = lines.Select(l => l.Split(',')).ToList();

    var headers = csv[0];
    var dicts = csv.Skip(1).Select(row => Enumerable.Zip(headers, row, 
Tuple.Create).ToDictionary(p => p.Item1, p => p.Item2)).ToArray();

    string json = new System.Web.Script.Serialization.JavaScriptSerializer().Serialize(dicts);
    Result1.Text = json;

The result is :

[
 {
"text":" 1",
"intentName":"2",
"entityLabels":"null"
},
 {
"text":"2",
"intentName":"1",
"entityLabels":"null"
}
] 

it almost like I expected, however I want to make if the entityLabels column is null, then it replace into []. so the output that I expecting is:

[
 {
"text":" 1",
"intentName":"2",
"entityLabels":[]
},
 {
"text":"2",
"intentName":"1",
"entityLabels":[]
}
] 

anyone know how to do it?

Answer 1

With external lib Cinchoo ETL - an open source library, you can convert CSV --> JSON with the expected format as below

Method 1:

string csv = @"text,intentName,entityLabels
1,2,null
2,1,null
";

StringBuilder sb = new StringBuilder();
using (var p = ChoCSVReader.LoadText(csv)
    .WithFirstLineHeader()
    .WithField("text")
    .WithField("intentName")
    .WithField("entityLabels", fieldType: typeof(int[]), nullValue: "null")
    )
{
    using (var w = new ChoJSONWriter(sb)
        )
        w.Write(p);
}

Console.WriteLine(sb.ToString());

Sample fiddle: https://dotnetfiddle.net/5M7fFX

Method 2:

string csv = @"text,intentName,entityLabels
1,2,null
2,1,null
";

StringBuilder sb = new StringBuilder();
using (var p = ChoCSVReader.LoadText(csv)
    .WithFirstLineHeader()
    .WithField("text")
    .WithField("intentName")
    .WithField("entityLabels", valueConverter: (o) => new int[] { })
    )
{
    using (var w = new ChoJSONWriter(sb)
        )
        w.Write(p);
}

Console.WriteLine(sb.ToString());

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

Output:

[
 {
  "text": "1",
  "intentName": "2",
  "entityLabels": []
 },
 {
  "text": "2",
  "intentName": "1",
  "entityLabels": []
 }
]

Hope it helps.

Answer 2

Don't try to use string operations to convert from one data type to another.

Instead use an actual CSV parsing library like csvhelper (available on NuGet) to deserialise the CSV into objects, and then re-serialise that same data as JSON using a JSON serializer.