V 3.1.1

Revision History

Languages

Introduction

The Fuel Transaction Import Add-in allows users to import Fill-up transactions extracted by fuel provider’s application/service into MyGeotab. This tool is a revised version of the original Add-in.

You can import transactions using three different options:

1. WEX
2. General Standard
3. Import Fuel Provider [NEW]

This document refers to the 3.1.1 version of the Add-in.

Installing the Add-In

To install the Add-in, follow the steps below:

1. Navigate to Administration > System… > System Settings from the MyGeotab left menu.
2. Open the Add-Ins tab.
3. Click New Add-In.
4. Paste the information below to the Configuration tab.

{

"name": "Fuel Transaction Import",

"supportEmail": "support@geotab.com",

"version": "3.1.1",

"items": [

{

"icon": "https://cdn.jsdelivr.net/gh/Geotab/addin-fuel-transaction-import@master/dist/images/icon.svg",

"path": "EngineMaintenanceLink/",

"menuName": {

"en": "Fuel Transaction Import"

},

"url": "https://cdn.jsdelivr.net/gh/Geotab/addin-fuel-transaction-import@master/dist/importFuelTransactions.html"

}

]

}





5. Click OK.

You can now open the Add-in by navigating to Engine & Maintenance > Fuel Transaction Import from the MyGeotab main menu.

Creating a JSON File

To use the Import Fuel Provider Add-in, you need a JSON file to tell the Add-in from where in the Transaction file it needs to gather the required data for the Fill-up transaction.

Template Fuel Provider Mapping File (JSON file)

Template Master 1.3

Populating the Fuel Provider Mapping File (JSON file)

The JSON file must be in JSON format. The JSON file is composed of one main array called “providers”. Inside the "providers" array, one or more entities represent the Fuel Provider's mapping.The JSON file must be in JSON format.

Example

Name: Mandatory. Represents the name of the Fuel Provider. This name will be listed in a dropdown menu in the Add-in.

unitVolumeLiters: Boolean value that specifies if the fuel in the transaction file is expressed in liters or gallons. Accepts only “Y” or “N”. This is a mandatory field, and cannot be empty.

unitOdoKm: Boolean value that specifies if the odometer in the transaction file is expressed in km or miles. Accepts only “Y” or “N.” This is a mandatory field, and cannot be empty.

isCellDateType: Boolean value that specifies if the date cell in the Excel file is Date format or not. By default, the value is “Y” and indicates that the format is a date in Excel. If it is a date, the software does not check how the dateFormat field is filled. If it is date and time composed by multiple cells (for example, ColumnC+ColumnB), it is mandatory to fill out both fields dateFormat and timeFormat.

dateFormat: Mandatory. Specifies the format found in the transaction file. Look at the table below in order to know the formats allowed. The Add-in will transform the date into UTC format automatically. If the date in the XLSX file has hours, minutes and seconds within a single cell, the field “timeFormat” can be blank ["timeFormat" :""].

Table Date Format:

timeFormat: Field that must be populated in case the transaction file has hours, minutes and seconds not in the same date cell. In case of multiple cells, write only the hours, minutes and seconds format and check the table date format to verify if the dateFormat + timeFormat is valid.

Example:

currencyCodeMapped: Field to directly add the three-digit ISO 4217 currency code (i.e. “EUR” or “USD”) into the JSON file. Single Key value, does not accept columns mapping from XLSX file. Optional field. Default [“”].

data: Represents the data that will match with the data in the XLSX file. Each value of the key in this section must follow the following format, depending on the type of key:

