Additional validations can be added to most formats, as illustrated in the code below:

  file_definition_by_name["sales"] = fd,
  file_definition(fd) {
    file_delimiter[] = "|",
    column_headers[] = "SKU,SALES",
    column_formats[] = "string([a-zA-Z0-9]*),float(>=0; <=20; precision 2)"
  }.

In the example above, the string column defines a regular expression [a-zA-Z0-9]*, indicating it only accepts alphanumeric characters. We also have a float column defining three validations, separated by semicolon:

The following table lists currently available validations:

  file_definition_by_name["sales"] = fd,
  file_definition(fd) {
    file_delimiter[] = "|",
    column_headers[] = "SKU,SALES",
    column_formats[] = "string([a-zA-Z0-9]*),float"
  }.

  file_definition_by_name["sales"] = fd,
  file_definition(fd) {
    file_delimiter[] = "|",
    column_headers[] = "SKU,SALES",
    column_formats[] = "string(exp1),float",
    format_regex["exp1"] = "[a-zA-Z]{0,9}"
  }.

TDX provides support for dealing with missing values, such as the following file:

SKU     | STORE       | WEEK | YEAR | SALES
apples  | atlanta     | W1   | 2012 |
oranges | atlanta     | W2   | 2012 | 15
apples  | portland    | W1   | 2012 |
oranges | portland    | W2   | 2012 | 5

In TDX, a column can be "required", "optional", or "possibly absent". If a column is required, this means that it must be present in the file, and every row must specify a value for that column. If a column is optional, this means that it must be present in the file, but some rows may have a missing value for that column. If a column is allowed to be absent, this means that missing values are allowed, and, furthermore, the entire column may be absent from the file on imports.

By default, all columns mentioned in the file definition are required columns. To change this, we can use the file_columns_required or the file_columns_optional predicate, as shown below. When the file_columns_required predicate is used, all columns not specified in that predicate are treated as optional columns. When the file_columns_optional predicate is used, all columns not specified in that predicate are treated as required columns.

file_definition_by_name["sales"] = fd,
file_definition(fd) {
  file_delimiter[] = "|",
  column_headers[] = "SKU,STORE,WEEK,SALES",
  column_formats[] = "alphanum,alphanum,alphanum,integer",
  file_columns_required[] = "SKU,STORE,WEEK"
}.

Possibly absent columns are specified using file_columns_can_be_absent, as in the following example:

file_definition_by_name["sales-returns"] = fd,
file_definition(fd) {
  file_delimiter[] = "|",
  column_headers[] = "SKU,STORE,WEEK,SALES,RETURNS",
  column_formats[] = "alphanum,alphanum,alphanum,integer",
  file_columns_can_be_absent[] = "SALES,RETURNS"
}.

In this example, the SALES and RETURNS columns may or may not be present in the file. If they are present, then, in any given row, a value may but need not be given for each of these columns.

TDX allows the customization of how data values are quoted and escaped in import and export services. The file mode specification consists of a string with zero or more of the following entries.

Escaping is applied upon export, and unescaping upon import. In the unix style, escaping is done by prefixing the quote character, line-feed and carriage-return with a backslash (e.g., "foo"bar" is replaced by "foo\"bar"). The backslashes are removed on imports. In excel mode, escaping is done by doubling the quote character (e.g., "foo""bar").

The default file mode is unix quote=" which means that by default Unix escaping rules are used, and values are enclosed in double quotes. That is, imports require values to be either not quoted or quoted with ", and exports always quote with ". For example, a file with a single string column named X behaves like the following by default:

// import
X
foo
"bar"

// export
X
"foo"
"bar"

This behavior can be changed statically with the file_mode attribute. For example, the following definition will export the file with ' as the quote character.

file_definition_by_name[name] = fd,
file_definition(fd) {
  file_delimiter[] = "|",
  file_mode[] = "quote='",
  column_headers[] = "X",
  column_formats[] = "string"
}.

This allows the following interaction (note that double quotes are now simply part of the record):

// import
X
foo
"bar"
'baz'

// export
X
'foo'
'"bar"'
'baz'

By setting the file_mode to raw, no quotes are added on export, and import values are treated completely as part of the record:

// import
X
foo
"bar"
'baz'

// export
X
foo
"bar"
'baz'

Finally, it is possible to override the static default by using a URL parameter to the TDX service:

