Marani 80 Posted September 9, 2017 Report Share Posted September 9, 2017 Hi, Using a website's API I get needed data.But I'm confused about adding JSON data to a table. I'm using "add list to list" and then add them to table columns. As you can see in the image I can't get the desired table at the end. I don't know what is the sequence of steps to take after having JSON data. like, to group set of phone numbers to a cell along with other values (state, city etc) and after that, get rid of brackets... For example, the first 3 phone numbers belong to the first row the next 2 belong to the second row etc. It seems "add list to list" is not the proper command to use.To use "add item to list" I don't know how many array values are there for a key (e.g phone numbers) to use inside a loop. Thanks in advanced for any help. Quote Link to post Share on other sites
Code Docta (Nick C.) 638 Posted September 10, 2017 Report Share Posted September 10, 2017 That's not how it works bud. The data needs to be normalized first. You need to parse the data or use Python to read the JSON or something similar. I made a JSON parser with Regex which you can get here http://network.ubotstudio.com/forum/index.php/topic/21270-youtube-api-getting-more-results-with-comments-and-hopefully-other-things/?do=findComment&comment=130758 You will need to parse each field separately. Have you read the doc's to see if they provide a CSV download instead of returning JSON? define $JSON object parser(#JSON STRING, #OBJECT) { return($find regular expression(#json string,"(?<= \"{#OBJECT}\": \").*(?=\",)")) } Add each field(object) to a list then add to table as column. add list to table as column(&data,0,0,%phone numbers) If you see the example on the other page you will see how you get a list. Regards,CD 1 Quote Link to post Share on other sites
Marani 80 Posted September 10, 2017 Author Report Share Posted September 10, 2017 Thanks a lot, Nick! that post was a sample I needed to understand how it works. everything works great now and I have the data in correct format inside a CSV file. The question I have now is, how do I get the list of total values of a key when it varies. like in the first posts' image, the number of Phones for each record (to use for the number of loop cycles). or even total number of records that a site returns in JSON? Thanks Quote Link to post Share on other sites
Code Docta (Nick C.) 638 Posted September 11, 2017 Report Share Posted September 11, 2017 NP, glad I an help somewhat...ys, Pyhon or some javascript would be more helpful in this case.Post some example JSON so we know exactly what that looks like and I or someone can help you. Regards,CD Quote Link to post Share on other sites
Marani 80 Posted September 11, 2017 Author Report Share Posted September 11, 2017 Here is a sample buddy: { "SearchResults": [{ "OrgName": "Marketing Group", "Category": "Marketing Consultants", "City": "Portland", "StateProvince": "OR", "Phones": ["(440) 914-0430", "(503) 235-6473", "(888) 326-7156"], "PostalCode": "97214", "Address": "3340 SE Morrison, Ste 391", "AltOrgNames": null, "OrgType": "Business", "OrgChanged": "2016-10-29T20:51:51.07", "RatingLastChanged": "2016-08-09T00:39:31", "AccreditationStatusLastChanged": null, "IsICEParticipant": null }, { "OrgName": "Corvel Corp", "Category": "Health & Medical - General", "City": "Portland", "StateProvince": "OR", "Phones": ["(913) 498-1885", "(503) 222-3144"], "PostalCode": "97201-5632", "Address": "1300 SW 5th Ave Ste 2500", "AltOrgNames": null, "OrgType": "Business", "OrgLastChanged": "2016-11-05T22:51:15.11", "RatingLastChanged": "2015-01-28T20:33:46", "AccreditationStatusLastChanged": null, "IsICEParticipant": null }, { "OrgName": "Actuarial Services", "Category": "Actuaries", "City": "Eugene", "StateProvince": "OR", "Phones": ["(414) 302-9184", "(541) 344-2324", "(541) 344-9104"], "PostalCode": "97440", "BusinessURLs": ["http://www.summitbenefit.com"], "Address": "374 W. 12th St.", "AltOrgNames": ["Gucciardi Resources"], "OrgType": "Business", "OrgLastChanged": "2016-11-09T16:10:58.27", "RatingLastChanged": "2015-01-28T20:34:19", "AccreditationStatusLastChanged": null, "IsICEParticipant": null }, { "OrgName": "ATG Northwest", "Category": "Wheel Chairs", "City": "Milwaukie", "StateProvince": "OR", "Phones": ["(866) 557-6825"], "PostalCode": "97222-8824", "Address": "4211 SE International Way Ste C", "AltOrgNames": ["ATG Rehab", "Wheelchair Works, Inc"], "OrgType": "Business", "OrgLastChanged": "2016-11-05T22:20:26.37", "RatingLastChanged": "2016-08-01T03:24:57", "AccreditationStatusLastChanged": null, "IsICEParticipant": null } ] } 1 Quote Link to post Share on other sites
Code Docta (Nick C.) 638 Posted September 11, 2017 Report Share Posted September 11, 2017 Here is your Phones list code set(#JSON,"\{ \"SearchResults\": [\{ \"OrgName\": \"Marketing Group\", \"Category\": \"Marketing Consultants\", \"City\": \"Portland\", \"StateProvince\": \"OR\", \"Phones\": [\"(440) 914-0430\", \"(503) 235-6473\", \"(888) 326-7156\"], \"PostalCode\": \"97214\", \"Address\": \"3340 SE Morrison, Ste 391\", \"AltOrgNames\": null, \"OrgType\": \"Business\", \"OrgChanged\": \"2016-10-29T20:51:51.07\", \"RatingLastChanged\": \"2016-08-09T00:39:31\", \"AccreditationStatusLastChanged\": null, \"IsICEParticipant\": null \}, \{ \"OrgName\": \"Corvel Corp\", \"Category\": \"Health & Medical - General\", \"City\": \"Portland\", \"StateProvince\": \"OR\", \"Phones\": [\"(913) 498-1885\", \"(503) 222-3144\"], \"PostalCode\": \"97201-5632\", \"Address\": \"1300 SW 5th Ave Ste 2500\", \"AltOrgNames\": null, \"OrgType\": \"Business\", \"OrgLastChanged\": \"2016-11-05T22:51:15.11\", \"RatingLastChanged\": \"2015-01-28T20:33:46\", \"AccreditationStatusLastChanged\": null, \"IsICEParticipant\": null \}, \{ \"OrgName\": \"Actuarial Services\", \"Category\": \"Actuaries\", \"City\": \"Eugene\", \"StateProvince\": \"OR\", \"Phones\": [\"(414) 302-9184\", \"(541) 344-2324\", \"(541) 344-9104\"], \"PostalCode\": \"97440\", \"BusinessURLs\": [\"http://www.summitbenefit.com\"], \"Address\": \"374 W. 12th St.\", \"AltOrgNames\": [\"Gucciardi Resources\"], \"OrgType\": \"Business\", \"OrgLastChanged\": \"2016-11-09T16:10:58.27\", \"RatingLastChanged\": \"2015-01-28T20:34:19\", \"AccreditationStatusLastChanged\": null, \"IsICEParticipant\": null \}, \{ \"OrgName\": \"ATG Northwest\", \"Category\": \"Wheel Chairs\", \"City\": \"Milwaukie\", \"StateProvince\": \"OR\", \"Phones\": [\"(866) 557-6825\"], \"PostalCode\": \"97222-8824\", \"Address\": \"4211 SE International Way Ste C\", \"AltOrgNames\": [\"ATG Rehab\", \"Wheelchair Works, Inc\"], \"OrgType\": \"Business\", \"OrgLastChanged\": \"2016-11-05T22:20:26.37\", \"RatingLastChanged\": \"2016-08-01T03:24:57\", \"AccreditationStatusLastChanged\": null, \"IsICEParticipant\": null \} ] \} ","Global") clear list(%phones) add list to list(%phones,$list from text($JSON PHONES parser(#JSON, "::"),$new line),"Delete","Global") define $JSON PHONES parser(#JSON STRING, #DELIMITER) { comment("need to press \"\" button before hitting ok if you decide to edit the $replace function. for the search text field.") return($replace($find regular expression(#JSON STRING,"(?<=\"Phones\": \\[\").*(?=\"\\],)"),"\", \"",#DELIMITER)) } Regards, CD 1 Quote Link to post Share on other sites
Marani 80 Posted September 11, 2017 Author Report Share Posted September 11, 2017 Thank you, to get the total number of all returned results is it the correct way to count (add to a list) a key that exists in all set of records (like the city)? Quote Link to post Share on other sites
Code Docta (Nick C.) 638 Posted September 11, 2017 Report Share Posted September 11, 2017 Your welcome, Yes, that is one way, something that will definitely be there. But look like "null" is returned if nothing. Also, in the returned JSON should some data about how many it returned. Usually at the end or beginning. CD 1 Quote Link to post Share on other sites
Marani 80 Posted September 12, 2017 Author Report Share Posted September 12, 2017 I sent you a PM and after that found the solution. I appreciate your help in the whole process bro. it's all done now. time to make money... 1 Quote Link to post Share on other sites
Code Docta (Nick C.) 638 Posted September 12, 2017 Report Share Posted September 12, 2017 Glad I can help. Quote Link to post Share on other sites
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.