Writing files

Table of contents

The export option

Writing files in Envision is done through the table tile and the export option. The table tile is the only tile that allows its content to be persisted as a file. Let’s revisit one script that we introduced previously:

table Products = with
  [| as Product, as Price |]
  [| "shirt", 10.50 |]
  [| "pants", 15.00 |]
  [| "hat", 5.25 |]

table Goods = with
  [| as Good, as Price |]
  [| "t-shirt", 4.25 |]
  [| "bandana", 3.75 |]

show table "Products" a1b3 export: "/sample/products.csv" with
  Products.Product
  Products.Price

show table "Products" c1d3 export: "/sample/goods.csv" with
  Goods.Good
  Goods.Price

The above script results in the production of the two files products.csv and goods.csv. The export option must be positioned after the tile title and after the tile position (if present).

In Envision, file writes are atomic. From the perspective of all the other Envision scripts, and even from the perspective of SFTP/FTPS clients, the output files are absent until they are present in their final form. The Lokad platform prevents any third party from accessing the files while writing is in progress.

It is possible to control the export’s file format by picking the right file name extension. For example, a Microsoft Excel spreadsheet can be produced with:

table Products = with
  [| as Product, as Price |]
  [| "shirt", 10.50 |]
  [| "pants", 15.00 |]
  [| "hat", 5.25 |]

show table "Products" a1b3 export: "/sample/products.xlsx" with
  Products.Product
  Products.Price

In the above script, the path for the exported file ends with .xlsx, and as a result, a spreadsheet is created instead of a flat text file.

The Lokad platform supports exporting data to a short series of file formats:

Attempting to export a file with an unknown filename extension results in a runtime error.

While all our examples so far are leveraging text literals for export paths, the value passed to the export attribute can be an arbitrary scalar text expression, as illustrated by:

table Products = with
  [| as Product, as Price |]
  [| "shirt", 10.50 |]
  [| "pants", 15.00 |]
  [| "hat", 5.25 |]

Products.Path = "/sample/products-\{Products.Product}.xlsx"

show table "Products" a1b3 export: min(Products.Path) with
  Products.Product
  Products.Price

The above script produces a file at the path /sample/products-hat.xlsx. While the above script does not really reflect a meaningful use case, this feature comes in handy for many situations. For example, when there are multiple files to be exported, it is possible to define a path for the destination folder, and then re-use this path in every export statement, as illustrated by:

exportPath = "/sample"

table Products = with
  [| as Product, as Price |]
  [| "shirt", 10.50 |]
  [| "pants", 15.00 |]
  [| "hat", 5.25 |]

show table "Products" a1b3 export: "\{exportPath}/products.xlsx" with
  Products.Product
  Products.Price

So far, when the export option was used, the table displayed in the dashboard and the table exported to the filesystem were essentially identical in their structure: same lines and same columns. However, it is possible to exclude a column from the file export.

table Products = with
  [| as Product, as Price |]
  [| "shirt", 10.50 |]
  [| "pants", 15.00 |]
  [| "hat", 5.25 |]

show table "Products" a1b3 export: "/sample/products.xlsx" with
  Products.Product
  Products.Price export: false

In the above script, the export option is used twice: first, at the tile level to indicate that the table should be exported, second, at the column level to indicate that this column should not be exported. In the dashboard, the table Products has two visible columns, Product and Price. In the filesystem, the file products.xlsx has only a single column Product.

The default value of the column-level export option is true. This option indicates whether the column should be part of the file export.

Advanced remark: Under the hood, the filesystem of Lokad behaves like a Git repository. All changes are both atomic and versionned.

Flat text file write options

When exporting data to a flat text file, compressed or not, Envision provides some capabilities to control the format of the resulting file.

The following script produces a CSV file without headers, with a US-style date format and using the dot as the decimal separator:

table Values = with
  [| as MyDate,         as MyNumber |]
  [| date(2020, 8, 4),  12          |]
  [| date(2020, 8, 4),  123.4       |]
  [| date(2020, 8, 4),  1234.56     |]

show table "My Values" a1c5 export:"/sample/values.csv" \
    omitHeaders:true date:"M/d/yy" number:"0.0" with
  Values.MyDate
  Values.MyNumber

This behavior is achieved through the options omitHeaders, date and number which can be listed in any order after the position of the tile. The resulting flat text file is the following:

8/4/20,12
8/4/20,123.4
8/4/20,1234.56

More generally, we have the following options:

| Option | Type | Default | Comment | |—————-|———|————–| | separator | text | N/A | Defines the separator to be used between cells. Default is comma for CSV files and tab for TSV files. | | omitHeaders | boolean | false | Indicates whether the column headers should be inserted as the first line of the file. | | date | text | “yyyy-MM-dd” | Format specifier to format dates. This option follows the same syntax as the one used for the date option on the read side. | | number | text | “0.0” | Format specifier to format numbers. This option follows the same syntax as the one used for the number option on the number side. |

These options are only available to flat text files because they do not make sense for strongly-typed formats like Ionic. Also, these options are only available for the table tile when the option export is present because they only influence the exported file.

Pack multiple spreadsheets in a workbook

Excel workbooks have the capability to contain multiple sheets. Envision takes advantage of this capability by allowing multiple tables to be exported in the same workbook, i.e. in the same Excel file.

The following script creates a single file named book.xlsx with two sheets respectively named My Products and My Variants:

table Products = with
  [| as Product, as Price |]
  [| "shirt", 10.50 |]
  [| "pants", 15.00 |]
  [| "hat", 5.25 |]

table Variants = with
  [| as Product, as Color |]
  [| "shirt", "white" |]
  [| "shirt", "pink" |]
  [| "pants", "blue" |]
  [| "pants", "black" |]
  [| "hat", "red" |]

show table "Sheet 1" a1c3 export:"/sample/book.xlsx{My Products}" with
  Products.Product
  Products.Price

show table "Sheet 2" d1f3 export:"/sample/book.xlsx{My Variants}" with
  Variants.Product
  Variants.Color

The destination sheet is specified via the export option by suffixing the sheet name between the curly brackets { and }.

In the workbook, the sheets are ordered according to their order of appearance within the Envision script. In the example above, My Products appears first because its matching tile statement appears prior to the other tile statement.