How do I convert this data to a json format?

Hi All,

Can someone please help me in converting data to a json format. Our mobile application is pulling configuration details as a json format, to which we would like to add the templates for the feedback. Any help would be really appreciated. You can find the data in this file: Feedback-sample.xlsx

We would like to group the feedback into templates depending upon the 'sample type' and 'fruit numbers'. Every 'sample type' will contain a 'default' template and may contain 'other' template(s). As the name suggests 'default' template will act as the default template for that particular 'sample type'. 'Other' template(s) will contain a set of templates which are grouped by the 'fruit numbers'.

Example: (based on the attached file)

For 'sample type' -  'C'
Fruit number [60] has a template (Lets say Template A)
Fruit number [96, 186, 366] has a different template (Lets say Template B)
Reason I would like to have a 'default' template is, when a fruit number mentioned doesn't belongs to the one mentioned above, then it calls the 'default' template

Each feedback template will contain the following

1. Weather a feedback is 'Optional' or 'Required'
2. List of Items
      - Each 'Item' can either be of type 'category', 'checkbox', 'radio', 'textbox',  or a 'textarea'
      
Note:
1. If an 'Item' is a 'Category', then that 'Item' will contain a set of 'Sub-Items' and each 'Sub-Item' can either be of type 'checkbox', 'radio', 'textbox' or a 'textarea'
2. If an 'Item' is a 'radio', then there should be an option to provide choices for that 'Item'
3. If an 'Item' is a 'textarea' then there should be an option to provide the maximum number of character that are allowed in the textarea.

{ 
 "data": {
   "configuration": {
     "feedback": {
        ???????
      }
    }
  }
}

Open in new window

KreynoldAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ray PaseurCommented:
If you want to learn about how PHP handles JSON, we have an article here.  It has code examples.
http://www.experts-exchange.com/articles/22519/Understanding-JSON-in-PHP-and-JavaScript-Applications.html

This seems to be more of a requirement for application development, rather than a technical question with an answer, and for something that has a "deliverable" work product you might want to hire a professional developer who can lead you through the process of understanding the existing data, the requirements, the test plan, etc.  I can only help with some of the basics.

Simple Excel spreadsheets can often be saved in "csv" format.  To see what that looks like, here are a few rows from the spreadsheet posted with this question.
,Fruit Numbers,96,60,186,366,90,60,96,60,All,20,40,N/A,N/A,N/A,N/A,N/A
Feedback Items,Sub-Items,,,,,,,,,,,,,,,,
Nothing to Report,,1,,1,1,1,,1,,1,,,,,,,
Sampling Planning Variation,Variable Crop Load,1,,1,1,1,,1,,1,,,,,,,
,Variable Vine Age,1,,1,1,1,,1,,1,,,,,,,
,Stressed Vines,1,,1,1,1,,1,,1,,,,,,,
,Other - free text minimal character nos,1,,1,1,1,,1,,1,,,,,,,
,60 fruit from 9 sampling positions,,1,,,,1,,1,,,,,,,,

Open in new window

Each row of the spreadsheet corresponds to a row of the CSV file.  Empty columns have null fields in the CSV file.

PHP can read the CSV file one line at a time with fgetcsv().  Each call to fgetcsv() will return an array.  You can aggregate these individual arrays into other structures, like objects.  You probably want to do that, giving meaningful names to the object properties, because the fgetcsv() function returns only numerically indexed (positional) arrays.  This means that any change in your spreadsheet requires a programming change, too.  Not a very efficient way to work.

PHP objects can be collected inside other objects or arrays, just like in JavaScript.

Once you have the data in a useful structure, you can create a json string with the PHP function json_encode().  JSON is a data-interchange format that will let you use the json string in storage and transmission applications.

Now, that said, I would probably turn the problem around and approach it this way:

1. Start by defining the exact nature of the JSON string you must consume - what fields are required, which are optional, the formats, expected data types and ranges, etc.  What are the acceptable default values when you cannot get the data?  Write this down. Give a unique name to each field.

2. For each field, write down exactly where the data comes from.  Is it an HTML form?  Or a database?  Assign an "id" to each data source.  Determine the algorithm that must collect each field.

3.  Once you've completed 1 and 2, you have a source-to-data mapping document.  Armed with that information, you can start building your application.  I think you may find that the Excel spreadsheet you have here is an impediment to success, and could safely be omitted from the process.

Best of luck with the project, ~Ray

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ray PaseurCommented:
@Kreynold: I see you're new to Experts-Exchange and you may not understand the grading guidelines.  By giving the grade of "C" you have given the worst possible grade that anyone can give, to the only expert who tried to help you.  Since the expected and default grade is "A" you may want to read the grading guidelines and then furnish the required explanation.

