Converting a Microsoft Graph JSON to CSV using Cinchoo ETL

Question

I am trying to convert a JSON file ( a Microsoft.Graph.Event ) to a CSV file. I'm using Cinchoo ETL to do this. Here is the URL I'm referring to:

https://www.codeproject.com/Articles/1193650/Cinchoo-ETL-Quick-Start-Converting-JSON-to-CSV-Fil

Here is my code:

            using (var csv = new ChoCSVWriter(path + calendarId + ".csv").WithFirstLineHeader())
            {
                using (var json = new ChoJSONReader(path + calendarId + ".json")
                    .WithField("id")
                    .WithField("iCalUId")
                    .WithField("isAllDay")
                    .WithField("isCancelled")
                    .WithField("isOrganizer")
                    .WithField("isOnlineMeeting")
                    .WithField("onlineMeetingProvider")
                    .WithField("type")
                    .WithField("startTime", jsonPath: "$.start.dateTime")
                    .WithField("endTime", jsonPath: "$.end.dateTime")
                    .WithField("location", jsonPath: "$.location.displayname")
                    .WithField("locationType", jsonPath: "$.location.locationType")
                    .WithField("organizer", jsonPath: "$.organizer.emailAddress.name")
                    .WithField("recurrence", jsonPath: "$.recurrence.pattern.type")
                    )
                {
                    csv.Write(json);
                }
            }

While I do get a CSV, and most of the headlines and values are correct, some of them are odd. Some of the headlines get a "_0" on the back, and some of the values are just a repetition of the prior column, instead of what it should be. snapshot of csv file

I've checked the JSON file which I've written out in advance, but they were just fine. I'm using .Net Core 3.1.

I am just a beginner, and any help or advice is greatly appreciated. Thank you.

EDIT adding the snapshot of CSV where some of the values are just a repetition of the prior column "startTime", instead of what it should be.

This is the expected CSV output:

Part of the JSON file

  "start": {
    "dateTime": "2020-05-17T00:00:00.0000000",
    "timeZone": "UTC",
    "@odata.type": "microsoft.graph.dateTimeTimeZone"
  },
  "end": {
    "dateTime": "2020-05-18T00:00:00.0000000",
    "timeZone": "UTC",
    "@odata.type": "microsoft.graph.dateTimeTimeZone"
  },
  "location": {
    "displayName": "asdfads",
    "locationType": "default",
    "uniqueId": "b0fd5377-937d-4fb2-b70a-0a696972b46c",
    "uniqueIdType": "locationStore",
    "@odata.type": "microsoft.graph.location"
  },

Would be nice to post snippet of JSON file. Based on your code, looks like some of properties are pointing to JSON array values, which output them with postfix _0, _1, etc. May be you need to change the JSONPath like `.WithField("startTime", jsonPath: "$.start.dateTime[0]")` to correct it? — Cinchoo, May 26 '20 at 16:12
Or try with `.WithField("startTime", jsonPath: "$.start.dateTime", isArray: false)` — Cinchoo, May 26 '20 at 16:21
TY for quick responce. This is the link to the full JSON schema [link](https://learn.microsoft.com/en-us/graph/api/resources/event?view=graph-rest-1.0#json-representation). One of your our codes `.WithField("startTime", jsonPath: "$.start.dateTime", isArray: false)` worked for the postfix _0, while `.WithField("startTime", jsonPath: "$.start.dateTime[0]")` didn't. Also there is this error that I am still getting where when I change the order of columns, some of the values in the CSV just become a repetition of the prior column. — beadaman, May 26 '20 at 21:55

Cinchoo · Accepted Answer · 2021-10-07T20:10:00.887

Here you go, I took a sample JSON from

https://learn.microsoft.com/en-us/graph/api/calendar-post-events?view=graph-rest-1.0&tabs=http

used for testing it.

Here is the code, using ChoETL v1.2.0.2 (latest):

StringBuilder csv = new StringBuilder();

using (var w = new ChoCSVWriter(csv)
    .WithFirstLineHeader()
    )
{
    using (var r = new ChoJSONReader(@"*** YOUR GRAPH JSON FILE PATH ***")
        .WithField("id")
        .WithField("iCalUId")
        .WithField("isAllDay")
        .WithField("isCancelled")
        .WithField("isOrganizer")
        .WithField("isOnlineMeeting")
        .WithField("onlineMeetingProvider")
        .WithField("type")
        .WithField("startTime", jsonPath: "$.start.dateTime", isArray: false)
        .WithField("endTime", jsonPath: "$.end.dateTime", isArray: false)
        .WithField("location", jsonPath: "$.location.displayname")
        .WithField("locationType", jsonPath: "$.location.locationType", isArray: false)
        .WithField("organizer", jsonPath: "$.organizer.emailAddress.name", isArray: false)
        .WithField("recurrence", jsonPath: "$.recurrence.pattern.type")
    )
    {
        w.Write(r);
    }
}

Console.WriteLine(csv.ToString());

Output:

id,iCalUId,isAllDay,isCancelled,isOrganizer,isOnlineMeeting,onlineMeetingProvider,type,startTime,endTime,location,locationType,organizer,recurrence
AAMkAGViNDU7zAAAAA7zAAAZb2ckAAA=,040000008200E641B4C,False,False,True,False,unknown,singleInstance,3/15/2019 12:00:00 PM,3/15/2019 2:00:00 PM,,default,Megan Bowen,

UPDATE: Here is updated code to change the order of the fields and get the attendees count

StringBuilder csv = new StringBuilder();

using (var w = new ChoCSVWriter(csv)
    .WithFirstLineHeader()
    )
{
    using (var r = new ChoJSONReader(@"*** YOUR GRAPH JSON FILE PATH ***")
        .WithField("startTime", jsonPath: "$.start.dateTime", isArray: false)
        .WithField("endTime", jsonPath: "$.end.dateTime", isArray: false)
        .WithField("id")
        .WithField("iCalUId")
        .WithField("isAllDay")
        .WithField("isCancelled")
        .WithField("isOrganizer")
        .WithField("isOnlineMeeting")
        .WithField("onlineMeetingProvider")
        .WithField("type")
        .WithField("location", jsonPath: "$.location.displayname")
        .WithField("locationType", jsonPath: "$.location.locationType", isArray: false)
        .WithField("organizer", jsonPath: "$.organizer.emailAddress.name", isArray: false)
        .WithField("recurrence", jsonPath: "$.recurrence.pattern.type")
        .WithField("attendees", jsonPath: "$.attendees[*]", valueConverter: o => ((IList)o).Count)
    )
    {
        w.Write(r);
    }
}

Console.WriteLine(csv.ToString());

Thank you @RajN for quick answers. Let me check this code afterwards. — beadaman, May 26 '20 at 22:58
Your code worked, and I've accepted the answer! I do have some additional questions though, can you not change the order of columns? ex.) moving startTime and endTime to the left. And, how do you handle array values where you can't tell how many arrays there will be? ex.) "attendees":[]. I just need the total number of attendees for each JSON. — beadaman, May 27 '20 at 18:21

Converting a Microsoft Graph JSON to CSV using Cinchoo ETL

1 Answers1