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](https://stackoverflow.com/questions/59519370/convert-nested-complex-json-to-csv-not-get-actual-output) — Jawad, Jan 01 '20 at 14:30
Isnt this like the third time you are asking this same question? — Jawad, Jan 01 '20 at 14:32

score 2 · Accepted Answer · answered Jan 02 '20 at 00:51

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());

You saved lot of time, thanks @cinchoo – ThermeshB Jun 07 '23 at 17:08 — ThermeshB, Jun 07 '23 at 17:08

score 0 · Answer 2 · answered Jan 01 '20 at 11:54

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 01 '20 at 11:54

Gauravsa

6,330
2
21
30

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 – Kunjahamed P Jan 01 '20 at 12:03

JSON to CSV conversion using ChoETL Some array value missing

2 Answers2