I'm working on a research project and a research partner has provided me with a massive file with data I need to be able to analyze from his website (which aggregates the data I'm interested in from internet sources). I've tried importing the .JSON file into excel, but it's awkward and figuring out an effective way to delineate the data has proven nearly impossible (for example, using ':' doesn't work because in one of the fields I need to study, colons appear, so mid-data field it gets cut-off). Is there any program, technique, or converter to take the JSON file and organize the data into something resembling a spreadsheet?
My ultimate destination/goal: I need to sample the data, but only for a couple variables (or I guess I should say fields as in JSON format/lingo each variable contains a couple fields of interest that I need to compare with similar fields from different variables as well as a number of null values/fields), for my data analysis. I need to get the data into some sort of spreadsheet-type format or something that is visually, if not functionally, similar that would allow me to figure out how many datapoints I have in the relevant categories and to randomly sample them. So, to summarize, is there any way to transform the tree-like json file into something column/row oriented automatically, because the JSON file is 35mb, and there's no way I could organize/extract the data I need by hand?
Thanks for any help!