http://support.experts-exchange.com/customer/portal/articles/481419
KreynoldAuthor Commented:
Hi Ray, Sorry if I offended you in any way. Your comment helped me a lot in writing down the JSON structure of the attached file. (Honestly, I tried to change the grades but it wasn't allowing me)

{
  "data": {
    "endpoint": "http://api-endpoint/version/",
    "tracking_frequency": {
      "sampling": 5,
      "otherwise": 120
    },
    "feedback_templates": [
      {
        "sample_type": [
          "C",
          "Wk10",
          "A",
          "M"
        ],
        "templates": {
          "default": {
            "feedback": "optional",
            "items": [
              {
                "item": "Sampling Planning Variation",
                "property": {
                  "type": "category"
                },
                "sub-items": [
                  {
                    "item": "Variable Crop Load",
                    "property": {
                      "type": "checkbox"
                    }
                  },
                  {
                    "item": "Variable Vine Age",
                    "property": {
                      "type": "checkbox"
                    }
                  },
                  {
                    "item": "Stressed Vines",
                    "property": {
                      "type": "checkbox"
                    }
                  },
                  {
                    "item": "Other",
                    "property": {
                      "type": "textarea",
                      "maxlength": 150
                    }
                  }
                ]
              },
              {
                "item": "Productivity Problem",
                "property": {
                  "type": "category"
                },
                "sub-items": [
                  {
                    "item": "Undervine shelter",
                    "property": {
                      "type": "checkbox"
                    }
                  },
                  {
                    "item": "Locked gate",
                    "property": {
                      "type": "checkbox"
                    }
                  },
                  {
                    "item": "Blocks difficult to find",
                    "property": {
                      "type": "checkbox"
                    }
                  },
                  {
                    "item": "Access difficult",
                    "property": {
                      "type": "checkbox"
                    }
                  },
                  {
                    "item": "Map Poor",
                    "property": {
                      "type": "checkbox"
                    }
                  },
                  {
                    "item": "Tracking phone problem",
                    "property": {
                      "type": "checkbox"
                    }
                  }
                ]
              },
              {
                "item": "OSHIssue",
                "property": {
                  "type": "category"
                },
                "sub-items": [
                  {
                    "item": "Holes",
                    "property": {
                      "type": "checkbox"
                    }
                  },
                  {
                    "item": "Dogs",
                    "property": {
                      "type": "checkbox"
                    }
                  },
                  {
                    "item": "Vehicles on orchard",
                    "property": {
                      "type": "checkbox"
                    }
                  },
                  {
                    "item": "Spraying",
                    "property": {
                      "type": "checkbox"
                    }
                  },
                  {
                    "item": "Upkeep of Orchard",
                    "property": {
                      "type": "checkbox"
                    }
                  },
                  {
                    "item": "Other",
                    "property": {
                      "type": "textarea",
                      "maxlength": 150
                    }
                  }
                ]
              },
              {
                "item": "Situation Not Covered in Training",
                "property": {
                  "type": "textarea",
                  "maxlength": 150
                }
              }
            ]
          },
          "count": [
            {
              "fruit_count": [
                60
              ],
              "template": {
                "feedback": "required",
                "items": [
                  {
                    "item": "Sampling Planning Variation",
                    "property": {
                      "type": "radio",
                      "options": [
                        "60 fruit from 9 sampling positions",
                        "60 fruit from lances 1, 2 & 3",
                        "60 fruit from normal gaps",
                        "60 fruit from where-ever they exist",
                        "Small fruit very hard to find"
                      ]
                    }
                  },
                  {
                    "item": "Productivity Problem",
                    "property": {
                      "type": "category"
                    },
                    "sub-items": [
                      {
                        "item": "Undervine shelter",
                        "property": {
                          "type": "checkbox"
                        }
                      },
                      {
                        "item": "Locked gate",
                        "property": {
                          "type": "checkbox"
                        }
                      },
                      {
                        "item": "Blocks difficult to find",
                        "property": {
                          "type": "checkbox"
                        }
                      },
                      {
                        "item": "Access difficult",
                        "property": {
                          "type": "checkbox"
                        }
                      },
                      {
                        "item": "Map Poor",
                        "property": {
                          "type": "checkbox"
                        }
                      },
                      {
                        "item": "Tracking phone problem",
                        "property": {
                          "type": "checkbox"
                        }
                      }
                    ]
                  },
                  {
                    "item": "OSH Issue",
                    "property": {
                      "type": "category"
                    },
                    "sub-items": [
                      {
                        "item": "Holes",
                        "property": {
                          "type": "checkbox"
                        }
                      },
                      {
                        "item": "Dogs",
                        "property": {
                          "type": "checkbox"
                        }
                      },
                      {
                        "item": "Vehicles on orchard",
                        "property": {
                          "type": "checkbox"
                        }
                      },
                      {
                        "item": "Spraying",
                        "property": {
                          "type": "checkbox"
                        }
                      },
                      {
                        "item": "Upkeep of Orchard",
                        "property": {
                          "type": "checkbox"
                        }
                      },
                      {
                        "item": "Other",
                        "property": {
                          "type": "textarea",
                          "maxlength": 150
                        }
                      }
                    ]
                  },
                  {
                    "item": "Situation Not Covered in Training",
                    "property": {
                      "type": "textarea",
                      "maxlength": 150
                    }
                  }
                ]
              }
            }
          ]
        }
      },
      {
        "sample_type": [
          "Res",
          "Pollen"
        ],
        "templates": {
          "default": {
            "feedback": "required",
            "items": [
              {
                "item": "Productivity Problem",
                "property": {
                  "type": "category"
                },
                "sub-items": [
                  {
                    "item": "Undervine shelter",
                    "property": {
                      "type": "checkbox"
                    }
                  },
                  {
                    "item": "Locked gate",
                    "property": {
                      "type": "checkbox"
                    }
                  },
                  {
                    "item": "Blocks difficult to find",
                    "property": {
                      "type": "checkbox"
                    }
                  },
                  {
                    "item": "Access difficult",
                    "property": {
                      "type": "checkbox"
                    }
                  },
                  {
                    "item": "Map Poor",
                    "property": {
                      "type": "checkbox"
                    }
                  },
                  {
                    "item": "Tracking phone problem",
                    "property": {
                      "type": "checkbox"
                    }
                  }
                ]
              },
              {
                "item": "OSH Issue",
                "property": {
                  "type": "category"
                },
                "sub-items": [
                  {
                    "item": "Holes",
                    "property": {
                      "type": "checkbox"
                    }
                  },
                  {
                    "item": "Dogs",
                    "property": {
                      "type": "checkbox"
                    }
                  },
                  {
                    "item": "Vehicles on orchard",
                    "property": {
                      "type": "checkbox"
                    }
                  },
                  {
                    "item": "Spraying",
                    "property": {
                      "type": "checkbox"
                    }
                  },
                  {
                    "item": "Upkeep of Orchard",
                    "property": {
                      "type": "checkbox"
                    }
                  },
                  {
                    "item": "Other",
                    "property": {
                      "type": "textarea",
                      "maxlength": 150
                    }
                  }
                ]
              },
              {
                "item": "Situation Not Covered in Training",
                "property": {
                  "type": "textarea",
                  "maxlength": 150
                }
              }
            ]
          }
        }
      },
      {
        "sample_type": [
          "STR",
          "STT",
          "KSR",
          "KST"
        ],
        "templates": {
          "default": {
            "feedback": "required",
            "items": [
              {
                "item": "Productivity Problem",
                "property": {
                  "type": "category"
                },
                "sub-items": [
                  {
                    "item": "Undervine shelter",
                    "property": {
                      "type": "checkbox"
                    }
                  },
                  {
                    "item": "Locked gate",
                    "property": {
                      "type": "checkbox"
                    }
                  },
                  {
                    "item": "Blocks difficult to find",
                    "property": {
                      "type": "checkbox"
                    }
                  },
                  {
                    "item": "Access difficult",
                    "property": {
                      "type": "checkbox"
                    }
                  },
                  {
                    "item": "Map Poor",
                    "property": {
                      "type": "checkbox"
                    }
                  },
                  {
                    "item": "Tracking phone problem",
                    "property": {
                      "type": "checkbox"
                    }
                  }
                ]
              },
              {
                "item": "OSH Issue",
                "property": {
                  "type": "category"
                },
                "sub-items": [
                  {
                    "item": "Holes",
                    "property": {
                      "type": "checkbox"
                    }
                  },
                  {
                    "item": "Dogs",
                    "property": {
                      "type": "checkbox"
                    }
                  },
                  {
                    "item": "Vehicles on orchard",
                    "property": {
                      "type": "checkbox"
                    }
                  },
                  {
                    "item": "Spraying",
                    "property": {
                      "type": "checkbox"
                    }
                  },
                  {
                    "item": "Upkeep of Orchard",
                    "property": {
                      "type": "checkbox"
                    }
                  },
                  {
                    "item": "Other",
                    "property": {
                      "type": "textarea",
                      "maxlength": 150
                    }
                  }
                ]
              },
              {
                "item": "Situation Not Covered in Training",
                "property": {
                  "type": "textarea",
                  "maxlength": 150
                }
              },
              {
                "item": "Antibiotic Sample Assessment Result",
                "property": {
                  "type": "radio",
                  "options": [
                    "Pass",
                    "Fail"
                  ]
                }
              }
            ]
          }
        }
      }
    ]
  }
}

Open in new window

Ray PaseurCommented:
you can use the request attention button to get help from a moderator.  And don't worry about offending me - I am not offended at all.  It's hard to convey emotion in an online forum.

The community standards at E-E are often overlooked in the "on-boarding" of new members.

In any case, best of luck with your project! ~Ray
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
JSON

From novice to tech pro — start learning today.