After using the API to retrieve a large data set from your Zendesk product, you might want to move the data set to a Microsoft Excel worksheet to more easily view and analyze the data. This tutorial teaches you how to
munge
the API data and write it to Excel. Data munging is the process of converting, or mapping, data from one format to another to be able to use it in another tool.

Disclaimer:
Zendesk provides this article for instructional purposes only. Zendesk does not support or guarantee the code. Zendesk also can't provide support for third-party technologies such as Perl. Please post any issue in the comments section or search for a solution online.

For all the possible data you can retrieve from your Zendesk product, see the "JSON Format" tables of the
API docs
. Most APIs have a "List" endpoint for getting multiple records.

Let's say you retrieved all the posts in a community topic and sideloaded the users who wrote the posts. The resulting data structure in Perl usually mirrors the JSON data returned by the API. In this case, it would consist of a Perl hash containing one array of posts and one array of users. Each item in the arrays would consist of a hash of properties. Example:

Also assume that you serialized the data structure in a file named
my_serialized_data
. Serializing a data structure means translating it into a format that can be stored and then reconstituted later in the same environment. Serializing your data is far more efficient than calling the API every time you need the data. Getting a large data set can involve hundreds if not thousands of API requests.

The
retrieve()
function reads the serialized data from the
my_serialized_data
file, reconstitutes it as a hash, and returns a reference to the hash in memory. The data is then dereferenced with
%{ $topic_hash_ref }
and assigned to a variable named
%topic_data
.

Munge the data

An Excel worksheet consists of a 2-dimensional table of rows and columns. Unfortunately, your data isn't in a neat 2-dimensional structure that can be easily written to Excel. It's in a hash consisting of an array of posts and an array of users. Each item in the arrays consists of a hash of properties. See
Get the data from the API
above for the structure.

The data also includes a lot of extra information you don't want in your Excel file. For example, each record contains all the attributes listed in the
Posts API doc
. You only want the following data about each post:

post id

title of the post

date created

name of the person who created it

The 'posts' array in your hash contains most of the data you want, including the 'id', 'title', and 'created_at' properties. However, the 'author_id' property consists of user ids, not actual user names. The associated user names are contained in the 'users' array.

The following sections describe how you can munge your data into rows of 4 cells:

You start by creating a row counter,
$r
, to increment the row index in each loop. You start at row 1 because row 0 already contains the column headings.

For each post in your (dereferenced) 'posts' array, the script loops through the 'users' array looking for an
id
value that matches the
author_id
value. When it finds a match, the script assigns the associated user name to the
$author
variable and breaks out of the loop.

The four write statements that follow write a row containing the post data in the worksheet:

Convert the ISO 8601 date strings

Massaging the data is sometime necessary before writing it to Excel. For example, dates returned by the API are ISO 8601 formatted strings such as
'2015-08-13T16:38:39Z'
. The script writes them to Excel as strings, but you can't easily reformat them as dates once in Excel. The Excel::Writer::XLSX module lets you write ISO 8601 dates as custom formatted dates in Excel.

If you need to use the date format in Excel for your analysis, update your script as follows (in
bold
):