Click or drag to resize
[Output] section
Print this page

An [Output] section defines the final mapping of input and calculated values to output columns. For each name/value pair in this section:

  • Name portion (left side) declares the name of a column in the output and (if the data must be converted from the source type) its data type or units in parentheses.
    • For Create or Update templates, the data type is the data type and units of the input (text file).
    • For Retrieve templates, the data type is the data type to cast the value to for output.
  • Value portion (right side) is either an input column, a calculated variable, or a defined function.

For example, this converts the value stored in the input table's Distance field to miles and saves it to the Mileage column of the generated CSV file:

Mileage(miles) = Input.Distance

This topic contains the following sections:

Referencing external keys

You can both create and reference external keys in your [Output] section.

Suppose that you want to add a node to your Hierarchy table and use the external Id of "500". In your Create template, you tell TDE to use the myNodeId you provide as a NodeID:

Copy
[Output]
NodeId(myNodeIdName) = input.NodeId      # directs TDE to map your external Id to NodeId
NodeName = input.NodeName
ParentId = input.ParentId
Since TDE will not find any existing node with the myNodeId value, it will create it. The new node will have both an internal NodeId and the myNodeId that you created.

To refer to this external NodeId later, such as to add child nodes to it, you would import them this way:

Copy
[Output]
ParentId(myNodeIdName) = Input.ParentId    # input file specifies "500" in this column
For more information, see External keys.

Multiple Output sections

A template can include one or more output sections. If there is only a single output section, the section header is [Output]. If there are multiple output sections, the section headers include a number as well. For example [Output-1], [Output-2], and so on.

When the intent of the template is Create, Update, or CreateOrUpdate, then for each row of the input file, multiple rows are created or updated in the table, one for each Output section. Each row that is added or updated reflects the mappings declared in a different output section.

When the intent of the template is Retrieve, then the output file contains a separate output section for each of the Output sections in the template. That is, an entire template output section is included with data (and, typically, with column headers), followed by the next output section with data (and, typically, its own headers), and so on.

Example

Copy
[Output-1]
Id = Input.Id1
Tag = Input.Name1

[Output-2]
Id = Input.Id2
Tag = Input.Name2

This updates two rows of a table from a single row of the input file which contains two key values (Id1 and Id2) and two values to update (Name1 and Name2).

For more information, see Custom columns.
Additional syntax for XPath and JSON formats

On Create, Update, or CreateOrUpdate templates where the template section includes "Format=Xpath" or "Format=Json", you are able to use any valid XPath query to specify your data. See the Microsoft XPath Reference for more information on XPath.

Note Note

XPath, JSON, and XML queries are case sensitive! For example, Input.[my/xpath/query] is different from Input.[My/Xpath/Query].

Example

This template

Copy
[Template]
  TemplateVersion = 1.0
  TableID = Point-1.0
  TemplateName = JsonInputExample

  Intent = Create
  Format = json
  ElementName = data/points

  [Output]
  UnitId(MyId) = Input.[ancestor::data/id]
  Time = Input.[time]
  Lat(degree) = Input.lat
  Lon(degree) = Input.lon

and using this JSON

JSON
Copy
{"data":[
    {
    "id":"958610",
    "points":[
        {
            "time":"2011-10-05T18:45:09.000Z",
            "lat":"42.5369",
            "lon":"-83.0299",
            "ignition":"true"
        },{
            "time":"2011-10-05T18:46:09.000Z",
            "lat":"42.5369",
            "lon":"-83.0299",
            "ignition":"true"
        }]
    },{
    "id":"958611",
    "points":[
        {
            "time":"2011-10-05T18:45:09.000Z",
            "lat":"42.5369",
            "lon":"-83.0299",
            "ignition":"true"
        },{
            "time":"2011-10-05T18:46:09.000Z",
            "lat":"42.5369",
            "lon":"-83.0299",
            "ignition":"true"
        }]
    }]
}

or this template

Copy
[Template]
  TemplateVersion = 1.0
  TableID = Point-1.0
  TemplateName = XpathInputExample

  Intent = Create
  Format = Xpath
  ElementName = root/data/points

  [Output]
  UnitId(MyId) = Input.[ancestor::data/id]
  Time = Input.[time]
  Lat(degree) = Input.lat
  Lon(degree) = Input.lon

and this XPath input

