Creating a JSON File from a CSV file?

[Beanymattd]

Apologies but this is going to be a "How to" question rather than a technical question. I am very new to C# and using Json. I have a CSV file as follows:

Edit - I did manage to find some code here - Convert CSV to JSON in C# - QA With Experts - not sure if this is suitable for my goal as it seems to be a simple convert.

    Time             Control_Code    Metric    Organisation    Value    DateTime
 

    2018-10-21T00:08:03 JKX           3721      AD450          20   2018-10-21T00:08:00
    2018-10-21T00:08:03 BHY           1234      HG650          88   2018-10-21T00:08:00

I need to produce multiple JSON output files from that csv in the following format example:

    {
        "Time":"2018-10-21T00:08:03",
        "Control_Code": "JKX",
        "metrics": [
            {
                "Metric": 3721,
                "Organisation":"AD450",
                "Value": 20,
                "Datetime":"2018-10-21T00:08:00"
            },
            {
                "Metric": 1234,
                "Organisation":"HG650",
                "value": 88,
                "datetime":"2018-10-21T00:08:00"
            }
            
        ]
    }

Now the extra problematic part on top of this is that there is a requirement where only one Control_Code may be used per Json.
Each Json generated must contain a single Control_Code and all related metric values in the metrics array. So the csv will need to be scanned for each different Control_Code and then produce an output for that specific Control_Code and then do the same for any subsequent Control_Codes.

So for example a different Json would be produced with a different Control_Code (from the same csv file) - Example (notice different Control_Code other values will of course change as well, but just providing an example).

    {
        "Time":"2018-10-21T00:08:03",
        "Control_Code": "BHY",
        "metrics": [
            {
                "Metric": 3721,
                "Organisation":"AD450",
                "Value": 20,
                "Datetime":"2018-10-21T00:08:00"
            },
            {
                "Metric": 1234,
                "Organisation":"HG650",
                "value": 88,
                "datetime":"2018-10-21T00:08:00"
            }
            
        ]
    }

Thanks for any advice/information in advance.

 

[Skydiver]

Seems like an you need to do is load the entire CSV as a list of objects, then group the objects by the control code. For each control code, create a JSON file with the objects in the group.

In that code that you found, the bypassed the step of partying each line into an object, and just parsed each line into an array of strings.

 

[cjard]

We install CSVHelper from nuget


We have a class to hold our data, including Attributes for what name of column in the CSV the proeprties should be mapped to:

public class X{

    [Name("Control_Code")]

    public string? ControlCode {get;set;}


    [Name("DateTime")]

    public DateTime RoundedTime {get;set;}


    public int Metric {get;set;}


    public string? Organisation {get;set;}


    [Name("Time")]

    public DateTime ActualTime {get;set;}


    public int Value {get;set;}


}

We read the CSV into a collection of the class:

Example
void Main()
{
    using (var reader = new StreamReader("path\\to\\file.csv"))
    using (var csv = new CsvReader(reader, CultureInfo.InvariantCulture))
    {
        var records = csv.GetRecords<X>().ToArray();
    }
}

Now we can transform to output the JSON. You seem to be saying you want the same metrics over and over, for every different ControlCode/ActualTime pairing. Let's make a reusable metric object:

var metrics  = records.Select(r => new { r.Metric, r.Organisation, r.Value, Datetime = r.RoundedTime }).ToArray(); //i assume your use of "Datetime" and "datetime" in the example was a typo

Now let's make a collection of distinct controlcode/actualtime pairings to cross this with:

var ccats = records.Select(r => new { r.ControlCode, r.ActualTime }).Distinct().ToArray();

Now we can just cross multiply them and write them out. We add the nuget package Newtonsoft.Json then

Directory.CreateDirectory(@"c:\temp\codes");
foreach(var ccat in ccats){
  var path = Path.Combine(@"c:\temp\codes", ccat.ControlCode ?? "NONE");
  var obj = new {
        Time = ccat.ActualTime,
        Control_Code = ccat.ControlCode,
        metrics
  }
  File.WriteAllText(path, JsonConvert.SerializeObject(obj));
}

 

[Beanymattd]

.

Thank you very much for your assistance, this is greatly appreciated and helped me immensely.