Replicating CSV file to Database Table

This example describes how to replicate a CSV file from a source location to a database table on a target location using HVR.

Prerequisites

1. A CSV file resides on your local machine. Contents of the CSV file are as below:

   

2. Create a table in a target database, for example, as follows:

   SQL > create table test_csv (c1 int, c2 varchar2(20));
   

   The base name of the columns in the database should match the columns in the CSV file.

Replication Steps

For the purpose of this example, it is assumed that the hub database already exists and a target database location tgt has been configured. For more information on how to register the HVR Hub and set up location configuration, see, for example, section Quick Start for HVR - Oracle.

1. Next, add a file directory to the source location: in HVR GUI, right-click Location Configuration and select New Location.

   

2. In the New Location window, specify the location name src.

3. In the Class pane, select File/FTP/Sharepoint.

4. In this case, the XML file resides on a local machine, thus, select Local in the Protocol field. Alternatively, connect to a remote file location using one of the available file protocols (e.g. FTP, SFTP or WebDAV).

5. In the Directory field, select the directory, in which the CSV file will be later uploaded.

   

6. Click Test Connection to verify the connection and then click OK.

7. Now, you need to create a channel: right-click Channel Definitions and select New Channel. Specify the channel name, e.g. csv_to_tbl, and click OK.

8. Expand the channel node, right-click Location Groups and select New Group. Specify the name of the source group, e.g. SRCGRP, select src under the Group Membership.

9. Right-click Location Groups and select New Group. Specify the name of the target group, e.g. TGTGRP, select tgt under the Group Membership.

10. Right-click the Tables node, select Table Explorer. In the Table Explorer window, click Connect. Select table test_csv and click Add. Click OK in the HVR Table Name window and close the Table Explorer window.

    

11. Add the Capture action to the source group: right-click source group SRCGRP, select New Action ▶ Capture.

12. In the New Action: Capture window, select Pattern and specify the name of the CSV file. This parameter controls to capture only the file that meets certain pattern. For example, if you specify '*.csv', HVR will capture all CVS files. Click OK.
    

13. Right-click source group SRCGRP, select New Action ▶ FileFormat.

14. In the New Action: FileFormat window, select options /Csv and /HeaderLine and click OK.

    

15. Add the Integrate action to the target group: right-click target group TGTGRP, select New Action ▶Integrate.

16. In the New Action: Integrate window, select the required table and click OK.

    

17. Now that the channel is configured, right-click the channel and select HVR Initialize. In the HVR Initialize window, click Initialize. HVR will create two replications jobs under the Scheduler node.

18. Right-click the Scheduler node and select Start.

19. Right-click the Scheduler node, navigate to All Jobs in System and click Start.

20. As shown in the screenshot below, the Capture and Integrate jobs are in the RUNNING state.
    

Test Replication

Add the CSV file to the directory defined on the source location. Right-click the Scheduler node and select View Log that will display the output of the Capture and Integrate jobs running.

If you look at the table, you will see that it got populated with the corresponding values from the CSV file.