Liquid Studio Documentation
Data Mapping / Data Mapper Functions / Excel Source
In This Topic
    Excel Source
    In This Topic

    Function Name
    Excel Data Source
    Reads data from an Excel Spreadsheet (Worksheet, Table or Range).
    Filename Source file to read data from, if this input is not connected then the filename provided in the 'Default Source File' property
    Row A row is create for each line read form the source file
    Child Items Row contain a number of child items that reflect the fields defined when the component is created.
    Default Source File The default data file to read from. This is used if the input connection point 'Filename' is not connected.
    Store Relative Indicates if paths should be stored relative to the transform, or as absolute paths.

    Creating an Excel Source

    When the Excel Reader component is dragged onto a transform a setup wizard is launched to guide you through the creation of the data source.

    Excel Data

    The first screen allows you to select your Excel file containing the structure of the data you want to read.

    The Wizard will analyse the spreadsheet and display all the Sheets which contain data along with globally and locally defined regions and tables.

    You then have the choice to either select one of these predefined areas (which populates the Refers To field) or specify a data range yourself in the 'Refers To' field.

    Additionally, you can specify if the First Line contains column names and optionally Ignore Last Line which, for example, may contain totals.


    After you have specified the spreadsheet range to read data from, the Wizard will display the Columns contained within the range along with a calculated Data Type for each column based on the existing data within each column.

    This page allows you to change the Data Type, e.g. the current data may look like integer values, but you may want to change this to be String.


    Once you press Finish the new Excel Data source is added to the transform and can be used as any other data source is.

    Property Pages



    The name of the component, must be unique within the transform



    Default Encoding

    The encoding used to decode the data read from the file into text if the encoding is not specifically identified within the file (see BOM).

    First line contains field headings

    If checked then the first line in the data file is ignored as it is assumed to contain the names of the columns

    Ignore invalid rows

    The transform does not stop when an invalid row is encounters, it just ignores it and moves onto the next line.

    Remove leading and trailing whitespace from each field

    If checked leading and trailing whitespace is removed from the field value i.e.

    Col1 ,  Col2 , Col3

    if checked the values are "Col1", "Col2", "Col3"

    if unchecked the values are "Col1 ", "  Col2 ", " Col3"

    Fields are enclosed in quotes

    When checked quotes are considered to be enclosing characters, i.e.

    "col 1 has a coma, would normally be seen as a separator", col 2

    By enclosing column 1 in quotes the ',' is treated as data not a separator.


    All the delimiters selected are treated as column separators unless they are enclosed in quotes (see Fields are enclosed in quotes).

    Edit Columns

    Allows the column definitions to be manually edited.

    Default input filename

    If the reader component does not have a connector to the 'Filename' or ' Text Source' then the filename is read from this property.

    If the connection point 'Filename' or ' Text Source' is connected, then this is ignored (and can be blank)

    Store as relative path

    Indicates that the filename is to be resolved relative to the data mapping file (.dm). If the transform is compiled the filename will be resolved relative to the compiled exe.