User Guide

0 mins to read

For All 4.x.x Add-In Versions

April 2024

Introduction

The Fuel Transaction Import Add-In allows users to import fuel fill-up transactions supplied by a fuel card provider into a MyGeotab database.

References

  1. The Configuration file git repo is provided as a reference for the configuration file. The repository should provide all the necessary information to get a new configuration file configured and functioning.
  1. The Fuel transaction entity (SDK) provides the backend object structure that is called in order to add the fuel transaction data. This is for any users that have more of a technical interest in the underpinning of this utility.

Installing the Add-In

To install the Fuel Transaction Import Add-In, go to MyGeotab, then follow the steps below:

  1. Navigate to Administration > System… > System Settings.
  2. On the System Settings page, select the Add-Ins tab and click New Add-In.
  3. In the Add-In popup window, add the following configuration:

{ "url": "https://app.geotab.com/addins/geotab/addin-fuel-transaction-import/manifest.json" }

To access the Add-In, select Engine & Maintenance > Fuel Transaction Import from the MyGeotab main menu.

document Image

Understanding the configuration file

The configuration file is the column mapping and configuration information required to execute a successful fuel transaction import operation.

See the GitHub configuration file repository to understand the configuration file structure, its properties, and how to correctly compile it in order to successfully import fuel transaction data into the MyGeotab database system.

The configuration file must contain the column mapping and configuration to be expected in the import process and describes the Excel import file.

An example configuration file and some formatting instructions can be found in the git repo, for example, source.json.

The data JSON object in the configuration file contains the Fuel Transaction entity property fields to be imported and their column references.

Example

Understanding the Excel import file

The Excel import file is an Excel spreadsheet (XLS, XLSX) file supplied by a fuel card provider (or a custom file produced through some other mechanism) containing the fuel fill-up transaction data to be imported.

Some context for the Excel import file includes:

  1. A header row is expected.
  2. NOTE: The first row is skipped as it is assumed to be a header row.

  3. There has to be at least one device identification field which is either a licence plate (licencePlate), VIN number (vehicleIdentificationNumber), or the Geotab serial number (serialNumber). The Comments and Description fields will also be searched for device identification details if the prior mentioned options do not resolve a device.

NOTE: The Maximum number of transactions that will be imported per import file is 5000, so truncate your import files where necessary to below this threshold in order to succeed.

Note

  1. Only dateTime and location accept array type data.
  2. None of the data properties accept input values. They only accept column references.

Importing Fuel Transactions prerequisites

There are some prerequisites for importing fuel transactions. These include:

    1. A correctly structured configuration file.
    2. An Excel file containing the transactions to import.
    3. Each transaction should at least identify a device by containing either a serial number, a VIN (vehicle identification number) or a licence plate number reference in order to successfully identify the device.

    ✱ NOTE: If a device does not have any of the identification references, the record will still be imported, but it will be orphaned and will not have the possibility of matching an actual fill-up event.

    Importing the fuel transaction files

    Follow these steps to import the fuel transaction files (refer to the image that follows).

    1. To select the configuration file (JSON), select the Choose file button. (1)
    2. Select the fuel provider from the Choose provider dropdown. (2)
    3. To pick the correct import file (XLS or XLSX), select the Choose file button. (3)
    4. If the sheet is not the first one in the workbook then change the Sheet number. (4)
    5. Choose the correct time zone by selecting the Reference Time zone dropdown. This should default to your user profile time zone set in MyGeotab options. (5)
    6. Select the Import button after the input criteria is complete to initiate the process. (6)
    7. In order to reset all the input fields, hit the Reset button. (7)
    8. document Image

    9. The page will display the progress loading status while importing. Once this is complete, the Import Summary section will display a summary of imported, skipped, and errors.

    document Image

    ✱ NOTE: The imported transactions will not reflect in the fuel fill-ups page report immediately. There is a post-processing action required that performs the physical matching of these transactions to the fill-ups at a later stage. Generally, the matched transactions will display the following day.

    Frequently Asked Questions (FAQ)

    Q: How long does it take for a transaction to be matched with the fill-up event?

    Imported fuel transactions are processed at the end of each day, and are not reflected in the database immediately. A delay of up to 24 hours should be assumed.

    Q: What is the purpose of the Reference Time Zone field?

    The Reference Time Zone field allows you to select a time zone other than the current local time zone. In other words, you can import fuel transactions for other time zones or for different time periods, such as importing transactions that occurred during daylight savings time.

    Appendix 1

    Configuration File Example

    Refer to this sample configuration file and then the following explanation.

    Also refer to the sample Excel import file associated with this configuration.

    Screenshot of the example.json file:

    document Image

    Screenshot of the example Excel import file:document Image

    Example Explanations

    Example describing each property and its associated setting/value.

    FuelProviderExample1 Explanation

    Property

    Setting/Value

    Explanation

    Name

    FuelProviderExample1

    The name property that identifies the configuration. It is the value you select in the Fuel provider dropdown menu in the Add-In after the configuration has been selected in the prior field (Select the configuration file (JSON):).

    unitVolumeLiters

    Y

    This option indicates whether the fuel volume is defined in metric or imperial units. Set to Y for metric (litres), and set to N for imperial (gallons).

    unitOdoKm

    Y

    This option indicates whether the odometer reading is defined in metric or imperial units. Set to Y for metric (kilometres), and set to N for imperial (miles).

    isCellDateType

    N

    This option indicates whether the import file column containing the date is formatted as a date type or a general (text) value. Set to Y for date type, and set to N for text.

    dateFormat

    dd/MM/yyyy

    The date format to be expected in the transaction file, based on the luxon table of tokens formatting definition.

    timeFormat

    empty

    N/A - empty

    currencyCodeMapped

    empty

    N/A - empty

    Data section

    cardNumber

    ColumnA

    The column in which the fuel card number is found in the Excel spreadsheet.

    licencePlate

    ColumnG

    The column in which the vehicle licence plate is found in the Excel spreadsheet.

    provider

    ColumnB

    The column in which the provider is found in the Excel spreadsheet.

    cost

    ColumnC

    The column in which the transaction cost is found in the Excel spreadsheet.

    currencyCode

    ColumnD

    The column in which the currency code is found in the Excel spreadsheet.

    dateTime

    ColumnE

    The column in which the transaction date and/or time is found in the Excel spreadsheet. This is a special property called an Array which is demarcated by [square brackets] which can contain either one or two column references. For example, [“ColumnE”] or [“ColumnE”, “ColumnZ”], representing the date and/or the time.

    volume

    ColumnF

    The column in which the transaction volume is found in the Excel spreadsheet.

    odometer

    ColumnH

    The column in which the vehicle odometer is found in the Excel spreadsheet.

    Multiple Fuel Provider Example

    To see how a configuration file containing multiple fuel providers is configured, refer to this link.

    scroll-up