Introduction

Replicate a file in CSV-format to a database table

Environment

HVR

Overview

This article will describe how to create a channel to move a csv file ‘file_a.csv’ from location to a table in database. Although it is possible to load a file into a table, it is not possible to perform CDC from a file to a table.

Prerequisites

1. Oracle database location ‘orcl’ is already defined in location configuration
2. File ‘file_a.csv’ is present on local machine in directory C: /Users /pkhadamkar /Downloads /hvr_demo37 /fdemo
3. Channel ‘filetotable’ is already created with locations SRC and TGT. SRC pointing to ‘file1’ and TGT pointing to ‘orcl’ database. Note: File1 location will be created in Step 1 below.
4. Contents of the csv file are as below

Note: the file, “file_a.csv” must be saved by an application like Excel which can save the file in CSV format. In other words, the KB is written assuming the file is in CSV format, not TXT format.

Steps

1. Create a file location under Location Configuration.

   A. Right click on location configuration and select New Location.

   >

   B. In the opened New location window enter the location name as ‘file1’ and select option ‘File /FTP /Sharepoint’ under class section. This step will enable the below options for File location.

   • Protocol : Leave it as default ‘Local’ which indicates that the file is present on the localhost.
   • Directory: Click on the browser button next to this field to select the directory in which the csv file is present. In this case it is C: /Users /pkhadamkar /Downloads /hvr_demo37 /fdemo.

   

   C] Click on test connection to see if it is working, if it is then create the location by clicking ok.

2. Create a target with the column names same as that in csv file.

   

3. Add Capture action to the SRC group in created channel ‘filetotable’.

   A. Right click on source group SRC and select new action Capture

   

   B. In the New Action: Capture Window select Pattern to specify the name of the file. This parameter means that only capture a file that meets certain pattern. In our case we give file_a.csv. For example if you mention ‘*.csv’, hvr will capture any csv file.

   

4. Create FileFormat action on the source group to define the format of the file

   A. Right click on the source group and select FileFormat under New action

   

   B. In the pop up window New Action : FileFormat select the options ‘Csv’ and ‘HeaderLine’ for the group source then click on ok

   

5. Create Integrate action on the Target group.

   A. Right click on the Target group and under New Action select Integrate

   >

   

6. Add the Target table from table explorer

   A. Right click on the Tables under the channel name and select Table Explorer

   

   B. This will show a window which will list the target database location which is ‘orcl’ in our case.

   

   C. Double click on orcl to select the table that we created and then select add to add this table to our channel

   

   D. After adding all the actions our channel looks like below

   

7. Initialize the channel by clicking on HVR Initialize. This step will create our capture and integrate jobs

   

   >

8. Start the scheduler to start the loading

   >

   If you look at the table in your oracle database you will see that it is populated