Creating a JSON File from a CSV file in C#

Member_16113477

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 - https://qawithexperts.com/article/c-sharp/convert-csv-to-json-in-c/465 - 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.

OriginalGriff

Concentrate on reading the CSV first - I use A Fast CSV Reader[^] - it does all the donkey work for you and can load it into a DataTable very easily. When you have that, create the appropriate C# classes to hold your expected data - running your JSON data sample through Convert JSON to C# Classes Online - Json2CSharp Toolkit[^] gave these so that'll be a good place to start:

public class Metric
{
    public int Metric { get; set; }
    public string Organisation { get; set; }
    public int Value { get; set; }
    public DateTime Datetime { get; set; }
    public int? value { get; set; }
    public DateTime? datetime { get; set; }
}

public class Root
{
    public DateTime Time { get; set; }
    public string Control\_Code { get; set; }
    public List metrics { get; set; }
}

Then process the table data to fill out your classes. When you have them, use Json.NET - Newtonsoft[^] to produce the JSON string - you can do what you like with it from there.

"I have no idea what I did, but I'm taking full credit for it." - ThisOldTony "Common sense is so rare these days, it should be classified as a super power" - Random T-shirt AntiTwitter: @DalekDave is now a follower!

jschell

Steps 1. Determine how to parse the CSV correctly 1.a. Load it into a flat (list) data structure. 2. Sort it 2.a Determine the appropriate collection where the control code is the primary key. 2.b Iterate through 1.a. If the control code already exists, add record. If it does not exist, create it, then add it. 3. Determine how to create appropriate json. (NOT from the above, but rather just putting it out with fake data.) 3.a Learn how to write a file 3.b Learn how to write a json format. 4. Iterate through 2.a, feed into 3, to product file.

Lost User

Your design is flawed; it doesn't reflect the data; and results in a redundant data structure. The "control code" is at the metric level; where "Time" is the key of the "root". From there you can create (other) "object graphs" (more easily).

"Before entering on an understanding, I have meditated for a long time, and have foreseen what might happen. It is not genius which reveals to me suddenly, secretly, what I have to say or to do in a circumstance unexpected by other people; it is reflection, it is meditation." - Napoleon I

OriginalGriff

That's the problem with CSV: it's flat data store with no hierarchic structure - which always leads to redundant duplication as the only way to build a "proper" structure when the flat data is processed.

"I have no idea what I did, but I'm taking full credit for it." - ThisOldTony "Common sense is so rare these days, it should be classified as a super power" - Random T-shirt AntiTwitter: @DalekDave is now a follower!

Lost User

I'll accept whatever CSV has to offer under the circumstances (e.g. Excel -> csv), but that doesn't mean propagating the "bad design" in the ETL phase ... one can always go back to the bad design after that. :-\

"Before entering on an understanding, I have meditated for a long time, and have foreseen what might happen. It is not genius which reveals to me suddenly, secretly, what I have to say or to do in a circumstance unexpected by other people; it is reflection, it is meditation." - Napoleon I

OriginalGriff

:-D

"I have no idea what I did, but I'm taking full credit for it." - ThisOldTony "Common sense is so rare these days, it should be classified as a super power" - Random T-shirt AntiTwitter: @DalekDave is now a follower!

jschell

Gerry Schmitz wrote:

Your design is flawed;

Good catch. That would need to be resolved before anything can proceed.