“Key”: “ColumnA”,[in case the key must have a single value.

“Key”: [“ColumnA”,”ColumnB”],in case the key can have multiple values found in the XLSX.

ColumnA must have a capital first letter (“C”) and must end with the letter of the column identified in the XLSX file.

Any of the following fields is documented also in the MyGeotab SDK documentation.

cardNumber: The masked or partial purchasing card number. Single Key value, doesn’t accept multiple columns in the XLSX file. Optional field. Default [“”].

Comments: The free text field where any user information can be stored and referenced for this entity. Single Key value, does not accept multiple columns in the XLSX file. Optional field. Default [“”]. Maximum length: 1024 characters.

Description: The asset description. Single Key value, does not accept multiple columns in the XLSX file. Optional field. Default [“”]. Maximum length: 255 characters.

Device: Do not fill this Key, leave it empty. -> “device”:””

Driver: Do not fill this Key, leave it empty. -> “driver”:””

driverName: The fuel card holder name. Single Key value, does not accept multiple columns in the XLSX file. Optional field. Default [“”].

externalReference: Single Key value, does not accept multiple columns in the XLSX file. The external reference to the transaction. Typically, this is an external identifier. Optional field, default [“”]. Maximum length [255].

licencePlate: Single Key value, does not accept multiple columns in the XLSX file. The license plate of the vehicle. This can be used to associate the transaction with a Device. Mandatory field, default [“”]. [LicencePlate OR serialNumber OR vehicleIdentificationNumber must be present]. Maximum length: 255 characters.

provider: Single Key value, doesn’t accept multiple columns in the XLSX file. The FuelTransactionProvider of this transaction. Optional field, leaving the field default [“”] provider will be unknown.

serialNumber: Single Key value, doesn’t accept multiple columns in the XLSX file. The serial number of the device. This can be used to associate the transaction with a Device. Mandatory field, default [“”]. [LicencePlate OR serialNumber OR vehicleIdentificationNumber must be present].Maximum length: 255 characters.

siteName: Multiple Key value, accepts multiple columns in the XLSX file. The site/merchant name where the transaction took place. Optional field, default [“”]. Example for multiple columns “siteName”: [“ColumnA”,”ColumnB”]

sourceData: Do not fill this Key, leave it empty. -> “sourceData”:””

vehicleIdentificationNumber: Single Key value, doesn’t accept multiple columns in the XLSX file. The vehicle identification number (VIN) of the vehicle. This is used to associate the transaction with a Device. Mandatory field, default [“”]. [LicencePlate OR serialNumber OR vehicleIdentificationNumber must be present]. Maximum length: 255 characters.

cost: The cost of the fuel transaction. Single Key value, doesn’t accept multiple columns in the XLSX file. Optional field, default [“”].

currencyCode: The three-digit ISO 4217 currency code (http://www.xe.com/iso4217.php). Single Key value, does not accept multiple columns in the XLSX file.Optional field, default [“”]. We have two ways to specify the Currency Code.

1. Add the Currency Code (i.e. “USD” or “EUR”) directly into the “currencyCodeMapped” property in the JSON file:
   
2. Add the column mapping in the property “currencyCode” in the JSON file:
   

The Add-in firstly checks the property “currencyCodeMapped”, if empty checks “currencyCode” , if empty the default value imported will be “USD”.

dateTime: Multiple Key value, accepts multiple columns in the XLSX file. The date and time of the transaction. Optional field, default [“”]. Example for multiple columns “dateTime”: [“ColumnA”,”ColumnB”]

location: Do not fill this Key, leave it empty. -> “location”:””

odometer: Single Key value, does not accept multiple columns in the XLSX file. The driver recorded the odometer reading. Optional field, default [“”].

productType: Single Key value, does not accept multiple columns in the XLSX file. The FuelTransactionProductType of this transaction. Optional field, leaving the field default [“”] provider will be unknown.

volume: Single Key value, does not accept multiple columns in the XLSX file. The volume of fuel purchased. Optional field, default [“”].

version: Do not fill this Key, leave it empty. -> “version”:””

Id: Do not fill this Key, leave it empty. -> “id”:”

Importing a JSON File

To import a JSON file, follow the steps below:

1. Open the Add-in by navigating to Engine & Maintenance > Fuel Transaction Import from the MyGeotab main menu.
2. Click the Import Fuel Provider button.

3. Click Select Provider File to choose a JSON file.

4. Click Import JSON File. After it is correctly parsed by the Add-in, select the Provider from the dropdown menu.

Importing a Fuel Card

To import a Fuel Card, follow the steps below after importing the JSON file.

1. On the Select Transaction File for Provider Selected section, select the checkbox if the transactions are in a different timezone than the one automatically selected.
2. Select the correct timezone of the fuel transactions present in the XLSX file.
3. Click Browse to select the Fuel Card XLSX file.
4. Click Open File to preview the different columns to import.
5. See the preview and click Import if everything is correct. Otherwise, click on Cancel.

6. Click OK to close the message confirming your Transaction ID.

What data must be included in the fuel transaction import?

Fuel card data must include the following data points:

1. A device identifier
2. 1. device (default null) - if null, best attempt will be auto matched to a device based on vehicleIdentificationNumber, serialNumber, licencePlate or comments properties.
   2. licencePlate (default = empty string)
   3. serialNumber (default = empty string)
   4. vehicleIdentificationNumber (default = empty string)
   5. description (default = empty string)
   6. comments (default = empty string)
2. dateTime - The UTC date and time of the transaction.
3. volume - The volume of fuel purchased in Liters. Default [0].
4. cost - The cost of the fuel transaction. Default [0].
5. currencyCode - The three digit ISO 427 currency code (http://www.xe.com/iso4217.php). Default ["USD"].

How is fuel card data matched?

To match a fuel transaction to a device, there must be at least one reference between the fuel card data and the device. The following reference fields are acceptable: VIN, Device Serial Number, License Plate, Device Description (name), Username and Comments. If fuel card data cannot be matched to a vehicle, it will not appear as an event on the Fill-Ups page.