Talend Rejected Rows

In this Talend section, we discuss the Talend rejected rows or capture the failed records into a table or text file. It is crucial to capture the rows that are failed or rejected when the Talend job runs. We can use that log to review and cleanse the data for further action.

In all our Talend previous articles, we always showed you how to load successfully processed data into Tables or text files. We have never revealed you about the Talend rejected rows or the Error logs, and we cover them here!.

To demonstrate the Talend rejected rows, we used the below text file.

Talend Rejected Rows 1

Talend Rejected Rows Example

We used tDBConnection and tDBCommit to establish and close the connection with SQL Database. Next, we used the tFileDelimitedInput to load the above text file into the Talend. Please click on the Edit Schema to add the Column names or use the existing File delimited schema (if any).

Talend Rejected Rows 2

Here, we used the tLogRow and ran this job to see the data in the text file.

Talend Rejected Rows 3

We removed/replaced the tLogRow with tDBOutput because we want to load this text file into the database table. Next, we used the existing connection and placed the Table name.

Talend Rejected Rows 4

Click on the Edit Schema to see or change the output columns. If you notice the lengths of the DBOutput columns, they are much shorter to accommodate the text file data. If you are creating a table, then you might change the length to store all the data. However, when we are using an existing table, we have no option to alter the table structure and consider this is our scenario.

Talend Rejected Rows 5

Once you run the Talend Rejected Rows job, you can see the Data truncation message six times.

Talend Rejected Rows 6

Let me open the Management Studio, and you can see it has four records, and they meet the data length.

Talend Rejected Rows 7

All this time, we are selecting the Row Main option from the context menu. However, we have another option, i.e., Row Rejects, and this is where we capture the Talend Rejected Rows.

Talend Rejected Rows 8

Next, we used tMap to process those rejected rows.

Talend Rejected Rows 9

Then we used tLogRow to display those rejected rows in table format.

Talend Rejected Rows 10

Let me open the tMap editor.

Talend Rejected Rows 11

We added all the rows from the tDBOutput to tMap. If you observe, it has two extra columns error code (error number) and error message.

Talend Rejected Rows 12

Once you run this Talend tMap job, you can see all the rejected rows along with error code and corresponding error message.

Talend Rejected Rows 13

We used the tFileDelimitedOutput to save these rejected rows in a text file. Here, we used a comma as the column separator.

Talend Rejected Rows 14

Now, you can see all the Talend rejected rows, error code, and error message in a text file.

Talend Rejected Rows 15

There are some situations where we have to save the rejected rows of all the Talend jobs in a single file or table. In that scenario, we have to use the same schema for all the tables (with different columns). In this situation, we use a single column to hold all the information (columns) of a particular table, second column for error code, and last column for the error message.

Let me open the tMap to concat all the columns in a table and wrote the following expression. It means EmpID of string type stores all the Employees information, and each column separate by | symbol.

Talend Rejected Rows 16

We store these records in the above-specified text file. So, let me alter the schema and remove the existing text file from the system.

Talend Rejected Rows 17

Now you can see that our File has three columns, and the EmpID column has all the Employee table columns.

Talend Rejected Rows 18