Skip to content

Step 2 – Excel Read Transform

In this step you set up a reader transform to read data from an Excel file.

Download the sample workbook and save it to C:\IMan\InputData\Training. Every later step reads it, and steps 11 and 12 move it. Keep a copy.

Design > Transform Setup

  1. Press the Transform Setup tab.
  2. Open the Readers group in the palette.

  3. Drag an Excel Reader onto the design surface.

    The Transform Setup tab with the Readers palette open, and an Excel Reader dropped onto the design surface

Transform > Setup

  1. Double-click the Excel Reader to open it.
    • It opens as a tab across the top of the screen, alongside Options and Transform Setup. You can leave several transforms open at once and move between them.

  2. Fill in the transform id and the Source section:

    The Setup tab: Transform Id set to Excel Read, and the Source section with the data source, file system, path and file name

    1. Transform Id
      • A unique id that identifies the transform. The audit report shows it in its detail section.
      • For training, enter: Excel Read
    2. Data Source
      • For training, choose: File
    3. File System
      • Where the file is stored. The Data Source sets how it is read.
      • For training, leave as: Windows
    4. File Path
      • The folder that holds the files. The button beside it opens a folder browser.
      • For training, enter: C:\IMan\InputData\Training
    5. File Name
      • A fixed name, or a name containing the wildcards ‘*’ or ‘?’.
      • For training, enter: OrdersFile.xlsx

  3. Open the Options section and set the remainder:

    The Options section: Worksheet Id, Header Rows, Footer Rows, Mapping Style and Hierarchy Style

    1. Worksheet Id
      • The worksheet that holds the data, by name or by index.
      • For training, enter: 0
    2. Header Rows
      • The number of rows containing ‘header’ text. This file has one header row, which holds the field names.
      • For training, enter: 1
    3. Footer Rows
      • The number of rows to ignore at the end of the sheet.
      • For training, leave as: 0
    4. Mapping Style
      • With ‘By Field Heading’, IMan takes the field names from the header row(s) above the data.
      • For training, leave as: By Field Heading (Design & Runtime)
    5. Hierarchy Style
      • Used where a single file holds more than one record type. You do not need it here. Step 3 builds the hierarchy.
      • For training, leave as: (none)

  4. Press Refresh.

    • The right-hand pane displays the file. Every screen except ‘Tasks’ shows a live preview of the data being processed.
    • Watch the status above the grid. It reads Progress - Completed when IMan has generated the data.

    The preview pane showing Progress - Completed and the first rows of the orders file

The preview is cleared when you move away from it

IMan clears the preview whenever you move away from it and back. Press Refresh again to fill it. The Trace and Audit tabs beside the preview show the detail behind a run. A dot appears on them when they have something new to show.

Transform > Field Mapping

In this step you name the transaction and change some of the field types.

Setting the types is housekeeping, not a requirement

You do not have to change the field types, because IMan converts them where necessary. Setting them is good housekeeping and avoids problems later.

  1. Press the Field Mapping tab.

    • The Transaction strip above the grid names the transaction this reader produces. A flat file produces a single transaction, named Root by default.

    The Field Mapping tab: the Transaction strip showing Orders, and the field grid below it, whose toolbar carries Edit on the left and Refresh Schema and a greyed Schema changes item on the right

  2. Press the pencil beside the transaction, enter Orders, and press the tick.

    The Rename Transaction dialog with Orders entered

  3. Press Edit above the grid.

    • Every row becomes editable at once, and the toolbar changes to Save and Cancel.

    The field grid in edit mode, with the Type column editable

  4. Find the following fields and set their types:

    Field Name Set Type
    AssemblyCharge Decimal
    TwoManDeliverySupplement Decimal
    Delivery Decimal
    SalesTotal Decimal
    LineNo Integer
    Qty Decimal
    UnitPrice Decimal

  5. Press Save above the grid to commit the changes.

  6. Press Close at the bottom of the transform, then Save the integration.

Step 3: Hierarchy Transform >