Creating interface services

The interface service allows the user to import or export data to an SCV table from/to a CSV or Excel file. This topic describes the process of creating an interface service with Data Entity = SCV Table and Interface Type = CSV.

To create an interface service:

  1. Select Configuration > Common > Interface Services > Interface Services.
  2. Click New. A New Interface Service window is displayed.
  3. Specify this information:
    Interface Type
    The type of the interface. Select CSV.
    Data Entity
    The data entity to be mapped to the interface. Select SCV Table.
    Note: The Column per Measure check box is displayed if the Data Entity field is set to Scenario Value.
    Interface Direction
    The direction of the data flow. Possible values:
    • Import
    • Export
  4. Click OK. A new Interface Services page is displayed.
  5. Specify this information on the Interface Details tab:
    Name
    A unique name of the interface service.
    Display Name
    The name that is displayed for the interface service. If not specified, the value specified in the Name field is defaulted.
    System
    Indicates the status of the interface service. Possible values:
    • On (Enabled)
    • Off (Disabled)
    Note: This option is enabled for the entity defined by the system.
    Interface Type
    The type of the interface. This value is defaulted from the New Interface Service window.
    Data Entity
    The data entity to be mapped to the interface. This value is defaulted from the New Interface Service window.
    Interface Direction
    The direction of the data flow. This value is defaulted from the New Interface Service window.
  6. Specify this information on the Data Source tab:
    Data Source
    The name of the data source (File, FTP or Amazon S3) defined in . The definition defined in the data source is used when executing the interface service using a macro.
    Note: If you are manually importing or exporting data using a file interface service, browse the file to be used on your local machine.
    Use File Name Pattern
    A pattern or expression to define the file name. This supports importing multiple files and ordering or grouping of the files to import the latest version. The file name with the matching pattern are imported.
    Note: This option is displayed only if the Interface Type field is set to Import.
    Files
    The name of the file to be used for interface service. The files that are displayed are based on the Data Source defined. If the files are not displayed, click plus (+) to add the required file.
    Note: If the Use File Name Pattern option is set to On, the field label is displayed as File Name Pattern and regular expression can be used as the file name. These variables are accepted with an underscore as separator between the variables:
    • Any alpha character (A-Z)
    • Any alpha-numeric character (A-Z,0-9)
    • Any numeric character
    Field Names in Header Row
    Indicates the content of the first row of the file that is imported or exported. If set to On, these are the possible scenarios:
    • When the file is imported, the first row of the import file contains field labels and can be skipped.
    • When the file is exported, the first row of the export file contains the field headers.
  7. Select the required SCV table name in the SCV Table tab. The data in the Mappings tab is displayed based on the SCV table you select.
    Note: 
    • When the file imported, all tables registered in SZ_REG_TABLE with the field table_area = Inbox or %_Import are listed.
    • When the file is exported, all SCV tables and views are listed.
  8. Specify this information in the File Detail tab:
    File Delimiter
    The file delimiter type. Possible options:
    • Comma
    • Tab
    • Pipe
    Note: By default, the value is set to Comma.
    Number Format
    The number format for the values to be validated when importing data to numeric fields in the SCV database. The numeric values are exported based on the defined format.
    Date Format
    The date format for the values to be validated when importing data to the date fields in SCV database. The date values are exported based on this defined format.
    Note: You must specify the Month indicator and Hour indicator in upper case.
  9. Click the File Processing tab.
    Note: This tab is displayed only if the Interface Direction field is set to Import.
  10. Specify this information:
    Order Files By
    The order (ascending) in which the files are organized in the directory based on the value specified. These variables can be used:
    • %file_1 = The first variable in the file name
    • %file_2 = The second variable in the file name
    Note: This option is enabled only if Use File Name Pattern option is set to On in the in the Data Source tab.
    Import Latest File
    Indicates the importing pattern of the interface service. Possible scenarios:
    • On: Imports the latest file based on the files time stamp or by the value specified in the Order Files By option.
    • Off: Import all files in an order either by Time Stamp (the system time when the file is saved) or by the value specified in the Order Files By option.
    Note: 
    • This option is enabled only if the Use File Name Pattern option is set to On.
    • Time Stamp is based on the source OS.
    Group Files By
    The criteria based on which the files are ordered in a group. The files are ordered in each group based on the value specified in Order Files By option. Else the files are ordered by the latest date and time. The latest file in each group is imported.
    Note: This option is enabled only if the Use File Name Pattern option is set to On.
    Rename File After Import
    The variables based on which the successfully imported files are renamed. These variables are used for renaming:
    • %file = Complete file name
    • %file_0 = file name without extension

    Examples:

    • %file_save: TAR_MD_BOM_20170705060012.csv is renamed to TAR_MD_BOM_20170705060012_save.csv.
    • %file_0_complete: The file TAR_MD_BOM_20170705060012.csv is renamed to TAR_MD_BOM_20170705060012_complete
  11. Specify this information in the Import Behavior tab:
    Clear Destination Table Before Import
    Indicates if the target table must be cleared of all the rows or the data must be added to the exiting record, before executing the interface and importing rows. Possible scenarios:
    • On: Delete and insert
    • Off: Append
    Clear based on Source Column/Value
    The condition based on which records are deleted from the target table. Possible scenarios:
    • If the values are not specified in these field, all the records are deleted from the target table.
    • If specified, only those records for which the values specified in the Source Column and the Value field are same; are deleted.
    Note: These options are enabled only if the Clear Destination Table Before Import option is set to On.
  12. Click the Export Filter tab. This tab allows you to define the conditions to filter the data on export.
  13. Specify this information:
    Column
    The column name of the SCV table.
    Operator
    The operator used to set the filter condition. Possible values:
    • Equals
    • Not Equals
    • Like
    • Not Like
    • Is Null
    • IS Not Null
    Value
    The value to which the filter condition is applicable.
  14. Click the Export Behavior tab.
    Note: This tab is displayed only if the interface direction is set to Import.
  15. Specify this information:
    Append Time Stamp to File Name
    Indicates if a Time Stamp must be added to the exported file name.
    Note: 
    • The Time stamp must be specified in UTC format and added to the file name before the extension and preceded by an underscore. For example, FileName_20171030135355.csv.
    • By default, the value is set to Off.
    Includes Quotes for Exported Values
    Indicates if the exported values to be enclosed in quotes.
    Note: By default, the value is set to On.
  16. Click the Mappings tab. The Mappings tab displays the list of attributes. The attribute list is defaulted based on the SCV table selected.
  17. Add a new attribute, if required. See Adding new attribute.
  18. Click the Import Constants tab. This tab contains a list of attributes from the SCV table that must be mapped a constant value when importing the data.
    Note: 
    • This tab is displayed only if the Interface Direction field is set to Import.
    • This value specified in this tab overrides the data mapped in the Mapping tab.
  19. Add a new import constant, if required. See Adding new import constant.
  20. Click the Export Constants tab. This tab contains a list of attributes from the SCV table, to be mapped a constant value while exporting.
    Note: This tab is displayed only if the Interface Direction field is set to Export.
  21. Add a new export constant, if required. See Adding new export constant.
  22. Click Save.