JSON to CSV conversion using CHOETL displaying values in one row and not columns

Question

I am converting a JSON file to a CSV file. The JSON has multiple nested objects. While converting, I am able to get all the values out of the JSON and into the CSV. However, all the values are being shown as one row with the same heading repeated multiple times. I am using CHOETL library.

using (var csv = new ChoCSVWriter("file1.csv").WithFirstLineHeader().WithDelimiter(","))
{
    using (var json = new ChoJSONReader("file2.json")
        .WithField("RecordID", jsonPath: "$..Events[*].RecordId")
        .WithField("RecordType", jsonPath: "$..Events[*].RecordType")
        .WithField("EventDate", jsonPath: "$..Events[*].EventDate")
    {
        csv.Write(json);
    }
}

It shows the results as

Record ID_0 Record ID_1 Record ID_2
123 456 789

Instead of as

Record ID
123
456
789

Here is the JSON File

[
    {
        "Id": "3e399241",
        "IdLineage": [
            "sfdsfdsfs",
            "sdfdsfdsf"

        ],
        "Individuals": [
            {
                "Id": "1232112",
                "IdLineage": [
                    "fdsfsd1"
                ],
                "Events": [
                    {

                        "RecordId": "2132121321",
                        "RecordType": "SALE",
                        "EventDate": "2016-01-04T05:00:00Z"
                    },
                    {

                        "RecordId": "123213212",
                        "RecordType": "SALE",
                        "EventDate": "2012-07-16T04:00:00Z"
                    }


                ]
            },
            {
                "Id": "ssf2112",
                "IdLineage": [],
                "Events": [

                    {

                        "RecordId": "123213ds21",
                        "RecordType": "ACXIOMRECORD",
                        "EventDate": "2017-12-17T03:33:54.875Z"
                    }
                ]
            },
            {
                "Id": "asadsad",
                "IdLineage": [],
                "Events": [
                    {

                        "RecordId": "213213sa21",
                        "RecordType": "SALE",
                        "EventDate": "2018-03-09T05:00:00Z"
                    }
                ]
            }
        ]
    }
]

please post sample json file. – Cinchoo Jul 23 '19 at 13:11 — Cinchoo, Jul 23 '19 at 13:11

Cinchoo · Accepted Answer · 2019-07-23T21:21:05.903

Based on sample code you posted, you are creating object from JSON as below

{
   RecordID : Array,
   RecordType: Array,
   EventDate: Array
}

This leads to generate CSV in the below format, this is as expected.

RecordID_0, RecordID_1, RecordID_2, RecordType_0, RecordType_1, ....

If you want to create CSV in the below format, you will have to fix the json path on each record field

RecordID, RecordType, EventData

sample code

using (var csv = new ChoCSVWriter("file1.csv").WithFirstLineHeader().WithDelimiter(","))
{
    using (var json = new ChoJSONReader("file2.json")
    .WithField("RecordID", jsonPath: "$..Events.RecordId")
    .WithField("RecordType", jsonPath: "$..Events.RecordType")
    .WithField("EventDate", jsonPath: "$..Events.EventDate")

    {
        csv.Write(json);
    }
}

UPDATE #1: After looking at the sample JSON, this is how you can pull the data and produce CSV file in expected format

StringBuilder msg = new StringBuilder();

using (var w = new ChoCSVWriter(msg)
    .WithFirstLineHeader()
    )
{
    using (var r = new ChoJSONReader("Sample32.json")
        .WithJSONPath("$..Events[*]")
        )
    {
        w.Write(r);
    }
}
Console.WriteLine(msg.ToString());

OUTPUT #1:

RecordId,RecordType,EventDate
2132121321,SALE,1/4/2016 5:00:00 AM
123213212,SALE,7/16/2012 4:00:00 AM
123213ds21,ACXIOMRECORD,12/17/2017 3:33:54 AM
213213sa21,SALE,3/9/2018 5:00:00 AM

UPDATE #2:

You must use Linq to combine id's with event members. Sample below shows how to

using (var fw = new StreamWriter("Sample32.csv", true))
{
    using (var w = new ChoCSVWriter(fw)
        .WithFirstLineHeader()
        )
    {
        using (var r = new ChoJSONReader("Sample32.json")
            .WithJSONPath("$..Individuals[*]")
            )
        {
            w.Write(r.SelectMany(r1 => ((dynamic[])r1.Events).Select(r2 => new { r1.Id, r2.RecordId, r2.RecordType, r2.EventDate })));
        }
    }
}
Console.WriteLine(File.ReadAllText("Sample32.csv"));

OUTPUT #2:

Id,RecordId,RecordType,EventDate
1232112,2132121321,SALE,1/4/2016 5:00:00 AM
1232112,123213212,SALE,7/16/2012 4:00:00 AM
ssf2112,123213ds21,ACXIOMRECORD,12/17/2017 3:33:54 AM
asadsad,213213sa21,SALE,3/9/2018 5:00:00 AM

I tried this exact code but the CSV is completely empty. It does however, show the headers in the proper format but there is no data in the file. — EnglishBanana, Jul 23 '19 at 14:42
Where does this get written to? Also isn't StringBuilder msg empty/null? — EnglishBanana, Jul 23 '19 at 15:28
You can replace stringbuilder with file, if you want. I did for my test purpose. — Cinchoo, Jul 23 '19 at 15:49
Oh wow it totally worked. I'm really grateful. Thanks so much RajN. — EnglishBanana, Jul 23 '19 at 16:00
Is there a way to append instead of over writing? I'm trying to also include the Id. Is there a way to append? — EnglishBanana, Jul 23 '19 at 16:37
If you want append , open file with append mode, pass the stream object to CSVWriter constructor. — Cinchoo, Jul 23 '19 at 20:49
See my update #2 above, how to append id along with event members. Hope it helps. — Cinchoo, Jul 23 '19 at 21:21
Thank you so much! It worked 100%. Really appreciate your help mate. — EnglishBanana, Jul 25 '19 at 18:18

JSON to CSV conversion using CHOETL displaying values in one row and not columns

1 Answers1

Linked