Mass-Writes Guide

Mass-Writes

This guide is intended to provide a brief walk through for the mass importation of parameters over an FTP server using OnPing. It covers the use of mass writes, common terms within the system, and addresses the functional scope – such as search, formatting, etc – of the application.

Mass-Writes Usage

The Mass Writes feature is applicable to both spot reports and daily reports. Utilize the following formula when using Mass Writes:

mass-writes [name of remote file] [local key transform file name]

Fill out TemporaryServerConfig.dhall with the necessary information to connect to both the FTP server and OnPing. Below are some examples of what to look for.

Daily Report Import:

mass-writes "EXAMPLE Daily Export.csv" "./DailyKeyTransform.dhall"

Spot report import:

mass-writes "EXAMPLE Spot Export.csv" "./SpotKeyTransform.dhall"

Dhall format

Since mass writes now supports XLSX, we may need to supply a sheet name within the key transform file. The CLI is backwards compatible with prior formatting for users familiar and comfortable with the old format – and support an updated format:

Pre-XLSX Support Format Example:   

let tft =
\(csvR : Natural) ->
\(csvC : Natural) ->
\(pId : Natural) ->

{_1 = {csvRow = csvR, csvCol = csvC}, _2 = pID}

in
...

The updated format, referred to here as InputFormat, reserves space to specify the sheet name via the dataSheet key:

Input Format Example:

let tft =
\(csvR : Natural) ->
\(csvC : Natural) ->
\(pId : Natural) ->
{dataCell = csvRow = csvR, csvCol = csvC}
, dataSheet = Some "Sheet 1"
, dataPId = pId
, timeCell = None { csvRow : Natural, csvCol : Natural }
, timeFormat = None Text
}
in
...

Finding relative cells by Regex

You can also lookup the value of a cell with regexes:

Absolute:

< Absolute :
{dataCell : { csvRow : Natural, csvCol : Natural }
, dataSheet: Optional Text
, dataPid : Natural
, timeCell : Optional { csvRow : Natural, csvCol : Natural }
, timeFormat : Optional Text

Relative:

{origin : { regexesRow : List Text, regexesCol : List Text }
, relativeDataCell : { relativeRow : Integer, relativeCol : Integer }
, dataSheet : Optional Text
, dataPid : Natural
, relativeTimeCell :

Optional { relativeRow : Integer, relativeCol : Integer }
, timeFormat : Optional Text
}
>.Relative {
relativeDataCell = {relativeRow = +0, relativeCol = +1}
, origin = { regexesRow = ["a*","c.*"], regexesCol = ["data"]}
, dataPid = 15034318
, relativeTimeCell = None { relativeRow : Integer, relativeCol : Integer }
, timeFormat = None Text
, dataSheet = None Text
}]

Regex

The origin field has the type { regexesRow : List Text, regexesCol : List Text }. The program is taking every input regex in regexesRow and it tries to find which row match every regex. It does the same process with columns using regexesCol. Once it find both, it takes the resulting cell and add the relative value contained in relativeDataCell and relativeTimeCell to find where the actual data is.

Example Data

We will look at an example for reference. Below is a chart and some sample code:


origin = { regexesRow = ["Tank.*","Oklahoma"], regexesCol = ["data"]}
relativeDataCell = {relativeRow = +0, relativeCol = +0}
relativeTimeCell = Some {relativeRow = +0, relativeCol = +1}

Here, the only row that matches both Tank.* and Oklahoma is the 3rd row.

The only column that matches the data is the 3rd column. The origin cell is {row : 3, column : 3}, the cell with the value 21.

To find the actual data, we add the origin cell to the relativeDataCell, which gives us the cell {row : 3 + 0, column : 3 + 0}, so the data is 21.

To find the time cell, we add the origin cell to the relativeTimeCell, which gives us the cell {row : 3 + 0, column : 3 + 1}, so the time is 12/11/2021

Fields Description

Below is a list of fields along brief descriptions of their purpose in this feature:

  • Origin – Regexes to find the origin cell |
  • RelativeDataCell – Where is the data cell you’re looking for relative to the cell found using regexes. This may contain either positive or negative numbers |
  • DataSheet – optional excel sheet |
  • DataPID – PID of the entry |
  • RelativeTimeCell – Where is the time cell you’re looking for relative to the cell found using regexes. Can use positive and negative numbers |
  • TimeFormat – Time format of the time cell

Retro compatibility

Mass Writes is retrocompatible with older dhall formatting.

You can also mix the relative cells (new format) and the absolute cells (old format) in the same file using this format:

Absolute :

{ dataCell : { csvRow : Natural, csvCol : Natural }
, dataSheet: Optional Text
, dataPid : Natural
, timeCell : Optional { csvRow : Natural, csvCol : Natural }
, timeFormat : Optional Text
}

Relative and Absolute Combination :

{ origin : { regexesRow : List Text, regexesCol : List Text }
, relativeDataCell : { relativeRow : Integer, relativeCol : Integer }
, dataSheet : Optional Text
, dataPid : Natural
, relativeTimeCell :
     Optional { relativeRow : Integer, relativeCol : Integer }
, timeFormat : Optional Text
}>
Absolute {dataCell = {csvRow = 3, csvCol = 2}
, dataPid = 15034318
, timeCell = Some { csvRow = 4, csvCol = 1}
, timeFormat = Some "%Y-%m-%d %H:%M"
, dataSheet = None Text
}

Use the combination of cells smoothly transition between data and formatting. This feature minimizes road blocks in the work flow and makes the user experience more fluid.

Summary

The updated Mass Writes feature in OnPing is a powerful tool that utilizes several versatile tools to deliver a rich and intuitive user experience. These include

  • Regex Filtering for data
  • Retro-compatible formatting
  • Better access to reports
  • Natural language accessibility

Contact Us

Thank you for taking the time to learn more about the Mass Writes application in OnPing. If you have any questions, comments, or suggestions regarding Mass Writes then contact us at info@onping.net – we want to hear from you!