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 :)
