Guide
to transferring OnPing data tables to Excel sheets: #

Table of Content #

1. Setting up template file
2. Writing DHALL file
3. Transferring data table

Requirements #

You will need:

• An Excel (.xls) file – This will serve as the
  Template
• A Table in OnPing to pull data from
  

  What is the Template? #

  We need a template so Excel can make sense of the Onping data. This
  will work together with the map we create inside the Dhall file to
  format our excel table.

  1. Setting up template file #

  • First, open/create our excel file that we wish to put all of our
    data in.
  • Format the file so that each row and column is organized to account
    for future changes or additions to
  • Initially, fill each data value inside the spreadsheet with a blank
    space “_“. This will represent data that should be there and help
    identify if something is missing

  The image below can be used as a visual reference for what the spreadsheet should look like. You can use multiple sheets within an Excel file. 

  

  2. Writing DHALL file #

Creating the base .DHALL
File

• Open up your preferred development environment
• Create a new file (.DHALL)
• Insert code below (from Example 1) into the new dhall file

Example Code 1

let CellTransform =
{ from : { _1 : { _unCRow : Natural }, _2 : { _unCCol : Natural } }
, to : { _1 : { _unXRow : Natural }, _2 : { _unXCol : Natural } }
}

in let pairTransform =
\(r : Natural) ->
\(c : Natural) ->
\(er : Natural) ->
\(ec : Natural) ->
{ from = { _1._unCRow = r, _2._unCCol = c }
, to = { _1._unXRow = er, _2._unXCol = ec }
}
: CellTransform

in [ { pairTransforms = [ pairTransform 0 0 1 1 ]
, sheetName.unSheetName = “SHEET1”
}
]

This code just formats our data to make it readable between OnPing
and the .xls file upon resolution. For now, let’s move on to where most
time will probably be spent.

Formatting the ‘Sheets’

Below is an example of one of the rows we’ll be putting into our
template. The first line is a comment, indicating how important it is to
keep track of which sheet you are working on. Each sheet must be
formatted individually. We recommend keeping track of each sheet with a
similar comment line, respective to each sheet as you work. These
sections will get extremely long very quickly – so this is
important.

Now, let’s take a closer look at the 3rd line. This line is where we
create the basic map for our .DHALL file.

How To Create Sets for
our .Dhall map

Our map will reference pairings of data between the data record in
OnPing and the .xls Template file. In Example 2 there are 4 lines, each
labeled with a reference to an OnPing Row/Column or a Template
Row/Column. We will refer to these as set. For each set:

• Name the set
  • Be consistent with your labels. These maps can become long. Good
    labels make it easier to find specific points while troubleshooting later on.
• Match the data from the desired columns/rows in OnPing to the respective columns/rows in the Template .xls file
  • Notice, in Example 2 each set has 4 numbers or references to data. These will be used to retrieve and format our .xls file.

**Each set accounts for one row/column pairing. Iterate through this process for each pairing on the sheet. Once complete, you will have a map for 1 Sheet. Each Sheet in the file will need its own map**

We can combine the sets on one page to make a ‘map’ of the Sheet. Combine the maps of each Sheet to create a ‘Master Map’ of the full .xls Template file.

How the Sets Work

As you can see in Example 2, there are four entries for each set.
Each entry will be associated with a column/row value from either OnPing
or the Template:

• First Entry: OnPing Column
• Second Entry: OnPingRow
• Third Entry: Template Column
• Fourth Entry: Template Row

For help finding the location of your data row/column, open OnPing and navigate to the table you want data from. Then:

• Click on the Wrench to open ‘Editing Mode’ for the table
• Click “Edit Table Config”
• ctrl+F, and search for the label of your desired column/row.
• Your data should come with a column/row number that makes it easier to find in massive amounts of data.

Recall, the last 2 numbers of each set are associated with the row and column value from our Template file. Unlike the Onping data table, Excel starts with 1 rather than 0. Keep that in mind as we create the
map between the Onping and Excel tables.

Example 2

--[Insert column/row name here]
in
let [Insert a proper sheet name here] = [ 
tft  [Onping column #] [Onping row #] [Template column #] [Template row #]   ,               
tft  ....  ,
.... ,
tft  .... ,
tft  ....

 ] : List CellTransform
 

After completing the DHALL map convert the data into sheet form using concat. To do so, simply copy and paste the code from Example 3 into your program.

Remember, you need a DHALL map for each sheet in the
Template! The resulting ‘master map’ will include the maps for each
sheet

Change the sheet “unSheetName” to the appropriate name based on the sheet’s name in the template that you are putting this sheet into.

Example 3

in
let [Name of your sheet within the code] =     {sheetName = {unSheetName = "[Name of the sheet in the template file]"}, 
  pairTransforms = concat CellTransform [ [Name of your Cell Transformations] ,
                                          ... ,
                                          ...

                                        ]}
                                        

Then you will want to output them all together as one final list

Example 4

in [ [Name of your sheets within the code],
 ...,
 ...]

After Creating the Map

You spent a lot of time making maps. Now, save your work!

• Save the files you’ve been working on
  • Template file (.xls)
  • Dhall file (.Dhall)
• Open command line editor
• Go to the location of your DHALL file
• Create a resolved version of the DHALL file
  • Use the code in example 5 to create this
• You will use the resolved file for the transfer process

You must run the command from Example 5 in an Ubuntu or
GitBash enabled environment

Example 5

dhall resolve <<< ./[Name of Dhall file].dhall > resolved-[Name of Dhall file].dhall

With that, you have both your template and Resolved Dhall file ready for your transfer

3. Transferring data table #

Hop onto Onping and find the event table that you wish to transfer to
excel.

• Click on the wrench icon to open widget editting mode.
• Click “Spot Report”
• Click “Add New
  Report”
• Scroll to the bottom and click “Custom Formatting”
• For the Template File, you’ll want to browse and find your template file aka the
  Excel file and put it in. You’ll want to hit “Upload” as well.
• For the Dhall File, browse your computer for the resolve dhall file that we made
  earlier and put it in. Again you’ll be wanting to hit “Upload” as well.
• Finally, hit the “Test Report” button and wait a bit.

If all is working, then you should get no errors. Eventually, a new Excel file will be returned. This file will be filled in according to the map you created earlier.

• Look over the sheet to ensure there are no blank spaces and everything is filled in correctly.