(Deprecated) Converting Analytics JSON to CSV

With the Backlot UI you can download data in comma-separated value (CSV) format. Here is a general approach for doing this programmatically.

Warning: The v2 Analytics API has been deprecated. See Ooyala IQ Analytics for more details on the new v3 Analytics API.

All responses by the Analytics API are in the form of JavaScript Object Notation (JSON). Much useful analysis can be done with spreadsheet programs, which can in general read CSV data.

Because the Backlot API is language-neutral, we do not focus on any particular language, relying on JavaScript to illustrate the conversion. The Internet has many free converter tools for many different languages.

Below is a sample JavaScript program (based somewhat on ideas from json.org) that converts the JSON in an array (named json3) to CSV. In actual practice, instead of defining an array, you might want to save the JSON responses to files and read the file, or change the script below to read from some other data source. Likewise, this script opens a new window to prompt to download the CSV data; you might want to change this behavior.
<script src="scripts/json.js" type="text/javascript"></script>
<script type="text/javascript">
var json3 = { "d": "[{\"Id\":1,\"UserName\":\"Sam Smith\"},{\"Id\":2,\"UserName\":\"Fred Frankly\"},{\"Id\":1,\"UserName\":\"Zachary Zupers\"}]" }

DownloadJSON2CSV(json3.d);

function DownloadJSON2CSV(objArray)
{
   var array = typeof objArray != 'object' ? JSON.parse(objArray) : objArray;

   var str = '';

   for (var i = 0; i < array.length; i++) {
       var line = '';

       for (var index in array[i]) {
            line += array[i][index] + ',';
       }

       // Here is an example where you would wrap the values in double quotes
       // for (var index in array[i]) {
       //    line += '"' + array[i][index] + '",';
       // }

       line.slice(0,line.Length-1); 

       str += line + '\r\n';
   }
   // Might want to change this output
   window.open( "data:text/csv;charset=utf-8," + escape(str))
}
</script>
    
  1. Make the API requests necessary to retrieve the data you want. Be sure that you are dealing with data that is amenable to conversion and that you are including similar dimensions in the data, regardless of type.
  2. Convert the data to CSV by using the desired converter program, either a variant of the JavaScript shown above or for your own preferred language.

Was this article helpful?