Convert dynamic JSON format to CSV using ChoETL

Question

I would receive JSON in various formats and hierarchies, Need to know the possibility to be able to convert any JSON format into CSV by forming columns dynamically.As an example,i have provided the required details,below is an multi level JSON and the expected CSV output

JSON

{
   "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"
            ],
            "createdAt":null
         }
      }
   ]
}

CSV Output

Li-Jyu Gao · Answer 1 · 2020-07-29T13:21:38.510

A workable code like this:

Read json string and write CSV file

 StringBuilder sb = new StringBuilder();
            sb.Append(@"
{
   ""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""
            ],
            ""createdAt"":null
         }
      }
   ]
}
");

var reader = ChoJSONReader<User>.LoadText(sb.ToString());
var user = reader.Read();

using (var parser = new ChoCSVWriter<UserInformation>("User.csv").WithFirstLineHeader())
{
    parser.Write(user.getUsers.Select(x => x.UserInformation));
}

Serialization Classes

class User
{
    public List<UserDetail> getUsers { get; set; }
}

class UserDetail
{
    public UserInformation UserInformation { get; set; }
}
class UserInformation
{
    public int Id { get; set; }
    public string firstName { get; set; }
    public UserType UserType { get; set; }
    public string primaryState { get; set; }
    public List<string> otherState { get; set; }
    public DateTime? createdAt { get; set; }

}

class UserType
{
    public string name { get; set; }
}

Edit

Dynamic parsing all format only with JSON reader is impossible. You need some tricky steps to parse it.

Get a json string and minimize it.
Use regular expression or something, recognize what the json pattern is.
Use the pattern to get correct Model Class to deserialize it.

Hi @Li-Jyu Gao Thank you for the answer, we thad tried this option already. But we have different types of JSON formats coming in. how do have the transformation to CSV code dynamic and is there a possibility to convert complex json to CSV without using model object? — user2586782, Jul 29 '20 at 10:35
No one direct method to parse all dynamic json format. I have a kind of solution in edit. — Li-Jyu Gao, Jul 29 '20 at 13:21

Cinchoo · Answer 2 · 2021-10-20T20:09:51.783

Here is how you can do it using Cinchoo ETL programatically.

First construct configuration object, along with fields configurations at runtime and pass it to reader for loading JSON

StringBuilder csv = new StringBuilder();

var config = new ChoJSONRecordConfiguration();
config.JSONPath = "$..getUsers[*].UserInformation";
config.AllowComplexJSONPath = true;

config.JSONRecordFieldConfigurations.Add(new ChoJSONRecordFieldConfiguration("Id"));
config.JSONRecordFieldConfigurations.Add(new ChoJSONRecordFieldConfiguration("FirstName"));
config.JSONRecordFieldConfigurations.Add(new ChoJSONRecordFieldConfiguration("UserType", "$.UserType.name"));
config.JSONRecordFieldConfigurations.Add(new ChoJSONRecordFieldConfiguration("primaryState"));
config.JSONRecordFieldConfigurations.Add(new ChoJSONRecordFieldConfiguration("otherState", "$.otherState[*]") { FieldType = typeof(string[]) });
config.JSONRecordFieldConfigurations.Add(new ChoJSONRecordFieldConfiguration("createdAt"));

using (var r = ChoJSONReader.LoadText(json, config))
{
    using (var w = new ChoCSVWriter(csv).WithFirstLineHeader()
        .UseNestedKeyFormat(false)
        )
        w.Write(r);
}

Console.WriteLine(csv.ToString());

Fiddle: https://dotnetfiddle.net/Gfw3r7

Tried testing with the above code provided, but it gives the error "Error reading JArray from JsonReader. Path '', line 0, position 0." — user2586782, Nov 09 '20 at 05:40

Convert dynamic JSON format to CSV using ChoETL

2 Answers2