Skip to content

Convert spreadsheets

The IBM Food Trust™ spreadsheet Converter API converts Microsoft® Excel™ files into XML format for upload via the Connector API. This tool enables you to easily generate valid IBM Food Trust messages from ExcelExcel™ files. This service converts content directly from a spreadsheet file to an IBM Food Trust message type in XML format. The template files also contain each XML field that is available in the message type.

Overview

Download a sample Excel™ spreadsheet (or a blank template) below, and review the worksheets and the required formats. Your input file to the Converter API may contain multiple worksheets—including one for facilities (locations) and one for products. The products and facilities worksheets describe your Master data, and provide a single source of attributes and values for reuse. The worksheets contain drop-down lists to select values that the Converter API requires.

Sample spreadsheets

Click any link to view or save the sample spreadsheet (.xlsx) file:

Blank spreadsheet templates

Click any link to save the blank spreadsheet template file; select Excel Workbook (.xslx) as the extension:

Spreadsheet documentation

The Excel™ spreadsheets below (.xlsx files) provide field-by-field details for each spreadsheet, including detailed field descriptions, required formats, examples, and requirements. Each table entry contains the collection of documentation files for each IBM Food Trust message type.

Note: Many of the spreadsheets contain a Level column, which represents groupings. Level 1 represents a new event or transaction. Anything above Level 1 represents an array. For example, the Aggregation description workbook contains both Level 1 and Level 2 groupings. To populate an Aggregation template workbook with one aggregation event, and three trade items, for example:

  1. First populate all required Level 1 cells in the first row. The first trade item description, quantity, and quantity UoM (unit of measure) cells (all Level 2) will be populated in the first row.
  2. To populate the second trade item, create a new row, do NOT populate the Level 1 cells, and populate the Level 2 trade item description, quantity, and quantity UoM cells.
  3. Create another row and populate the Level 2 cells for the third trade item.
  4. Adding a new row and populating the required Level 1 cells creates a new aggregation event.

You can view this Level usage in the sample spreadsheets above.

Excel™ spreadsheet fields

Click any link below to view or save the Excel™ spreadsheet field-level help file:

Message Data Field Descriptions
Aggregation - Aggregation specific fields
- Entitlement Modes
- Product related fields
- Facility related fields
Commission - Commission specific fields
- Entitlement Modes
- Product related fields
- Facility related fields
- ILMD
Decommission - Decommission specific fields
- Entitlement Modes
- Product related fields
- Facility related fields
Despatch Advice - Despatch Advice specific fields
- Entitlement Modes
- Product related fields
- Facility related fields
Disaggregation - Disaggregation specific fields
- Entitlement Modes
- Product related fields
- Facility related fields
Master Facility - Master Facility specific fields
- Entitlement Modes
Master Product - Master Product
- Entitlement Modes
Observation - Observation specific fields
- Entitlement Modes
- Product related fields
- Facility related fields
Payloads - Payload specific fields
- Entitlement Modes
- Product related fields
Purchase Order - Purchase Order specific fields
- Entitlement Modes
- Product related fields
- Facility related fields
Receive Advice - Receive Advice specific fields
- Entitlement Modes
- Product related fields
- Facility related fields
Transformation - Transformation specific fields
- Entitlement Modes
- Product related fields
- Facility related fields
- ILMD

Entitlement mode

All spreadsheets have an Entitlement mode worksheet, which lets you specify which of your supply chain partners can view the data. To change the entitlement mode, select a new value from the drop-down list. The entitlement mode applies to the entire XML file that is generated from a spreadsheet. A mode of none indicates that no data entitlement will be specified for the generated XML. When the XML is submitted to the IBM Food Trust API without an entitlement mode, the default data access control for the user's organization is applied. See data entitlement and data access control for details.

Attention: The data entitlement documentation describes setting the entitlement mode via the IFT-Entitlement-Mode header when submitting XML. Usage of this header supersedes any entitlement mode specified in a spreadsheet.

Explicit Entitled Org Ids

The Entitlement Mode worksheet also includes a column named Explicit Entitled Org Ids, which lets you explicitly specify which organizations are entitled to the mode selected in the Entitlement Mode dropdown list. Simply place each organization's Org Id on a separate row in the Explicit Entitled Org Ids column.

Custom bizSteps

All event spreadsheets include a hidden tab with selections for the supported bizSteps, such as packing and shipping. Support for custom bizSteps is also provided, in the format http://<client-controlled-domain-name/path/biz_step_name>. To enable selection and upload of a custom bizStep, it must first be added to the event spreadsheet hidden tab, as follows:

  1. Open the applicable event spreadsheet, such as Aggregation.xls.
  2. Right-click on any tab at the bottom of the spreadsheet.
  3. Select Unhide and then bizSteps.
  4. Add the custom bizStep URL to the bottom (first empty row) of the bizSteps list, in the format http://<client-controlled-domain-name/path/biz_step_name>.
  5. Once added and saved, users will be able to select the custom bizStep (URL) in the event spreadsheet.

Usage

Modify your downloaded sample (or create it programmatically) and submit it to the spreadsheet Converter API. If you have multiple events to convert to XML, enter the additional events in their corresponding worksheets and submit them to the API.

Swagger

The Spreadsheet Converter API is available on Swagger.

Endpoint prerequisite

To submit data, you must include the following Authorization header:

Header Value
Authorization Bearer <token>

Note: If necessary, refer to the authenticate system users documentation.

API responses

One of three responses is typically returned by calling a Converter API endpoint:

Response Meaning
200 Success
400 Error in Spreadsheet File
401 User Unauthorized

Error messages

401: User Unauthorized - Indicates a problem with the caller's authorization token, such as a missing Authorization Header, or an expired token (valid for three hours after assignment). This message can also result from specifying a token for the incorrect zone (integration or production).

400: Bad Data - Indicates a problem with the submitted data, with a detailed description.

All errors are returned in the response body separated by a new line. Here is an example with multiple errors:

<BadRequestResponse>
  <message>
    [Aggregations][2, 9]: BizTransaction may not be null when associated BizTransaction Type is populated.
    [Aggregations][2, 11]: Trade Item Description may not be null when associated fields are populated.
  Aggregation starting with Event ID urn:uuid:6926712e-599f-4c4e-b6e9-8dd888c906bd must have a Trade Item Description populated with Lot and/or Serial populated.</message>
</BadRequestResponse>
Errors (fatal) Description Message format
Unrecognized Workbook IBM Food Trust Converter is unable to recognize the workbook format. You will receive this error if you attempt to submit a spreadsheet from the deprecated IBM Food Trust Basic Converter. Uploaded File Error: Could not recognize Spread Sheet Format.
Missing Sheets The workbook is missing a required worksheet The following required sheets are missing. [sheet_names]
Unrecognized Sheets The workbook is missing a required worksheet The following sheets are not recognized. [sheet_names]
Missing Header A worksheet is missing a header entry (column) <sheet_name> sheet has missing headers [header_names]
Missing Field The sheet specified is missing a value [sheet_name][row, column]: <column_name> may not be null.
Two Missing Fields The sheet specified requires that at least one of two values must be populated [sheet_name][row, column]: <column_name_1> and <column_name_2> may not both be null.
Multiple Missing Fields The sheet specified requires multiple associated values must be populated <sheet_name> starting with <primary_key_column> <primary_key_column> must have a <column_name_1> populated with <column_name_2> and/or <column_name_3>.