How To: Replicate a file in CSV-format to a database table

Viewing 1 post (of 1 total)
  • Author
    Posts
  • #14542
    Pallavik
    Keymaster

    How To: Replicate a file in CSV-format to a database table

    Scenario:

    Customer wants to move a csv file into database table

    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

    Excel_sheet_for_file_a.csvNote: 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:

    Step 1: Create a file location under Location Configuration

    A] Right click on location configuration and select New Location

    Screenshot_NewLocation_file_a.csv

     

    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

    Step 2: Create a target with the column names same as that in csv file
    Target_Table_in_Oracle
    Step 3: Add Capture action to the SRC group in created channel ‘filetotable’

    A] Right click on source group SRC and select new action Capture
    NewAction_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.
    Action_Capture_Window
    Step 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
    New_Action_FileFormat
    B] In the pop up window New Action : FileFormat select the options ‘Csv’ and ‘HeaderLine’ for the group source then click on ok
    NewAction_Fileformat_Window
    Step 5: Create Integrate action on the Target group

    A] Right click on the Target group and under New Action select Integrate
    NewAction_Integrate_FiletoDB
    Step 6: Add the Target table from table explorer

    A] Right click on the Tables under the channel name and select Table Explorer
    TableExplore_FiletoDb
    B] This will show a window which will list the target database location which is ‘orcl’ in our case.
    Select_DB_FiletoDB
    C] Double click on orcl to select the table that we created and then select add to add this table to our channel
    SelectTables_FiletoDB
    D] After adding all the actions our channel looks like below
    FiletoDb_Channel
    Step 7: Initialize the channel by clicking on HVR Initialize. This step will create our capture and integrate jobs
    HVRInitialize_FiletoDB
    JobCreation_FiletoDb
    Step 8: Start the scheduler to start the loading
    Scheduler_FiletoDb
    If you look at the table in your oracle database you will see that it is populated
    Target_Table_FiletoDb

Viewing 1 post (of 1 total)
  • The forum ‘How to’ is closed to new topics and replies.

© 2020 HVR

Test drive Contact us