Copy
<?xml version="1.0" encoding="UTF-8"?>
<root>
   <data>
      <id>958610</id>
      <points>
         <time>2011-10-05T18:45:09.000Z</time>
         <lat>42.5369</lat>
         <lon>-83.0299</lon>
         <ignition>true</ignition>
      </points>
      <points>
         <time>2011-10-05T18:46:09.000Z</time>
         <lat>42.5369</lat>
         <lon>-83.0299</lon>
         <ignition>true</ignition>
      </points>
   </data>
   <data>
      <id>958611</id>
      <points>
         <time>2011-10-05T18:45:09.000Z</time>
         <lat>42.5369</lat>
         <lon>-83.0299</lon>
         <ignition>true</ignition>
      </points>
      <points>
         <time>2011-10-05T18:46:09.000Z</time>
         <lat>42.5369</lat>
         <lon>-83.0299</lon>
         <ignition>true</ignition>
      </points>
   </data>
</root>
will create 4 points, two from unit 958610 and two from unit 958611.

The way myValue = Input.[my/Xpath/Query] works is it selects all of the nodes matching the query, and concatenates all of their text values in the node and any child nodes it has together. If no nodes match the query, an empty string will be returned. If you want to store the raw xml of a node, a RenderXml built-in function is provided.

Example

Using this Xml as an example
Copy
<a>
  <b>
    <c>1</c>
    <c>2</c>
  </b>
  <b>
    <c>3</c>
    <c>4</c>
  </b>
</a>
  • Input.[a/b/c] evaluates to "1234" because it selects all of the "c" nodes.
  • Input.[a/b/c[1]] evaluates to "13" as it selects only the first "c" node from each "b" node.
  • Input.[a/d] evaluates to "" as the "a" node has no "d" nodes as children.
  • RenderXml(Input.[a/b[1]/c[1]]) evaluates to "<c>1</c>" as it selects the raw XML of the first "c" node of the first "b" node.

On Retrieve templates where the template section includes "Format=Xpath", you have more options when specifying the syntax for a field value that you are exporting:

  • You can prefix an @ symbol to the name portion to indicate that it should be output as an attribute rather than as a node. Using the @ symbol for any other output format or for any template other than a retrieve template will generate a syntax error.
  • You can prefix the name portion with the name of a node and a forward slash ("/") to indicate that the output contains a node with the specified name that includes the specified value as a subnode or attribute.

Example

Copy
@Radius(feet) = Input.Radius

In a Retrieve template with "Format=Xpath", this causes the radius to be included as an attribute on the node for a row rather than as a child node of the row.

Example

Copy
Where/Lat (degree) = Input.Lat
Where/Lon (DDDMMSS) = Input.Lon

In a Retrieve template with "Format=Xpath", this causes the output to contain a node called "Where" that has subnodes for Lat and Lon, such as:

Copy
<Where>
<Lat>33.9027618707873</Lat>
<Lon>1182209</Lon>
</Where>

In a Retrieve template, all values by default have any XML entities escaped. If you want to override this, a ParseXml built-in function is provided.

Example

Using this Xml as an example,
Copy
<a>
  <b>
    <c>1</c>
    <c>2</c>
  </b>
  <b>
    <c>3</c>
    <c>4</c>
  </b>
</a>
Using this Xml as an example, suppose you stored some raw XML in some column.
Copy
myColumn = RenderXml(Input.[a/b[1]/c[1]])
If you later want to retrieve that data:

  • The following line

    Copy
    myOutputColumn = Input.myColumn

    results in your output file containing this XML node:

    Copy
    <myOutputColumn>&lt;c&gt;1&lt;/c&gt;</myOutputColumn>

  • On the other hand

    Copy
    myOutputColumn = ParseXml(Input.myColumn)

    results in your output file containing this XML node:

    Copy
    <c>1</c>

    as it parses the value of Input.myColumn into XML and inserts the xml directly into your output.

  • Finally,

    Copy
    my/output/path = ParseXml(Input.myColumn)

    results in your output file containing this XML node:

    Copy
    <my><output><c>1</c></output></my>

    It inserts the parsed xml at the path specified.

Custom Data

Each row of a can be augmented to store custom data. This is done by prefixing the name of the table column with the string "custom". This is illustrated in the following examples:

In a Create or Update template, this line stores the value in the "mycases" column of the input as a custom column in the table named "cases" and "special cases," respectively:

Copy
custom.mycases = Input.cases
custom.[special cases] = Input.cases    # Use brackets for column names with special characters

In a Retrieve template, this retrieves the custom value shown in the previous example from the table:

Copy
MyCases = custom.cases
MyCases = custom.[special cases]

For more information, see Custom columns.

Using the DeleteEntry command

The [Output] section has a DeleteEntry flag available, which when set to true, deletes the current row being processed within the template. It can be used within Update and CreateOrUpdate templates for any table that supports Delete operations.

