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
– Cinchoo

2019-07-23 13:11:34 +00:00
Commented Jul 23, 2019 at 13:11 — Cinchoo
– Cinchoo, Commented Jul 23, 2019 at 13:11

Cinchoo · Accepted Answer · 2019-07-23 21:21:05Z

1

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

edited Jul 23, 2019 at 21:21

answered Jul 23, 2019 at 14:14

Cinchoo

6,3562 gold badges21 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

EnglishBanana Over a year ago

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 Over a year ago

I have added the JSON file

EnglishBanana Over a year ago

Where does this get written to? Also isn't StringBuilder msg empty/null?

Cinchoo Over a year ago

You can replace stringbuilder with file, if you want. I did for my test purpose.

EnglishBanana Over a year ago

Oh wow it totally worked. I'm really grateful. Thanks so much RajN.

|

Collectives™ on Stack Overflow

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

1 Answer 1

9 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related