Sunday, March 1, 2015

Uploading Excel file data into Database using Talend Open


Uploading Excel file data into Database using Talend Open Studio


Step 1: Create a talend Job. If you don't know. Learn How to create a Talend Job

Step 2: Open the Pallet tab. Select tFileInputExcel and drag it to the work area.

Step 3: Click on the tFileInputExcel component and select the input file path. Here my input file is Organization.xlsx. Click on the check box (i.e. Read excel2007 file format .xlsx) if your excel file is of xlsx type. Otherwise leave it as is. Click on All Sheet or mention Sheet names you want to be inserted. Refer the screen shot.

excel input component image
excel input file



Step 4: Create the database connection.
  • Click on the Metadata link in the Repository panel. It will be expanded. Right click on Db Connections click on the menu Create Connection. Then a window will appear.
  • Fill the name, purpose and description as you want. Give Status as Development. Then click on Next button.
  • Select your DB. My DB is MySql. You may select any other. Provide the connection parameters. Then click on Check. If it's ok click on Finish. Refer the screen shot.

    create db connection image
    creating db connection


Step 5: Now you can rename the tFileInputExcel component to ExcelInput and local to localDB. If you don't know how to rename component. Click on <a href=””>How to rename component of a Talend Job</a>

Step 6: Click on the tMap in Processing menu under Pallet panel and drag it to the work area. Rename it to map.

Step 7: Now select the table where you want to insert the data. Click on localDB. In Component Menu under Basic Setting you will have a Table label. Click on the button next to it a window will appear, it will show you the list of tables available in you db schema . Select your table where you want your data to be inserted from that list. Refer the screen shot. My table name is Organization.

adding tMysqlOutput component image
tMySqlOutput component



select table image
select table

Step 8: Now click on the map. Under component tab click on the button next to Map Editor and add columns in Schema Editors. Refer the screen shot.
 
mapping image
mapping




Step 9: Now Run the job. If you don't know how to run a talend job then click on <a href="">How to run a talend job</a>

result image
result


 

No comments:

Post a Comment