The best use for the DeleteEntry flag is if you want to create a single template that determines whether a row is created, updated, or deleted. This can be accomplished using a combination of a CreateOrUpdate template, the DeleteEntry flag, and a conditional operator, as shown in the example below.

Copy
[Template]
TemplateVersion = 1.0
TableID = Marker-1.0
TemplateName = CreateUpdateOrDeleteMarkers
Intent = CreateOrUpdate
Format = CSV

[Output]
Id(Sys1) = Input.Sys1
Tag = Input.Tag
EnvelopeAddress = Input.EnvelopeAddress
Country = Input.Country
Radius(ft) = Input.Radius
# Sets DeleteEntry to true if the Status Code is X; otherwise, DeleteEntry is set to false
DeleteEntry = (Input.[Status Code] == "X") ? "True" : "False"

If you were to run the template above for the input file shown below, Marker1 would be deleted, because its Status Code is X. While Marker2 would either be created or updated, because its Status Code is A.

Copy
Tag,Sys1,EnvelopeAddress,Country,Radius,Status Code
Marker1,97645,"10801 North MoPac Expressway,AUSTIN,TX,78759","USA","1500",X
Marker2,55661,"20 Enterprise,Aliso Viejo,CA,92656","USA","2050",A
Setting time zone and units of measure from an input file

In Create or Update templates, a column’s unit of measure (for any measurement Data types) and time zone can be set from data within your input file, so that you change these values as needed when executing a template.

Note Note

If you specify a time zone or unit of measure using this method, you must provide a value for each input line.

Use this method, for example, if you want to insert fuel transactions that 1) have a PurchaseTime in different time zones and 2) use different units for FuelVolume.

Copy
[Template]
TemplateVersion = 1.0
TableID = FuelTransaction-1.0
TemplateName = CreateFuelTransaction
Intent = Create

[Output]
UnitId(VehicleIdMap) = Input.VehicleName
PurchaseTime(M/dd/yyyy H:mm:ss,Input.PurchaseTimeZone) = Input.PurchaseTime   # dynamically sets time zone by input value
TransactionType= Input.TransactionType
DriverName= Input.DriverName
AccountNumber = Input.Account
CardNumber= Input.CardNumber
Latitude(Angle,Degree) = Input.Latitude
Longitude(Angle,Degree) = Input.Longitude
FuelVolume(Volume,Input.FuelUnits) = Input.FuelVolume   # dynamically sets Volume units by input value
PurchaseAmount = Input.PurchaseAmount
FuelType = Input.FuelType

The input file for this template includes the values to set for the TimeZoneByInput and UnitByInput variables.

Copy
PurchaseTime,PurchaseTimeZone,VehicleName,TransactionType,Account,CardNumber,DriverName,Latitude,Longitude,FuelVolume,FuelUnits,PurchaseAmount,FuelType
9/15/2015 1:05:00,CST,Vehicle_1,Auth,12345,****1234,Bob Lopez,30.3976004,-97.7295845,1.5,US gallon,3.25,Unleaded
9/15/2015 7:10:00,MST,Vehicle_2,Sale,12345,****1234,Craig Davidson,28.6391905,-106.0732948,5.67,liter,8.35,Unleaded
Ignoring input values

If you need to selectively ignore values in an input file provided for a Create, Update, or CreateOrUpdate template, you can accomplish that using the Ignore token and an if-then-else ternary operator in the [Output] section. This method works for all input formats.

For example, consider the following scenario. Let’s say you work at a company where some drivers are contractually obligated to always drive the same truck, and you want to make sure their vehicles are never updated by accident. You could accomplish that using the example input file and template described below.

For each driver, the input file contains an AlwaysDrivesSameTruck value that indicates whether the driver always drives the same truck. The value is either set to true or false.

Input file
Copy
EmployeeNumber,VehicleId,AlwaysDrivesSameTruck
111,222,false
333,444,true

The template then uses the AlwaysDrivesSameTruck value to determine whether the VehicleId provided in the input file should be accepted or ignored:

  • If AlwaysDrivesSameTruck is set to true, then the VehicleId is ignored.
  • If AlwaysDrivesSameTruck is set to false, then the VehicleId is updated with the value provided.
Update template
Copy
[Template]
TemplateVersion = 1.0
TableID = Driver-1.0
TemplateName = UpdateDriverVehicle
Intent = Update
Format = CSV

[Output]
Id(Driver) = Input.EmployeeNumber
VehicleId = Input.AlwaysDrivesSameTruck == “true” ? IGNORE : Input.VehicleId
Note Note

You can also use the IgnoreValue entry in the [Template] section to ignore specific strings or empty input fields.