Create a table in PostgreSQL from Talend ETL using excel sheet

Create a table in PostgreSQL from Talend ETL

Environment:

Talend Version : 5.1.3

PostgreSQL : 9.2

Excel sheet with few columns records are having null values.

Step 1:

Right Click on Job Designs -> Create job >

Name: dummytable

Give purpose & Discription of the job

 Step 2:

Left side from the Repository, go to Metadata

File Excel -> Right click -> Create new Excel

There will be 4 steps you need to complete.

i)                    Give Name, purpose, Description etc.

ii)                   Give the location of the excel file (Click on Browser). In File Viewer and Sheets setting, Set sheet parameters. I,e. Select the number of sheets.

iii)                 Encodinig UTF-8, if your excel sheet having header then Tick Header & fill it as 1. Tick Set heading row as column names and Click on Refreshing.  Click onfinish

iv)                 Click on finish.

Step 3:

From the databases(right side from the palette) click on postgreSQL-> Then click on postgresqlOutput component and then drag on to the panel.

Click on component.

Give all the details as shown in figure.

Host: localhost

Port: 6062( default port number of postgreSQL is 5432)

Database: iReportDummy ( You need to create database before you start designing the job)

Schema : public

Username: postgres

Password: postgres

Table : issuedummytable

Action on table : Create table if not exist (See all the options in dropdown).

Action on data : insert (see all the options in dropdown)

Step 4:

·         From the palette of components click on Processing -> select tMap component and drag it to job design. (place in between  the excel sheet component and postgresqlOutput component as shown in  figure 1.

·         Double click on “tMap” component.

 

Click on “auto Map”.  You can find the mapping lines as shown in figure.

NOTE:

There are few columns which are having null values in Excel sheet.

You need to use the below expression to get all the data from excel sheet.

From the right side, click on the column(click  small react angled one), then the expression editor will appear.

There you need to write the condition.

·         Write the similar conditions for all the output columns for which in excel sheet is having null values.

·         row1.placement_count.equals("NULL") == true ? null: new BigDecimal(row1.placement_count)  

 

Step 5:

Save the job and run it.

Check whether the table created correctly or not in postgreSQL.

Thank you :)