asked on

Build JSON from table records

I get this data (csv formatted here) from a query.

"Webinarkey",        "RegistrantKey",     "PID",  "FullName"
4605887570527930114, 5668181830364643852, 909428, "Taylor Swifth",
5551459873814116097, 6629935645682877452, 909428, "Taylor Swifth",
7184734918871838978, 7781215580043136268, 909428, "Taylor Swifth",
3211296708669449729, 2705374026203470860, 909428, "Taylor Swifth",
1711808039457813249, 4033024420806207757, 909428, "Taylor Swifth",
1239773404021476866, 4653789994609595403, 909428, "Taylor Swifth",
4300862154261746178, 8978590339606068235, 909428, "Taylor Swifth",
8227760236281352705, 1170319454386360844, 909428, "Taylor Swifth",
1217612747145926657, 2082471002006026253, 909428, "Taylor Swifth",
9113395961717620484, 612206252367243021,  909428, "Taylor Swifth"
1217612747145926657, 7967558611165451531, 914251, "Katie Perry",
1711808039457813249, 1787578684817884171, 914251, "Katie Perry",
1239773404021476866, 3566719340901889036, 914251, "Katie Perry",
4300862154261746178, 6675608259647855116, 914251, "Katie Perry",
7184734918871838978, 3466720957533842189, 914251, "Katie Perry",
3211296708669449729, 6923047853794082829, 914251, "Katie Perry",
9113395961717620484, 640247097410423309,  914251, "Katie Perry",
5551459873814116097, 202236849192755211,  914251, "Katie Perry",
8227760236281352705, 623542217498013964,  914251, "Katie Perry",
4605887570527930114, 7202114900321460491, 914251, "Katie Perry"

Open in new window

I need to take the above data and loop through to create this JSON:

{
   "Webinars":[
      {
         "PID":909428,
         "FullName":"Taylor Swifth",
         "Attendances":[
            {
               "WebinarKey":4605887570527930114,
               "RegistrantKey":5668181830364643852
            },
            {
               "WebinarKey":5551459873814116097,
               "RegistrantKey":6629935645682877452
            },
            {
               "WebinarKey":7184734918871838978,
               "RegistrantKey":7781215580043136268
            },
            {
               "WebinarKey":3211296708669449729,
               "RegistrantKey":2705374026203470860
            },
            {
               "WebinarKey":1711808039457813249,
               "RegistrantKey":4033024420806207757
            },
            {
               "WebinarKey":1239773404021476866,
               "RegistrantKey":4653789994609595403
            },
            {
               "WebinarKey":4300862154261746178,
               "RegistrantKey":8978590339606068235
            },
            {
               "WebinarKey":8227760236281352705,
               "RegistrantKey":1170319454386360844
            },
            {
               "WebinarKey":1217612747145926657,
               "RegistrantKey":2082471002006026253
            },
            {
               "WebinarKey":9113395961717620484,
               "RegistrantKey":612206252367243021
            }
         ]
      },
      {
         "PID":914251,
         "FullName":"Katie Perry",
         "Attendances":[
            {
               "WebinarKey":1217612747145926657,
               "RegistrantKey":7967558611165451531
            },
            {
               "WebinarKey":1711808039457813249,
               "RegistrantKey":1787578684817884171
            },
            {
               "WebinarKey":1239773404021476866,
               "RegistrantKey":3566719340901889036
            },
            {
               "WebinarKey":4300862154261746178,
               "RegistrantKey":6675608259647855116
            },
            {
               "WebinarKey":7184734918871838978,
               "RegistrantKey":3466720957533842189
            },
            {
               "WebinarKey":3211296708669449729,
               "RegistrantKey":6923047853794082829
            },
            {
               "WebinarKey":9113395961717620484,
               "RegistrantKey":640247097410423309
            },
            {
               "WebinarKey":5551459873814116097,
               "RegistrantKey":202236849192755211
            },
            {
               "WebinarKey":8227760236281352705,
               "RegistrantKey":623542217498013964
            },
            {
               "WebinarKey":4605887570527930114,
               "RegistrantKey":7202114900321460491
            }
         ]
      }
   ]
}

Open in new window

I can handle C# or PHP code.

Tapan Pattanaik

Hi EddieShipman,

You can handle in C#. For that you have to use Json.NET

You can download the .dll from the below URL

Example :

