Talend Datatype Conversion

In this chapter, we explore the Talend tConvertType element to perform datatype conversions. In real-time, we won’t get the data in the desired data type so we have to use the Talend datatype conversation field to convert them as per the business requirements. For example, we might get the date and time in string format or sales in string format. In such a situation, we use this Talend datatype conversation transformation.

To demonstrate this Talend Datatype conversation field, we are using the below shown text file.

Talend datatype conversion Example

As we all know, we need tFileInputDelimited field to work with text files. So, drag the same and select the text file from the file system Here, we are using the file directly, but you have the option to use the Repository file.

Here, you can see the Schema of all the columns.

Next, drag and drop the Talend tConvertType to the job design area. As you can see from the tooltip, tConvertType converts one Talend Java type to another.

Please connect the main row to Talend tConvertType. Next, click on the Edit Schema button to change the data types of the required columns. If you don’t see the source column then click on the Sync columns button.

Let me change the EmpID to integer, yearly Income and Sales to Double, and Hire Date to Date data type. Don’t forget to assign appropriate length, precision, and date format.

Please checkmark the other two options Auto Cast, and Set empty values to Null before converting (important).

Let us create an empty table in SQL Server. Or you can select the existing table.

CREATE TABLE [dbo].[Talend_ConvertType](
  EmpID int NOT NULL,
  FirstName nvarchar(255) NULL,
  LastName nvarchar(255) NULL,
  Education nvarchar(255) NULL,
  Occupation nvarchar(255) NULL,
  YearlyIncome float NULL,
  Sales float NULL,
  HireDate datetime NULL
)

We used tDBOutput to connect to SQL Server. Here, we are using the repository connection. Next, we selected the newly created table, i.e., Talend_ConvertType.

Click on the Edit Schema to check the input and output columns.

Let us run the Talend datatype conversion job.

Within the Management Studio, you can see the data.

This time, we unchecked the Auto Cast option and did the manual casting of the columns.

Again run the Talend datatype conversion job.