Define the CSV File Structure

Owned by Nancy Cohn (Deactivated)
Last updated: Aug 09, 2024 by Pamela Totas (Deactivated)
6 min read

 

In IWS, you can use the Raw Service tool to define the CSV file structure. You can create a new feed, create global columns, and create a dimension table. You can save your changes periodically. After you finish, you can publish your changes to generate the code and upload it into the environment to make it available for processing.

Create a New Feed

This procedure describes how to create a new feed and provides an example that you can follow.

Before you begin, create a sample CSV file that you can load. In a local folder, create a file named test_csv_.csv with the following content:

REPORT_DATE,ENTITY_ID,USER_DATE4,USER_CHAR4,USER_DATE5,USER_NUM4,USER_NUM5,USER_CHAR5,USER_CHAR6,USER_NUM6,USER_DATE6 20170815,ENTTST01,20170101,TEST1,20170102,11.1,11.2,TEST2,TEST3,11.3,20170103 20170816,ENTTST02,20170104,TEST4,20170105,12.1,12.2,TEST5,TEST6,12.3,20170106 20170817,ENTTST03,20170107,TEST7,20170108,13.1,13.2,TEST8,TEST9,13.3,20170109 20170818,ENTTST04,20170110,TEST10,20170111,14.1,14.2,TEST11,TEST12,14.3,20170112 20170819,ENTTST05,20170113,TEST13,20170114,15.1,15.2,TEST14,TEST15,15.3,20170115

To create a new feed:

  1. Open IWS.

  2. In the Solutions pane, click Templates, and then click the Raw Service template in the Refresh pane.

    IWS Open window - Template and Raw Service options

  3. Click OK.
    In the Raw Service tab, you see the Raw Service tool.

    IWS - Raw Service tab

  4. In the Raw Service tab, click Create new to create a new feed.
    You see the Create new dialog box.

  5. Enter the Vendor name, Feed name, and Description field values, and then click Create.
    After you enter the initial data, you see the Edit Feed dialog box. The Data Settings tab is selected by default.

    Raw Service tool - Data Settings tab

  6. Enter values for the rest of the parameters.
    A description of the parameters follows. The number to the left of each parameter refers to its location in the related screenshot.
    - 1. Vendor. Vendor name as specified earlier in the Create new dialog box.
    - 2. Feed. Feed name as specified earlier in the Create new dialog box.
    - 3. Sample Data tab. Tab that provides data parsing options.
    - 4. Header lines. Number of header lines. They are skipped during the processing.
    - 5. Footer lines. Number of footer lines. They are skipped during the processing.
    - 6. Header at line number. Line number from header lines, which should be used as captions line.
    - 7. Columns Separator. Column separator.
    - 8. Rows Separator. Record separator.
    - 9. Grid. Grid view of the sample data. Use the Load From File button to upload sample data for the feed. The data is rendered in the grid according to the provided parameters. An example follows in the next step.
    - 10. File Name Parameters tab. Provides an area where you can create variables from the name of incoming file. An example follows in the next section.
    - 11. Raw Data tab. Displays a raw view of your sample data file. An example follows.
    - 12. Custom Parameters tab. Advanced parameters.

  7. For this example:
    - Select the Header lines check box and set the related box to 1
    - Select the Header at line number check box and set the related box to 1
    - Set the Columns Separator list to ,
    - Click the Load From File button, and then select the sample file to load named test_csv.csv
    Make sure the information is parsed correctly, according to the provided separators. See the following figure.

    Raw Service tool - Data Settings tab's Sample Data tab

  8. To view the sample file in its original form, click the Raw Data tab. 

    Raw Service tool - Data Setting tab's Raw Data tab

  9. At any time during the editing, you can click the Save feed option in the ribbon to save your progress.

  10. Continue to edit the feed as needed.

Create Global Columns

When you create a new feed in IWS, you can extract values from the incoming file name and add them to the load process as global columns.

To create global columns:

  1. In the Raw Service tool’s Data Settings tab, click the File Name Parameters tab.

  2. If you specify a delimiter, the system splits the data file into multiple tokens, and assigns them variable names, which you can then adjust.
    These variables can then be added to the load.

    Raw Service tool - Data Settings tab's File Name Parameters tab

  3. At any time during the editing, you can click the Save feed option in the ribbon to save your progress.

  4. Continue to edit the feed as needed.

Create Dimension Tables

When you create a new feed in IWS, you can create a dimension table.

To create a dimension table:

  1. In the Raw Service tool’s Data Settings tab, click the Dimensions tab.
    You can specify the name of the dimension table in the Table Name field.

  2. In this example, you enter the name of the Dimension table as DM2D_TESTLOAD.
    See the following figure.

    IWS Raw Service tab - Dimensions subtab

  3. At any time during the editing, you can click the Save feed option in the ribbon to save your progress.

  4. Continue to edit the feed as needed.

Review Settings and Feed Description

The Structure Description tab displays the names, types, and formats of columns, and the key fields of the dimension table.

You can alter any information generated for you in the structure definition grid, as well as specify the load key by selecting the check boxes next to the field name.
Global fields, such as the ones you extracted from the file name, appear in a darker background.

To review the settings and feed description:

  1. In the Raw Service tool, click the Structure Description tab.

  2. In the grid, you can alter any information generated for you, as well as specify the load key by selecting the check boxes next to the field name.

    Raw Service tool - Structure Description tab

  3. You can change the data type and data format in this tab.
    The fields include:
    - Key. Specifies key fields for the data – needed for the dimension table. If you are creating the Dimension table, be sure to select key fields for this table. If you select no fields as key fields, the systems does not create the Dimension table.
    - Name. Identifies the name of the field (generated by the header line).
    - Data Type. Specifies the type of data for this field. Values currently include CHAR, NUMBER or DATE. (New data types can be added in the future.) You can change the value if the type is incorrect.
    - Format. Specifies the format of data for this field, and applies only for DATE type. For example, values can include YYYYMMDD, YYYY-MM-DD, MM/DD/YYYY, and so on. You can change the value if the format is incorrect for the data. The format value should be null for all types excluding DATE.
    - Alias. Specifies the alias for this field.
    - Enum. Specifies the enumeration for this field.
    - NA and Missing Data Handling. Specifies the value for missing data in this field.
    - Description. Specifies a description of this field.
    - Selected. Indicates that the data of the selected column will be loaded if you select the column’s check box. (For Future Use)

  4. At any time during the editing, you can click the Save feed option in the ribbon to save your progress.

  5. Continue to edit the feed as needed.

Save and Publish

At any time during the editing, you can click Save feed in the ribbon to save your progress.

Raw Service tab - Save feed option and Publish option

When you are satisfied with all the information entered, click Publish to generate the code and upload it into the environment to make it available for processing.

The Publish Feed box displays all the files to upload to the environment, and allows you to preview the content. After the publish process completes, you can use your feed for loading data. For more information about publishing, see Publish the IWS Solution for CSV.

Publish Feed box