How to Map ChoETL Json to CSV Complex Types

Question

I have the following Class structure

public class ResultData
{
    [JsonProperty(PropertyName = "Id")]
    public string Id { get; set; }

    [JsonProperty(PropertyName = "Results")]
    public IEnumerable<Result> Results{ get; set; }
}

public class Result
{
    public string Name { get; set; }

    public string Value { get; set; }
}

Here is my code to convert to csv

private static void ToCsv(ResultData data)
{
    StringBuilder csv = new StringBuilder();
    var json = JsonConvert.SerializeObject(data);

    var config = new ChoJSONRecordConfiguration {AllowComplexJSONPath = true};

    config.JSONRecordFieldConfigurations.Add(new ChoJSONRecordFieldConfiguration("Id"));
    config.JSONRecordFieldConfigurations.Add(new ChoJSONRecordFieldConfiguration("Results", "Name") { FieldType = typeof(string[]) });
    config.JSONRecordFieldConfigurations.Add(new ChoJSONRecordFieldConfiguration("Results", "Value") { FieldType = typeof(string[]) });

    using (var r = ChoJSONReader.LoadText(json, config))
    {
        using var w = new ChoCSVWriter(csv).WithFirstLineHeader().UseNestedKeyFormat(false);
        w.Write(r);
    }

    File.WriteAllText("2.csv", csv.ToString());
}

Json

{"Id":"839c0a09-f2d0-4f29-9cce-bc022d3511b5","Results":[{"Name":"ABC","Value":"5"},{"Name":"CDE","Value":"2"}]}

What I would like is a CSV file With the Id and the results name in one column and the results value in another column

What is ur expected csv output? Also post sample json as well. — Cinchoo, Dec 07 '20 at 21:43
hi @Cinchoo i had added the json and added an explination of what i need "What I would like is a CSV file With the Id and the results name in one column and the results value in another column" — MicroMan, Dec 08 '20 at 17:48

Cinchoo · Accepted Answer · 2020-12-08T20:49:21.067

Here is how you can produce the CSV output from JSON

string json = @"{
  ""Id"": ""839c0a09-f2d0-4f29-9cce-bc022d3511b5"",
  ""Results"": [
    {
      ""Name"": ""ABC"",
      ""Value"": ""5""
    },
    {
      ""Name"": ""CDE"",
      ""Value"": ""2""
    }
  ]
}";

StringBuilder csv = new StringBuilder();
using (var r = ChoJSONReader<ResultData>.LoadText(json)
    .UseJsonSerialization()
    )
{
    using (var w = new ChoCSVWriter(csv)
        .WithFirstLineHeader()
        .Configure(c => c.IgnoreDictionaryFieldPrefix = true)
        )
    {
        w.Write(r.Select(r1 => new
        {
            r1.Id,
            Results = r1.Results.ToDictionary(kvp => kvp.Name, kvp => kvp.Value)
        }));
    }
}

Console.WriteLine(csv.ToString());

Output:

Id,ABC,CDE
839c0a09-f2d0-4f29-9cce-bc022d3511b5,5,2

UPDATE:

private static void ToCsv(ResultData data)
{
    using (var w = new ChoCSVWriter("2.csv")
        .WithFirstLineHeader()
        .Configure(c => c.IgnoreDictionaryFieldPrefix = true)
        )
        w.Write(data.ConvertToEnumerable().Select(r1 => new
        {
            r1.Id,
            Results = r1.Results.ToDictionary(kvp => kvp.Name, kvp => kvp.Value)
        }));
}

thanks, when i add this line var json = JsonConvert.SerializeObject(data); the string output is blank? — MicroMan, Dec 08 '20 at 19:40
just wondering why JSON is needed here? look like you want to produce CSV from `ResultData` object. In that case, you just need `ChoCSVWriter` to produce the output. No intermediary steps required. — Cinchoo, Dec 08 '20 at 20:38

How to Map ChoETL Json to CSV Complex Types

1 Answers1