Load OracleDatabase Table data From Filedata

 Load Database table  from CSV files that contains the following fields:

•          Employeid 
•          Employename
•        Dateofjoin
•         Salary

                                  

The file would looks like this:

 

We want to load this file into a OracleDatabaseTable  with a schema as follows:

 Here are the steps we will take to build our Talend Open Studio solution:

Step 1:  Open Talend

Open Talend and create or open an existing project

Step 2: Create a new job

Right click on Job Designs in the Repository window and select “Create job”

Name the job “item_load”

    Enter the name of JOb

Step 3: Create a File Delimited repository element

Now we need to create a repository item for our  Oracle databaseTable. To do this click the arrow next to “Metadata”  in the Repository window and right click on “File delimited” and select “Create file delimited”

Enter a name for your example file schema and click next:

.

 

Select your example file that we saw earlier (or use your own) by clicking the “Browse” button.

Click Next

 

 

On the next screen select field separator as comma (as per your file data) and select the checkbox for “Set heading row as column names”, then click “Refresh Preview”. Click Next.

Talend has now generated an estimated schema, review this schema and make any changes as you would like, then click Finish.

Step 5:  Design your job

In this step we are going to design our job to connect our CSV file to our OracleDatabaseTable.

Open the job we created in Step 2 by double clicking the name of the job under Job Designs in the Repository window.

In the palette window on the right hand side type “tFileinputDelimited” into the search box, then drag and drop the component “tFileInputDelimited” into the job window in the center of the screen

  Select the tFileInputDelimited Component in the job window and then select the “Component” tab near the bottom middle of the screen.

Click the drop down box next to “Property type” and select “Repository”

Then click the button with three dots that appears and select the delimited file from the repository that we created in Step 3.

Select a tMap component from the palette on the right hand side of the screen and drag it into the job design window

 Right click on the tFileinputDelimited component, select Row->Main and connect a row to the tMap component.

 

 

Select a tOracleoutput  component from the Palette and drag it over to the job design window.

                                         

Click on the tOracleoutput  component to select it, then navigate to the Component tab in the lower middle of the screen. Fill all blocks

Connection Type:

DB Version:             

Host:

Port:

Database:

Oracle schema:

Username:

Password:

Action on Table: 

Next right click the tMap component, Select “Row” then “New Output”.

Connect this new output to the tOracleoutput component

Name the output row- “output1″

Now your tMap is connected to the tOracleoutput component, double click the tMap component to open up the tMap editor.

Click and drag the data fields from the “row1″ panel to the left of the screen to the corresponding “output1″ data fields. This tMap editor enables you to map to fields from the CSV input to the correct output fields in tOracleoutput data fields.

Click “Apply” then “OK” to save the changes and return to the Job Design window

Now your job design is complete and we just need to run the job to load the file into OracleDatabaseTable.

  

Step 6: Run your Job

In the group of tabs in the lower middle of the screen, select the “Run” tab

 

Under Execution, click the “Run” button

Your job will load and then run and you will see how many rows were processed in the job design window

Congratulations! You have now loaded your one file into your OracledatabaseTable.