Product product = new Product();
product.Name = "Apple";
product.Expiry = new DateTime(2008, 12, 28);
product.Sizes = new string[] { "Small" };

string json = JsonConvert.SerializeObject(product);

// {
//   "Name": "Apple",
//   "Expiry": "2008-12-28T00:00:00",
//   "Sizes": [
//     "Small"
//   ]
// }

http://www.newtonsoft.com/json

Eddie Shipman

ASKER

Sorry, Tapan, that example is too simple for what I need, please examine the data in the post. I know how to use Json.Net.

Ray Paseur

Eddie: Is this in a file somewhere? Or can you tell us how the data sets were acquired? The commas seem to indicate that there are three separate collections.

Ray Paseur

Also, do you need strict typing, or can we represent the long numbers as character strings instead of very-large integers?

Eddie Shipman

ASKER

@Ray,
The data has commas at the end of the line when it shouldn't. It should be straight CSV showing, I will modify. It doesn't matter as I will most likely be converting to C#.

Eddie Shipman

ASKER

I hate that they won't let you edit your own question...

"Webinarkey",        "RegistrantKey",     "PID",  "FullName"
4605887570527930114, 5668181830364643852, 909428, "Taylor Swift"
5551459873814116097, 6629935645682877452, 909428, "Taylor Swift"
7184734918871838978, 7781215580043136268, 909428, "Taylor Swift"
3211296708669449729, 2705374026203470860, 909428, "Taylor Swift"
1711808039457813249, 4033024420806207757, 909428, "Taylor Swift"
1239773404021476866, 4653789994609595403, 909428, "Taylor Swift"
4300862154261746178, 8978590339606068235, 909428, "Taylor Swift"
8227760236281352705, 1170319454386360844, 909428, "Taylor Swift"
1217612747145926657, 2082471002006026253, 909428, "Taylor Swift"
9113395961717620484, 612206252367243021,  909428, "Taylor Swift"
1217612747145926657, 7967558611165451531, 914251, "Katie Perry"
1711808039457813249, 1787578684817884171, 914251, "Katie Perry"
1239773404021476866, 3566719340901889036, 914251, "Katie Perry"
4300862154261746178, 6675608259647855116, 914251, "Katie Perry"
7184734918871838978, 3466720957533842189, 914251, "Katie Perry"
3211296708669449729, 6923047853794082829, 914251, "Katie Perry"
9113395961717620484, 640247097410423309,  914251, "Katie Perry"
5551459873814116097, 202236849192755211,  914251, "Katie Perry"
8227760236281352705, 623542217498013964,  914251, "Katie Perry"
4605887570527930114, 7202114900321460491, 914251, "Katie Perry"

Open in new window

Ray Paseur

OK, so it can be looked at as an array of arrays - titles on top and data rows below?

Eddie Shipman

ASKER

@Ray, this data will be queried from a table.
I guess you could say that.

Ray Paseur

OK, then it's a results set that would be accessed with some kind of iterator, retrieving each row one at a time. I think I can work with that. Back in a few moments...

Eddie Shipman

ASKER

You can make it work with an array because in C# it will be in a List object

SOLUTION

Ray Paseur

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

Chris Stanyon

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Fernando Soto

Hi Eddie;

The following code snippet using Linq will read a csv file, strip the header, split each line, group the data and create the classes that Newtonsoft will use to create the Json object.

char[] charsToTrim =  {' ', '\"'};

var res = (from string line in File.ReadAllLines("Data.csv")
           where !line.StartsWith("\"Webinarkey")
           let fields = line.Split(new string[] { "," }, StringSplitOptions.RemoveEmptyEntries).ToList()
           let groupFields = new { Webinarkey = fields[0], RegistrantKey = fields[1], PID = fields[2], FullName = fields[3] }
           group groupFields by groupFields.PID.Trim() into grouping
           select new Rootobject
           {
               Webinars = new Webinar[] {
                   new Webinar { PID = int.Parse(grouping.Key),
                                 FullName = grouping.Select(g => g.FullName ).First().Trim(charsToTrim),
                                 Attendances = (from att in grouping
                                                select new Attendance {
                                                    WebinarKey = long.Parse(att.Webinarkey),
                                                    RegistrantKey = long.Parse(att.RegistrantKey)
                                                }).ToArray()
                               }
               }
           }).ToList();

string theJasonObject = JsonConvert.SerializeObject(res);

Open in new window

SOLUTION