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?

An empty array is still an array, not a `null` value. If you want to generate an empty array for missing `entityLabels`, you'll have to replace nulls with arrays — Panagiotis Kanavos, Aug 08 '18 at 11:12
BTW `JavaScriptSerializer` is an obsolete class that shouldn't be used. It was created almost a decade ago and uses conventions that are no longer valid, eg for dates. The most popular library is JSON.NET. Even ASP.NET Web API and ASP.NET Core projects use it out-of-the-box — Panagiotis Kanavos, Aug 08 '18 at 11:14
This works for current example, but how it will work with given entityLabels data. How it will look like in csv? — Renatas M., Aug 08 '18 at 11:25

Cinchoo · Accepted Answer · 2021-11-15T17:32:00.687

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.

can you explain about ChoJSONWriter? where does it comes from? i use the ChoETL, but ChoJSONWriter could not be found anywhere. is there other code i need to create? — Raspi Surya, Aug 10 '18 at 15:08

score 0 · Answer 2 · answered Aug 08 '18 at 12:17

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.

csv to json file format

2 Answers2