// POST to /file?tdx_file_mode="raw"
X
foo
"bar"
'baz'

// GET to /file?tdx_file_mode="quote=*"
X
*foo*
*"bar"*
*'baz'*

When importing a delimited file, it is often necessary or convenient to combine column values, or to perform a modification of column values prior to inserting them into predicates in a workspace. Similar manipulations may also be called for when exporting to a delimited file. TDX allows developers to specify column binding transform functions to be applied during imports and/or exports.

In the following we will introduce transform functions by means of examples. Suppose that we want to import this simple sales file and that the application model is the one shown in the following listing.

SKU     | STORE       | WEEK | YEAR | SALES
apples  | atlanta     | W1   | 2012 | 10
oranges | atlanta     | W2   | 2012 | 15
apples  | portland    | W1   | 2012 | 20
oranges | portland    | W2   | 2012 | 5

sku(x),   sku_id(x:id)   -> string(id).
store(x), store_id(x:id) -> string(id).
week(x),  week_id(x:id)  -> string(id).

sales[sku, store, week] = sales -> sku(sku), store(store), week(week), integer(sales).

Now suppose that there are small mismatches between the file format and data, and the schema of our application. For example, we may want to have names of all stores in upper case characters. One possible way to transform the import file is to bind the STORE column to the refmode of sample:sales:store entities, and apply the string:upper function upon imports. This can be specified with the following file binding:

...
file_binding_by_name["sales"] = fb,
file_binding(fb) {
  file_binding_definition_name[] = "sales",
  file_binding_entity_creation[] = "accumulate",
  predicate_binding_by_name["sample:sales:store"] =
    predicate_binding(_) {
      predicate_binding_columns[] = "STORE",
      column_binding_by_arg[0] =
        column_binding(_) {
          column_binding_import_function[] = "string:upper"
        }
    }
}.
...

Note that import functions bind the values from columns to their keys, and produce a value that is then stored in the predicate being bound. This binding is thus roughly equivalent to applying string:upper[STORE] = id and then using id as the refmode of a store entity.

This file binding would create stores, but we are also interested in creating sales values. The problem now is that in the workspace weeks are identified by a concatenation of year and week id (for example, '2012-W1') The solution is to define a concat_dash function that concatenates two strings with a dash ('-'). Then, we apply this function to the values of the YEAR and WEEK columns, and use the resulting value as the refmode of the week-of-year entity:

concat_dash[s1, s2] = s1 + "-" + s2.
lang:derivationType[`concat_dash]="Derived".

...
file_binding_by_name["sales"] = fb,
file_binding(fb) {
  file_binding_definition_name[] = "sales",
  file_binding_entity_creation[] = "accumulate",
  predicate_binding_by_name["sample:sales:sales"] =
    predicate_binding(_) {
      predicate_binding_columns[] = "SKU, STORE, YEAR;WEEK, SALES",
      column_binding_by_arg[2] =
        column_binding(_) {
          column_binding_import_function[] = "sample:sales:services:concat_dash"
        },
      column_binding_by_arg[1] =
        column_binding(_) {
          column_binding_import_function[] = "string:upper"
        }
    }
}.
...

The above file binding will concatenate YEAR and WEEK as well as apply string:upper to the STORE column. Note that column_binding_by_arg is indexed by the components in predicate_binding_columns, which are separated by commas (,). If a component contains multiple columns, because the function contains multiple arguments, they are separated by semicolons (;). Also note that any function can be used, including built-ins (such as string:upper), derived and materialized functions.

This file binding supports importing files but it would fail for exports. The problem is that we need to specify the inverse of concat_dash, that is, how to decompose the week-of-year entity refmode to export WEEK and YEAR columns. But before attending to this issue, let us see how we could extend the binding to export the names of store entities in lower case. This is accomplished by using the inverse export function, string:lower

    ...
      column_binding_by_arg[1] =
        column_binding(_) {
          column_binding_import_function[] = "string:upper",
          column_binding_export_function[] = "string:lower"
        }
...

Note that export functions are applied with respect to the predicate being bound. This binding is thus roughly equivalent to applying store_id(x:id), string:lower[id] = STORE. This also means that we need inverse export functions when exporting to multiple columns, as in the case of WEEK and YEAR, because we will have to bind the entity refmode to the value of the function, and the multiple columns to the keys. So to implement our requirement, we define a split_dash function that splits a string into two strings by the '-' character, and then apply this function as an inverse to the binding. The resulting code for our example is the following:

concat_dash[s1, s2] = s1 + "-" + s2.
lang:derivationType[`concat_dash]="Derived".

split[s1, s2] = s -> string(s1), string(s2), string(s).
split[s1, s2] = s <-
  string:split[s, '-', 0] = s1,
  string:split[s, '-', 1] = s2.
lang:derivationType[`split2]="Derived".

...
file_binding_by_name["sales"] = fb,
file_binding(fb) {
  file_binding_definition_name[] = "sales",
  file_binding_entity_creation[] = "accumulate",
  predicate_binding_by_name["sample:sales:sales"] =
    predicate_binding(_) {
      predicate_binding_columns[] = "SKU, STORE, YEAR;WEEK, SALES",
      column_binding_by_arg[2] =
        column_binding(_) {
          column_binding_import_function[] = "sample:sales:services:concat_dash",
          column_binding_export_function_inverse[] = "sample:sales:services:split_dash"
        },
      column_binding_by_arg[1] =
        column_binding(_) {
          column_binding_import_function[] = "string:upper",
          column_binding_export_function[] = "string:lower"
        }
    }
}.
...

Adding alias_all(`lb:web:delim:tdx_helpers) to the service will expose the helper predicates below to use for TDX specification:

The example below illustrates a file definition using TDX helpers.

block(`product) {

  alias_all(`lb:web:delim:tdx_helpers),
...

  clauses(`{
...

  fb(fb),
  file_binding_by_name[product_file_definition_name[]] = fb,
  file_binding(fb) {
    file_binding_definition_name[] = product_file_definition_name[],
    accumulate_entity("SKU_NBR"),
    binds_pred["product:sku:label"] = "SKU_NBR,SKU_DESC",
    binds_pred["product:sku"] = "SKU_NBR",
    binds_pred["product:sku:imported"] = "SKU_NBR",
    export_only("product:sku"),
    import_only("product:sku:imported"),

  }.

  })
} <-- . 

Exports involving optional columns are a bit more complicated. For each row in such an export, all required columns have actual values, while each optional column can have an actual or missing. Moreover, TDX configurations with optional columns are only valid if each exported column is properly bound, i.e.:

In the most common case, the proper binding of each optional column is guaranteed by a predicate binding to a functional predicate for which all columns bound to its keys are required. However, the second part of the definition above also allows for an optional column C to be bound to the value of a functional predicate F for which some of the keys are bound to other optional columns. In both cases, the set of rows to be exported is computed iteratively as follows:

At the end of this process, all columns are known, and the resulting rows in P_K, each of which contains an actual or missing value for each of the columns, are exported to the CSV file. We illustrate this process through an example below:

sales[sku,week] = sales -> product:sku(sku), time:week(week), int(sales).
returns[sku,week] = returns -> product:sku(sku), time:week(week), int(returns).
...
file_definition_by_name["sales_returns"] = fd,
file_definition(fd) {
  column_headers[] = "SKU, WEEK, SALES, RETURNS",
  column_formats[] = "alphanum, alphanum, int, int",
  file_columns_optional[] = "RETURNS"
},

file_binding_by_name["sales_returns"] = fb,
file_binding(fb) {
  file_binding_definition_name[] = "sales_returns",
  predicate_binding_by_name["measure:sales"] =
    predicate_binding(_) {
      predicate_binding_columns[] = "SKU, WEEK, SALES",
    },
  predicate_binding_by_name["measure:returns"] =
    predicate_binding(_) {
      predicate_binding_columns[] = "SKU, WEEK, RETURNS",
    }
}.

In the TDX configuration above, the columns SKU, WEEK and SALES are properly bound because they appear in a predicate binding (to the predicate measure:sales) in which all columns are required. The optional column RETURNS is properly bound because it is bound to the value of the functional predicate measure:returns, and the columns SKU, WEEK which are bound to the keys of this predicate are properly bound.

The result of this export contains one row for each SKU,WEEK pair appearing in the measure:sales predicate, together with the corresponding value from that predicate for the SALES. For each such row, if the SKU,WEEK pair also appears in measure:returns, the RETURNS column of this row contains the corresponding value from this predicate, otherwise it contains an empty string (or the default export value for RETURNS). This export essentially corresponds to the left-outer-join of measure:sales with measure:returns.

Suppose now we want the export to include all SKU,WEEK pairs for which at least one of the measure:sales and measure:returns predicates has a value. In contrast to the left-outer-join described above, this corresponds to the full-outer-join of the two predicates. To export this full-outer-join, the first step would be to mark both the SALES and RETURNS columns as optional. However, the resulting TDX configuration would be invalid: even though the SALES and RETURNS optional columns are bound to values of functional predicates, the keys of those predicates are bound to the SKU and WEEK columns which do not appear in any predicate binding for which all columns are required.

A simple way to make this TDX configuration valid would be to add, for each of these columns, a new predicate binding in which all columns are required, e.g.:

  predicate_binding_by_name["product:sku"] =
    predicate_binding(_) {
      predicate_binding_columns[] = "SKU",
  predicate_binding_by_name["time:week"] =
    predicate_binding(_) {
      predicate_binding_columns[] = "WEEK",
    }

The result of this export contains one row for each SKU,WEEK pair from the cross-product of product:sku and time:week. For each such pair, the SALES and RETURNS columns contain the corresponding values from measure:sales and measure:returns, respectively, or the empty string or other default export value, if the SKU,WEEK pair does not appear in the corresponding predicate.

However, this may include a lot of SKU,WEEK pairs for which both of the SALES and RETURNS columns have missing values (such tuples also do not belong to the full-outer-join of the two predicates). Thus, to export exactly the contents of the full-outer-join we must ensure that the predicate(s) we use to properly bind the SKU and WEEK columns contain only pairs for which at least one of the predicates has a value. For instance, we can define such a predicate using the following LogiQL rule:

keysToExport(sku,week) <- 
   sales[sku,week] = _ ; returns[sku,week] = _.

We can then use this predicate to bind both of the SKU,WEEK columns, instead of binding them to product:sku and time:week, respectively:

  predicate_binding_by_name["keysToExport"] =
    predicate_binding(_) {
      predicate_binding_columns[] = "SKU, WEEK"
    }

The resulting export is also likely to be more efficient, as it avoids creating partial rows and performing lookups for a (potentially large) number of SKU,WEEK pairs that do not appear in either of the measure:sales or measure:returns predicates.

If the measure:sales and measure:returns predicates in the example of the previous section are default-valued predicates, the full-outer-join of their parts that have non-default values could also be exported using a TDX configuration where both the SALES and RETURNS columns are marked as required. In this case, no additional predicate bindings are needed to ensure that all columns are properly bound, and the export computes the (inner) join of the two predicates. Because default-valued predicates have a value for all possible intersections in their key space, the result of this join may contain SKU,WEEK pairs for which one or both of the predicates have the default value. For exports involving default-valued predicates, TDX does some additional filtering to avoid exporting rows for which all columns that are bound to the values of such predicates have the default value. Thus, such a TDX export produces the set of rows in the full-outer-join described in the previous section (the only difference being that in this case missing values are represented by the default value of the corresponding predicate, instead of the empty string or other default export value that was produced when using optional columns).

However, using a combination of required columns with default-valued predicates to export the full-outer-join of these predicates is likely to be inefficient because, as explained above, it essentially involves computing the join over the whole key space of these default-valued predicates, which is typically much larger than the set of intersections for which some of these predicates have non-default values. A better alternative, similar to the one described for non-default-valued predicates in the previous section, would be to:

For our running example, assuming the default value for measure:sales and measure:returns is 0, such a predicate can be defined using a LogiQL rule along the lines of:

keysToExport(sku,week) <- 
   sales[sku,week] != 0 ; returns[sku,week] != 0.

The SKU and WEEK columns can then be bound to the keys of the keysToExport predicate, to ensure that:

Records that failed to import are reported back to the user. The format of the report is the same as in the import file, including headers, plus two additional columns that indicate the error (see the section called “Causes”). If no records failed to import, the server returns a file with only the headers. This feature is enabled by default.

Returning error records does have a small performance penalty as the server must write and then return the file containing the bad records. If for some reason you wish to disable the feature, you can specify the --ignore-bad-records flag on lb web-client or simply do not specify an output_file or output_url in the batch. See the section called “Accessing via HTTP” for how to disable when accessing via HTTP.