Creating a JSON File from a CSV file in C#

o achieve the desired output, you can use C# to read the CSV file and transform the data into JSON format, creating a separate JSON file for each unique "Control_Code." You can use the CsvHelper library to simplify reading the CSV file. Make sure to install the CsvHelper library via NuGet Package Manager if you haven't already.

Here's a step-by-step guide on how to do this:

1. Create a C# project in Visual Studio or your preferred development environment.

2. Add a reference to the CsvHelper library (if not already added) using NuGet Package Manager.

3. Create a class to represent the data structure:

public class CsvRecord
{
    public string Time { get; set; }
    public string Control_Code { get; set; }
    public int Metric { get; set; }
    public string Organisation { get; set; }
    public int Value { get; set; }
    public string DateTime { get; set; }
}

4. Use CsvHelper to read the CSV file and create the JSON files:

using CsvHelper;
using CsvHelper.Configuration;
using Newtonsoft.Json;
using System;
using System.Collections.Generic;
using System.Globalization;
using System.IO;
using System.Linq;

class Program
{
    static void Main(string[] args)
    {
        string csvFilePath = "your_csv_file.csv";
        string outputDirectory = "output_json/";

        using (var reader = new StreamReader(csvFilePath))
        using (var csv = new CsvReader(reader, new CsvConfiguration(CultureInfo.InvariantCulture)))
        {
            var records = csv.GetRecords().ToList();

            var uniqueControlCodes = records.Select(r => r.Control_Code).Distinct();

            foreach (var controlCode in uniqueControlCodes)
            {
                var dataForControlCode = records.Where(r => r.Control_Code == controlCode).ToList();

                var jsonData = new
                {
                    Time = dataForControlCode.First().Time,
                    Control_Code = controlCode,
                    Metrics = dataForControlCode.Select(r => new
                    {
                        Metric = r.Metric,
                        Organisation = r.Organisation,
                        Value = r.Value,
                        Datetime = r.DateTime
                    }).ToList()
                };

                string json = JsonConvert.SerializeObject(jsonData, Formatting.Indented);
                File.WriteAllText(Path.Combine(outputDirectory, $"{controlCode}.json"), json);
            }
        }

        Console.WriteLine("JSON files have been created.");
    }
}

Make sure to replace "your_csv_file.csv" with the actual path to your CSV file. This code will create separate JSON files for each unique "Control_Code" in the "output_json" directory.

Ensure that you have the CsvHelper and Newtonsoft.Json libraries referenced in your project. You can install them using NuGet Package Manager if they're not already added.

After running this code, you should have JSON files with the desired format, each containing a single "Control_Code" and its related metric values.