JSON to CSV conversion using ChoETL Some array value missing

Question

I am converting a JSON file to a CSV file. The JSON has multiple nested objects and large size. While converting, I am able to get all the values out of the JSON and into the CSV. However, array values are missing . I am using CHOETL library. the sample json is (original json is long and big size)

{
    "getUsers": [
        {
            "UserInformation": {
                "Id": 1111122,
                "firstName": "*****1",
                "UserType": {
                    "name": "CP"
                },
                "primaryState": "MA",
                "otherState": [
                    "MA",
                    "BA"
                ],
                "createdAt": null
            }
        },
        {
            "UserInformation": {
                "Id": 3333,
                "firstName": "*****3",
                "UserType": {
                    "name": "CPP"
                },
                "primaryState": "MPA",
                "otherState": [
                    "KL",
                    "TN",
                    "DL",
                    "AP",
                    "RJ"
                ],
                "createdAt": null
            }
        }
    ]
}

the otherState array in first array is two but second array its four. its not showing is csv. the input json is long and nested hierarchy and mostly dynamic the code is

StringBuilder msg = new StringBuilder();

            using (var w = new ChoCSVWriter(msg)
                .WithFirstLineHeader()
                )
            {
                using (var r = new ChoJSONReader(@"E:/Temp/CSV/input/Data_Sample2.json")
                    .WithJSONPath("$..getUsers[*]")
                    )
                {
                    w.Write(r);
                }
            }
            File

the output is

UserInformation_Id,UserInformation_firstName,UserInformation_UserType_name,UserInformation_primaryState,UserInformation_otherState_0,UserInformation_otherState_1,UserInformation_createdAt
1111122,*****1,CP,MA,MA,BA, 3333,*****3,CPP,MPA,KL,TN,

Does this answer your question? Convert nested/complex JSON to CSV not get actual output — Jawad
– Jawad, Commented Jan 1, 2020 at 14:30
Isnt this like the third time you are asking this same question? — Jawad
– Jawad, Commented Jan 1, 2020 at 14:32

Cinchoo · Accepted Answer · 2020-01-02 00:51:13Z

2

You will have to mention the CSV driver to scan and discover fields automatically using MaxScanRows property (default 1)

Sample code below shows how to

StringBuilder csv = new StringBuilder();

using (var r = ChoJSONReader.LoadText(json)
    .WithJSONPath("$..getUsers[*]")
    )
{
    using (var w = new ChoCSVWriter(csv)
        .WithFirstLineHeader()
        .Configure(c => c.MaxScanRows = 2)
        .Configure(c => c.ThrowAndStopOnMissingField = false)
        )
    {
        w.Write(r);
    }
}

Console.WriteLine(csv.ToString());

answered Jan 2, 2020 at 0:51

Cinchoo

6,3562 gold badges21 silver badges36 bronze badges

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

1 Comment

ThermeshB Over a year ago

You saved lot of time, thanks @cinchoo

Gauravsa · Accepted Answer · 2020-01-01 11:54:30Z

0

I created a file test.json and put it in the bin directory. Test.json had the same contents above. then I used the following code:

using (var r = new ChoJSONReader("test.json"))
{
   using (var w = new ChoCSVWriter("sample.csv").WithFirstLineHeader())
   {
      w.Write(r);
   }
}

It generated the file sample.csv with the right array values. Are you sure, stringbuilder msg has the right content.

answered Jan 1, 2020 at 11:54

Gauravsa

6,6382 gold badges25 silver badges33 bronze badges

1 Comment

Kunjahamed P Over a year ago

its get output but i have two user, i want each user in single row. as per ur code its show only in single row. my real data have large size it difficult in single row

Collectives™ on Stack Overflow

JSON to CSV conversion using ChoETL Some array value missing

2 Answers 2

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related