Microsoft Gold Azure and Dynamics CRM Partner | Salesforce Experts

If you ran into this article, then like me you’re wondering why the heck the InsideView API doesn’t allow you to pull bulk data in JSON format. InsideView’s justification is likely that the CSV format (the only option you currently have for receiving bulk data) is much more space efficient since it doesn’t contain metadata per row. Instead, CSV has the header information in the first row, and then each consecutive row is represented by comma separated values (duh…). However, This has one big downside – the header information is only one level deep. Let me give a brief example to show the advantages of JSON and the downside of CSV formats.

JSON

CSV

Let’s look at the ticker field. As we can observe, there are two big issues with CSV format. First, the naming convention of the headers doesn’t match. The JSON format has the field named “tickers” and the CSV format names it “ticker.” Second, there is no way we can get the property names of the ticker object (tickerName and exchange) from the CSV format.

Despite the space advantage, in this day and age, I still expect a robust API to delegate the choice of efficiency to the developer. But enough complaining – what can we do to get around this problem?

Well, before I propose a solution, let me give you a bit of a background to my situation. As part of our solution, we needed to pull bulk data from InsideView via Subscription API and store it in a database in JSON format. We wanted to be able to pull that data when needed and convert it to dynamic objects (ExpandoObject) and access the fields by name. In other words, we needed the solution to be as flexible as possible. That said, if you are dealing with bulk CSV data and want to be able to convert it to objects or even access any of the properties by name, you have two choices: create a class with static (hard-coded) fields or create some sort of a smart mapping.

The first option is obviously very easy to implement; however, this restricts you to the existing InsideView API. If anything changes in the InsideView API, such as new fields being added, you’ll need to modify the class, recompile, and redeploy the solution. In our case, this was unacceptable, so we went for the second solution.

Enter XML mapping. To overcome the limitation of a statically defined class, we decided to create an XML mapping file that can be configured dynamically. Essentially, our XML file contains the skeleton of the JSON format. Each node in the XML file represents a field in the bulk API. Combined with nested structure and attributes that dictate the correct name mapping and whether the field is an array, we can create a proper dynamic object and serialize it as JSON to store in the DB. Below we provided an example of such an XML file and code to read it.

XML Structure

 

Code to read XML

 

Essentially, our solution is an example of how dynamic mapping can be used to properly read the InsideView data from the original CSV format. Depending on the level of robustness required, this solution can be simplified or elaborated on. The choice is yours, now go have fun with InsideView!