tool Spreadsheet Converter

IBM Food Trust™ Spreadsheet Converter

Contents:

Overview
Sample Spreadsheets
Empty Spreadsheet Templates
Usage
Swagger API
API Responses
Next Steps

Overview

There are two APIs available for natively converting Microsoft Excel files for IBM Food Trust -- a "basic" and a "full" converter. The main differences between the two is that one only requires the bare minimum amount of fields to generate the data into JSON, while the other allows for every XML field to be defined and converts directly to XML.

Why are there two?
The basic API was developed first as a tool to help users become familiar with the IFT Messages. Over time the need arose to provide a capability for users to submit all data fields via Excel spreadsheets.

This page is dedicated to the Spreadsheet Converter. If you need to the basic converter, please see the Basic Spreadsheet Converter API.

IBM Food Trust™ (IFT) Spreadsheet Converter converts Microsoft® Excel® files into XML format for submission to IFT Connector. This tool enables you to easily generate valid IFT messages from Excel files.

To start, download a sample Excel spreadsheet (see 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

Empty Spreadsheet Templates

To use the templates simply double-click to open the file and select File -> Save As and select Excel Workbook (.xslx) as the extension.

Entitlement Mode

All spreadsheets have an Entitlement Mode worksheet which lets you change the default data access control level.

To change the default entitlement mode select a new value from the dropdown. This entitlement mode applies to the entire XML 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 IFT Connector 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 please visit our Documentation section.

Note: The Data Entitlement documentation discusses setting the entitlement mode via an IFT-Entitlement-Mode header when submitting the XML to IFT Connector. This takes precedence over setting the entitlement mode in the 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 an 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	IFT Converter is unable to recognize the workbook format	`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 submit the returned XML files to IFT Connector for ingestion into the system.