2 – Excel Read Transform

In this step we will set up a Reader transform to pull data from an Excel file.

Design > Transform Setup

  1. Click on the Transform Setup tab.
  2. Click on Readers.
  3. Drag an Excel Reader onto the design palette.

Transform > File Layout

  1. Double click the Excel icon to open the transform setup screen and configure the transform:

  1. Transform Id
    • A unique Id used to identify the transform. The value is displayed in the detail section of the audit report.
    • For Training, enter: Excel Read.
  2. Data Source
    • For training, choose: File.
  3. File Path
    • A path to where the files are located.
    • For training, enter: <IMan Shared Data>\InputFile (replacing <IMan Shared Data> with the directory specified during the install)
  4. File Name
    • Can be either a static value or can contain the wildcards ‘*’ or ‘?’.
    • For training, enter: Sage200OrdersFile.xlsx
  5. Worksheet Id
    • The worksheet id refers to the worksheet where the data is located.
    • It can be referred by name, or by index, where the index starts at 0 (representing the first worksheet).
    • For training, enter: 0
  6. Header Rows
    • The number of rows containing ‘header’ text. In this example, there is one header row, containing the field names
    • For training, enter: 1 (there is 1 header row, containing the field names.)
  7. Mapping Style
    • If you leave as Field Heading, the field names will be interpreted from the row(s) preceding where the data begins, i.e. the header row(s).
    • For training: Leave 'Field Heading' selected.
  1. Press Refresh.
    • On the hand right pane of the screen will display the file.
    • Every screen except for ‘Tasks’ displays a live preview of the data being processed.
It’s important to note the Generation Status. Due to the asynchronous nature of processing you often need to press the Refresh twice.

Transform > Field Mapping

In this step we are going to alter some the field types. You don’t strictly need to alter the field types as IMan will attempt to convert them where necessary, but it’s good housekeeping, and will stop you from running into issues later!

  1. Click the Field Mapping tab.
  2. Rename the Transaction Id from ‘Record’ to ‘Orders’.

  1. Press Save.
  2. Click the edit button at the bottom of the grid.

  1. Change the following field types:
Field Name Set Type
AssemblyCharge Decimal
TwoManDeliverySupplement Decimal
Delivery Decimal
SalesTotal Decimal
LineNo Integer
Qty Decimal
UnitPrice Decimal
  1. Press the Green tick at the bottom of the grid to save the changes, press 'Apply' and close the setup window.
Step 3: Hierarchy Transform >