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.

Source 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.

Talend Datatype Conversion 3

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.

Talend Datatype Conversion 4

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.

Talend Datatype Conversion 7

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

Talend tConvertType 8

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

CREATE TABLE [dbo].[Talend_ConvertType](
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.

Talend tConvertType 11

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

Talend tConvertType 12

Let us run the Talend datatype conversion job.

Run Talend Datatype Conversion Job

Within the Management Studio, you can see the data.

Talend tConvertType 14

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

Talend Datatype Conversion Manually

Again run the Talend datatype conversion job.

Talend tConvertType 16