Skip to Content

Tutorial 1: Convert Relational Database Table to Delimited File

In this tutorial, users will learn how to convert data from a database table to a delimited file.

Conversion Type: One-to-one.

After completing this tutorial, users will be able to do the following:

  • Use a connection saved in a previous tutorial.
  • Create a delimited connector.
  • Map a source to a target using automapping.
  • Edit a record layout using the Record Layout Editor.
  • Save and run the conversion specification.
  • Browse source and target data (after running the conversion specification).
Error
Warning

DO NOT MODIFY SYSTEM TABLES. The tables start with dbo.sys.

Source and Target Data Descriptions

Data Source

Connector type: Microsoft SQL Server (use a database type installed on the system being used)

Connector name: The connector created in Tutorial 1 in Chapter 2, “Related Extract and Related Loader Tutorials”

Database name: fileaidex_tutorial

Table name: dbo.authors

Data Target

Connector Type: Delimited file

File name and location: <your directory path>/tutorial1.asc

Define the Data Source

The table or view selected as the data source will be converted to the output type specified when the data target is defined.

  1. Launch ConverterPro like any other application based on your operating system. The ConverterPro dialog box appears, displaying the Data Sources tab. The Data Sources tab allows users to define a data source connection.
  2. From the Connector Name list, select the connector templates. The Open Connection Template dialog box appears.
  3. Select the connection created in the first Related Loader tutorial and click OK. The connector panel appears with the connection information prefilled.
  4. Click Show all tables/views so that all tables and views from the fileaidex_tutorial database will be included in the selection.
  5. Click Connect. A connection validation confirmation message appears.
  6. Click OK.
  7. Click Next. The Table/View Selection panel appears with existing tables and views from the fileaidex_tutorial database listed in the Available Record Layouts box.

  8. Select dbo.authors and click image2021-8-26_18-24-16.png  to add this table to the Selected Record Layouts box.

    Error
    Warning

    DO NOT MODIFY SYSTEM TABLES. These tables start with dbo.sys.

  9. Click Next. The Data Targets tab appears. The project tree view in the left pane of the ConverterPro window displays the authors table and the chosen connector under the Data Sources node. The actual table name (designated by dbo.authors) and connector are displayed below the  authors table. Clicking the  table name,  actual table name, or connector displays their respective properties in the Properties view. Refer to the ConverterPro online help for detailed information about the project tree and properties views.

Define the Data Target

Use the Data Targets tab to define a target connection and the format of the conversion specification output.

  1. From the Connector Name list, select new.
  2. From the Connector Type list, select Delimited File. The Delimited File pane appears.
  3. In the Connector Name field, type Tutorial1_Delimited.

  4. In the File Name field, type <your  directory  path>/Tutorial1.asc,  or  click  Browse and navigate to <your directory path> and add file name Tutorial1.asc. The File Name field populates with the path and file name. This file is the data target in this conversion specification and will be created when this specification is run.

    Warning

    Important

    Ensure that the directory path specified is a valid, locally mapped drive or path to which the user has write access.

  5. From the Target Action (Exist/Not Exist) list, select Recreate/Create.
  6. Click Next. The Record Layout Selection pane appears.
  7. From the Existing Record Layouts box, select authors and click image2021-8-26_18-27-3.pngto add it to the
  8. Click Next. The Data Targets tab displays the field names, field data type, encoding type, and record occurrence or field length of the fields in the authors table.
  9. From the Record Separator list, select CR-LF.
  10. Click Next. The Mapping Editor tab appears. The project tree view displays the authors file and Tutorial1_Delimited connector under the Data Targets node. The path and file name are displayed below the authors file.

Map the Source to the Target

The Mapping Editor tab is used to specify source-to-target mapping.

Fields from the source authors table appear in the left pane, while fields from the target authors file appear in the right pane.

Using Automap

Use the automapping feature to map each field in the source authors table to a corresponding field in the target authors file.

If the field names in the source and target files are not the same, a source field can be manually dragged and dropped it onto a target field. In this tutorial, field names in the source table are identical to the field names in the target file, so automapping can be used.

Right-click authors in the Source column, then select Automap > By Field Name Match. All like-named fields are mapped. The automap result appears in the Mapped Field column.

Save and Run the Conversion Specification

Before running this conversion specification, save it to the repository.

  1. From the File menu, select Save. The Save Conversion As dialog box appears.
  2. In the Name field, type Tutorial1.
  3. In the Description field, type <database type> to Delimited File.

  4. Select the Save with Userid/Password check box.

    Warning

    Important

    Save with Userid/Password saves the user ID and password of the database used to create the source data connector. This also applies if the target created requires a user ID and password.

  5. Click OK and then click Next. The Conversion Customization pane appears. This pane allows users to make modifications to a conversion specification. For this tutorial, no changes are needed.
  6. Click Next.
  7. From the Actions menu, select Run to run the conversion. The Execution Status Viewer appears. Its Summary tab displays the conversion specification name (Tutorial1), the database table, delimited file name and location, and the number of records read, inserted, updated, deleted, and discarded. The authors file is written to the directory path specified.
  8. Select the Details tab. Additional information about the conversion specification is appears.
  9. Click Close.

Browse the Source and Target

To verify what was written to the target, compare the data in the Source Data Browser with the data in the Target Data Browser.

  1. Click image2021-8-26_18-30-1.png. The Source Data Browser appears, displaying the contents or the source database.
  2. Click image2021-8-26_18-30-25.png  . The Target Data Browser appears, displaying the contents of the authors.asc file.
  3. Compare the source and target browsers to see that the conversion converted the information accurately.
  4. Click OK on both browser windows to return to the ConverterPro window.

Tutorial 1 is now complete.

 

Tip: For faster searching, add an asterisk to the end of your partial query. Example: cert*

BMC Compuware Topaz Enterprise Data 20.10