How to manage the .json chat logs via Microsoft Excel in Windows

    With the rollout of the new IBM Video Streaming chat comes a lot of new enhanced features like threads, emojis, direct messaging and more. Providing reporting on these many features creates a much more complex data set when compared to the IBM Video Streaming legacy chat. In order to help customers utilize the fullest capabilities of the new chat we have updated the export data to a use a .json format.

.json is a very common file format and data-interchange format used to transmit data objects which consists of attribute value pairs. When opened in a browser or text editor the way the data appears can be quite overwhelming. However when filtered appropriately or brought into a data visualization system it can be very quick and easy to find the exact information you are looking for. 

   For those who are looking for a simple .xls file similar to what was available with the legacy chat, it’s possible to accomplish in a few ac<ons with Microsoft Excel. The following example will show how to gather "who sent the chat?” and “what was the message?” data. 

  Get the messages and who wrote the messages. 

First, locate your .json file. Next open Microsoft Excel and select Data>Get Data>From File>From JSON 



Upon selecting the .json file a new window will open up.


After you click on the Record button you will be presented with the first level of the nested .json file structure. 


If you click on Messages you will see records of all the messages found in the file. Next click on To Table to convert the list into a table. 


After the list is converted into a table, feel free to rename the column (ex: messages) and click on the filter button next to the name. Here you can decide what items you would want to filter from the whole object file.  


For this example we will select “user” and “text”.
Click OK once you have selected your values. 

You will see that there are now 3 columns and each row contains the selected values and each row pertains to an individual message.  


Next step is to filter out the user object and only display the name of the user. To do this click on the filter button next to the user column and select the “name” value or “original id” and click OK.  

The column will now contain the names of each of the users. 


Finally in order to save your changes to the query you will click on Close & Load at the top of the tool bar. 


Your chat data is now in an .xls format!


Powered by Zendesk