Talend Load Data from Text File into Database

In this Talend chapter, we show how to load data from a text file into a database table. In Talend, you can load text file data into the database table in two ways.

  • Drag and drop the tFileInputDelimited and browse the Text file, and create a schema (or column names) for that text file.
  • Create metadata for the text file and use that File Delimited metadata.

In this Talend example, we use the already existing File delimited metadata as a text file source. The following image shows the Text file data.

Talend Load Data from Text File to Database 0

Talend Load Text File Data into a Database Example

First, drag and drop the EmpInfo from the File Delimited folder into the Talend Job design. From the below screenshot, you can see that the file Component properties are using the Repository values.

Talend Load Data from Text File to Database 1

Next, drag and drop the tDBConnection, tDBCommit, and tDBOutput from Palette to Job design space. Here, you can use only tDBOutput also. It Works, but the standard way to separate the opening and closing connections help in many ways.

Talend Load Data from Text File to Database 2

Please select the tDBConnection, and select the type as Microsoft SQL Server, and select the Property type as Repository and select the SQL DB connection form repository. Or use Global context groups to fill the properties.

Talend Load Data from Text File to Database 3

Next, we selected the SQL as a Database and chose the tDBConnection1 as the Component List for the tDBCommit. Please checkmark the Close Connection option to close the connection once it reaches this field.

Talend Load Data from Text File to Database 4

Talend tDBOutput Configuration

Within the Talend tDBOutput Components tab, we changed the Database like SQL Server and checkmark use an existing connection option. Next, we connected all the fields, as shown below. I suggest you refer to Connect Talend to DB using the Context Group article.

Talend Load Data from Text File to Database 5

First, we selected the DBConnection1 as the Component List. Next, click the Browse button to select the existing SQL Table (if any). We don’t have any, so we typed a new table name called Talend_EmpInfo.

Talend Load Data from Text File to Database 6

Action on table: The action that you want to perform on the Talend_EmpInfo Table. As you can see, it has many options, and by the name, you can understand their purpose. Please click the hyperlinks to know about Create Table, clear means deleting all the records, and Truncate.

Talend Load Data from Text File to Database 7

Turn on Identity Insert: By default, Tables don’t allow you to insert Identity values. So, check this option to enable them.

Action on Data: Do you want to Insert, Update, Delete, Insert or Update, Insert if Not exist, etc. Choose the action as per your need. For example, if you want to insert if there is no such record, select the Last option.

Talend Load Data from Text File to Database 8

By clicking the Edit Schema button, the following window will open. Please check and change the DB types, Lengths, and precession of the tDBOutput columns.

Talend Load Data from Text File to Database 9

We have changed the lengths of all the tDBOutput columns.

Talend Load Data from Text File to Database 10

Here, we have chosen to create a table if it does not exist, and Insert as the Action on data.

Talend Load Data from Text File to Database 11

Let us run the Talend load text file data into database job.

Talend Load Data from Text File to Database 12

Let me open the Management Studio to check whether the Talend job loaded the text file data into Database.

Talend Load Data from Text File to Database 13

I think I forgot one more property. So, let me go to the tDBOutput Components tab.

  • Specify identity Field: Please check mark this option to mention the column name that has Identity set On. 
  • Identity field: Choose the Column name—for instance, EmpID. Next, you have to assign the integer value of starting value and the increment value (Step value).
Talend Load Data from Text File to Database 14

Within the Schema page, you can assign any column as Key column by simply checkmark the Key option beside the Column name.

Talend Load Data from Text File to Database 15

Let me undo all the change that we made, and change the Action to Insert If not exit and Run. Well! Nothing will happen because the table already has those records.

Talend Load Data from Text File to Database 16