List of read options

The read statement in Envision offers the possibility to load one or more files into a table. Below, we gather the different options relating to the read statement.

Syntax overview

The usual syntax for reading a file in Envision is as follows:

read "/sample/Lokad_Orders.tsv" as Orders with
  Id : text
  Date : date
  "Quantity" as Qty : number

The text that comes just after the read keyword is referred to as the path. The path is intended to match the files that exist in the file storage associated with a given Lokad account. The columns are not required to be explicitly listed after the with keyword. In this case, the columns are available within the Envision script as long as the column names are valid variable names (for example, no spaces allowed in the column name). The primary purpose of the keyword as is to rename both tables and columns.

If the as option that follows the read is omitted, then Envision reads this file as part of the Items table.

It is useful to remark that the file path of a read statement can contain text constants and, as it will be discussed in the following sections, also variables defined in a form tile or through the today() function:

channel := "WebSite"
read "/sample/Lokad_Orders-\{channel}.tsv" as Orders with
  Id : text
  Date : date
  "Quantity" as Qty : number

Reading multiple files

It is possible to read multiple files at once through the use of the wildcard (*) in the specified path. The wildcard can replace any string of characters, except those containing a /. It may be used in the file name as well as in a folder name, and may include the file format. An empty string would also match the wildcard. Finally, multiple wildcards within the same path are supported. As an example,

read "/sample/Lokad_Orders*.tsv" as Orders with
  Id : text
  Date : date
  "Quantity" as Qty : number

could match “/sample/Lokad_Orders-2018-07-25.csv.gz” as well as “/sample/Lokad_Orders.tsv”.

Filter one file among many

When reading files using a wildcard or another allowed variable in the file path, it is possible to select one or more files, using one of the syntaxes described in the following.

In order to restrict the set of files originally captured by the wildcard to a single file, the read statement supports 3 distinct modifiers:

In addition, Envision supports the possibility of reading a specified number of files. This can be expressed by adding a literal positive integer just after the wildcard filter (e.g. max 3).

Using the above modifiers, filtering can hence be done as follows:

read max 3 "/sample/Lokad_Orders*.gz" as Orders with
  Id : text
  Date : date
  "Quantity" as Qty : number

In a list of files containing a date in their path, a file or subset of files can be selected everyday using a sliding temporal window defined through the today() function, as in:

todayDate := today(+1) - 30
fileDate := "\{todayDate:yyyy-MM-dd}"
read "/clean/StockHistory-\{fileDate}-*.ion" as Stock[*]

Finally, the file(s) to be read can be selected manually by the user, through a form tile displayed in the dashboard:

show form "Please select a country" with as "Country"

read "/sample/Lokad_Orders-\{}-*.tsv" as Orders with
  Id : text
  Date : date
  "Quantity" as Qty : number

Filter many files among many

In order to restrict the set of files originally captured by the wildcard to a single set of files that share the same folder, the read statement can use the percentage sign (%) - one at most - to indicate the folder match of interest.

read max "/%/Lokad_Orders*.gz" as Orders

The read statement will capture all the files that match the folder as restricted by the modifier. As a folder does not have a last updated date in Lokad, only the min and max modifiers are allowed when the percentage sign is present. Conversely, when the percentage sign is present, one read modifier should be present too.

Reading a single file as several ones

In addition, one can read a particular file, e.g., Lokad_Orders.tsv, as two distinct ones, e.g., Orders and NewOrders. In addition, various variables can be read.

read "/sample/Lokad_Orders.tsv" as Orders with
  Id : text
  Date : date
  Quantity : number
  NetAmount : number
  Client : text
  Currency : text
and as NewOrders with
  Id : text
  Date : date
  "Client" as CustomerID : text
  "Quantity" as QtyOrdered : number

Table-level options

Multiple options are supported in relation to the read statement:

The script below illustrates how the above options can be specified:

read "/sample/Lokad_Orders.tsv" skip:0 separator:"\t" quotes:false encoding:"UTF-8" headers:"Id, Date, Quantity" as Orders[*] with
  "Id" as Ref : text
  "Date" as OrderDate : date
  Quantity : number

Table type options

Envision offers the possibility to define the primary keys for a table, by listing them between parentheses right after specifying the table name. There are only 7 primary key combinations that are allowed:

read "a.csv" as A[*]
read "b.csv" as B[Id]
read "c.csv" as C[Id, *]
read "d.csv" as D[Date]
read "e.csv" as E[Date, *]
read "f.csv" as F[Id, Date]
read "g.csv" as G[Id, Date, *]

The table type option can be omitted. In this case, the default type applied is [Id, Date, *].

Column-level options

Multiple options are also supported for each column to be read:

The following script illustrates the usage of the column options:

read "/sample/Lokad_Orders.tsv" as Orders[Id,*] with
  date:"dd-MM-yyy" as OrderDate : date
  number:"1,000.00" as Qty : number
  const(true) as IsClosed

Specifying the columns is optional. Specifying a column’s data type is also optional, even if the column is specified. When data type is not specified with the read statement, the column data type is inferred from usage within the Envision script.

Distributions are an advanced data type that is only supported by Lokad’s Ionic file format.

Number options

The number format string imitates the actual desired output for the numbers:

Date options

The format string for the date option can be built from the following tokens:

Default values

When missing values are identified, the parser replaces empty values according to the declared data type of the column as follows: