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"),

  }.

  })
} <-- . 

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.