tool Spreadsheet Converter

Spreadsheet Converter

Contents:

1. Overview
2. Sample Spreadsheets
3. Empty Spreadsheet Templates
4. Spreadsheet Documentation
5. Entitlement Mode
6. Explicit Entitled Org Ids
7. Custom bizSteps
8. Usage
9. Swagger API
10. API Responses
11. Next Steps

Overview

IBM Food Trust™ Spreadsheet Converter converts Microsoft® Excel® files into XML format for submission to the IBM Food Trust API. This tool enables you to easily generate valid IBM Food Trust messages from Excel 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.

To start, download a sample Excel spreadsheet (or a blank template) below, and review the worksheets and the layout required by the Spreadsheet File Converter. Your input file may contain multiple worksheets— including one for locations and one for products. Together, these two worksheets consist of your Master Data, and provide a single source of attributes and values for easy reuse. The worksheets contain drop-down lists to easily select values that the Spreadsheet File Converter requires.

Sample Spreadsheets

Click any sample link to view or save the sample spreadsheet file:

• Aggregation
• Commission
• Decommission
• DespatchAdvice
• Disaggregation
• Facility
• Product
• Observation
• Payloads
• Purchase Order
• Receive Advice
• Transformation

Blank Spreadsheet Templates

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

• Aggregation
• Commission
• Decommission
• Despatch Advice
• Decommission
• Disaggregation
• Facility
• Product
• Observation
• Payloads
• Purchase Order
• Receive Advice
• Transformation

Spreadsheet Documentation

The Excel workbooks below provide field-by-field details for each spreadsheet accepted by IBM Food Trust. They include detailed field descriptions, required formats, examples, and requirements. Each table entry contains the collection of documentation files for each IBM Food Trust message type. Because many fields are similar across message types, they are grouped into spreadsheets to easily help you locate the documentation.

Note: Many of the workbooks 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 workbook row. The first trade item description, quantity, and quantity UoM cells (all Level 2) will be populated in the first workbook 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. Then 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 Workbooks

Click any sample link to view or save the sample spreadsheet file:

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

Entitlement Mode

All spreadsheets have an Entitlement Mode worksheet, which lets you change the data access control. To change the entitlement mode, select a new value from the dropdown list in the Entitlement Mode column. This entitlement mode applies to the entire XML file that is generated from a spreadsheet.

A mode of "none" indicates that an entitlement will not be applied to the generated XML. When the XML is submitted to the IBM Food Trust API without an entitlement mode, the default entitlement is applied.

Please see data entitlement and access control to learn about Data Entitlement and default access control levels for each IBM Food Trust message type.

Note: The Data Entitlement documentation discusses setting the entitlement mode via an IFT-Entitlement-Mode header when submitting the XML to the IBM Food Trust API; usage of this header takes precedence over setting the entitlement mode in the 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. 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

Once you become comfortable with the input file format, modify your downloaded sample (or create it programmatically) and submit it to the Spreadsheet File Converter. If you have multiple single events to convert to XML, simply enter the additional events on their corresponding worksheets and submit them to the API.

Attention: To comply with data privacy laws, NO data that personally identifies an individual can be submitted to IBM Food Trust.

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 details on obtaining a valid authentication token.

API Responses

One of three responses is typically returned by calling a Spreadsheet 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>.

---

Next Steps

Once you have successfully converted your spreadsheet data to XML, you will upload the returned XML files to IBM Food Trust.