Skip to Content

Text File to Database

Create a New Transformation

  • Make sure you have opened the appropriate project in which you want to store the new transformation.

  • There are several ways you can start to create a new transformation: If you are working in an operation, you can click the Control Button to the right of the Transformation field and select Create New; OR

    • If you are working in an operation, you can click the Control Button to the right of the Transformation field and select Create New.
    • Click the New Transformation from the top toolbar.
    • Right-click on Transformations in the hierarchical tree in the Design tab in the left pane of the screen, then choose New Transformation from the pop-up menu.

  • The transformation wizard appears in its own New Transformation tab in the right pane of the window.

Specify Source and Target Types

  • Enter an Appropriate Name for your new transformation in the Transformation Name field.

  • Select Text from the Source and Database from the Target drop-down menus.

  • Click the Next Button in the toolbar to continue.

Select Source Text Information

  • In the next screen of the Transformation Wizard, select the Document you want to use as a source from the Text Document Definition drop-down menu, OR click the Control Button to the right of the field to create a new definition. See Defining File Formats or Creating a Text Document Definition from a Sample File for additional information.

  • Click the Next Button in the toolbar to continue.

Select Database Target Information

  • Select the Name of the data target that you defined as your Target Definition from the Database Target drop-down menu, OR click the Control Button to the right of the field to create a new target database.

  • Click the Next Button in the toolbar to continue.

  • The next steps you follow depend upon whether you want to use only a single table in your target database, or whether you need to access more than one database table. Select how you will write information to your target database:

    • Only a single table in the target database will be accessed.

    • More than one table in the target database will be accessed.

What's Next?

Once you have completed defining your database target, a tree data structure should appear in the target transformation tree to depict what you have defined. In mapping to your database target, if you have a multi-table target with parent child relationships and you are inserting data, you should become familiar with using the transformation functions <SQLIDENTITY> and <SEQUENCE>.

  • <SQLIDENTITY> is used for all target databases that support identity or automatic number generating keys. Once the parent is inserted then the child can inherit the generated number from the parent by mapping this function to the foreign key in the child table.

  • <SEQUENCE> performs a similar action and should be used for Oracle.

Now that you have defined the nature of the source and your desired target, you are ready to map the relationship between your source and target at the field, or element, level.