Column Definition Language

The Shift and Worker Data feeds, include a powerful Column definition language which allows you to read any possible file columns.

Using Excel XLSX files #

You can upload Excel XLSX files as well as CSV. The only consideration is that any cells formatted as dates are unfortunately stored in Excel as date-time rather than just date – changing the displayed version in Excel will make no difference. If you use a DateTime as the {Date} input, paiyroll® will automatically strip the time and use the data only. If you use a Date in Display as input, you will see a date-time e.g. 2023-08-01 00:00:00 when you actually prefer a date: 2023-08-01

There are 2 solutions: Either format the date column to YYYY-MM-DD and save the XSLX as CSV UTF-8 which means Excel will output a date only in ISO format e.g. 2024-07-16.

Use the transformation operator (see below) to reformat the cells.

Transformation Operator -> #

The Column definition language includes a powerful transformation operator -> that lets you perform code-like changes to the data.

Below is an example of how the operator works. Notice how the Display as left-hand part before the -> and the right-hand part after the operator:

 "Display as": "{Date}{Job}{Hours}{Rate} -> '{0} {1}  {2}hrs @{3}'.format(_1[:10], _2, _3, _4)",

The left-hand part simply includes all the columns that are needed {Date}{Job}{Hours}{Rate}. These 4 columns are available on the right-hand side in the same order as _1 _2 _3 and _4. In this example, {Date} is _1 because it is the first column. Using the transformation means we can strip the first 10 characters of the date with [:10] so we get 2023-08-01 instead of 2023-08-01 00:00:00

Also on the right-hand side you can enter spaces and other characters to arrive at whatever final formatting you prefer. In this example, the values created on the Shift Pay Items are shown below:

Minimum Wage Pay Template #

if you’re using this template and wish to display the Rate used in the Display as input, you will need to 4 Curly brackets {{{Rate}}} as in the example below:

This will result in just a single curly bracket {Rate} entry in the Display as input after the data feed has run:

---

Column example (complex) #

In this example, the mini-language is a little more complex than usual as we wish to pick out and format the day and month from an Excel date-time 2024-09-08 12:43:34 column:

Explanation #

  "Works id": "{WorkerId}",
  "Worker name": "{WorkerName}",
  "Rate":  "{HiringRate}",
  "Units": "{TotalHours}",
  "Display as": "{Date}{TotalHours}{HiringRate}{ClientName}{RoleName} ->'{0}/{1} {2}hr x {3} {4}-{5}  '.format(_1[8:10], _1[5:7], _2, _3, _4, _5)",
  "Date": "{Date}"

The example has a simple mapping for all columns except the Display as column.

Transform -> #

The -> transform takes all the “Display as” columns on the left of the -> and uses variables _1 , _2 etc. to format them with an expression on the right. In this case, the following occurs:

1. {Date} is an Excel date time e.g. 2024-09-08 12:43:34 and we want to select the day and month only. _1[8:10] meaning characters 8 to 10 are day characters and _1[5:7] is the month (characters 5 to 7)
2. Each of the columns {Date}{TotalHours}{HiringRate}{ClientName}{RoleName} become numbered variables _1, _2 etc. which means {Date} is _1, {TotalHours} is _2 {HiringRate} is _3, {ClientName} is _4 and {RoleName} is _5
3. The final format string for Display as is {0}/{1} {2}hr x {3} {4}-{5} means {0} is the day, {1) is the month separated by a /, {2} is the total hours, {3) is the rate and {4} & {5} being the {ClientName} and {RoleName}