Talend Load Data from Text File into Database

his Talend chapter will 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 to 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 Load Data from Text File into Database example, we use the already existing File delimited metadata as a text file source. The following image shows the Text file data.

Text File 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.

Read the txt into the Workflow

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

Please select the tDBConnection, select the type as Microsoft SQL Server, select the Property type as Repository, and select the SQL DB connection from the repository. Or use Talend Global context groups to fill the properties to Load Data from the Text File into the Database.

Add tDBConnection, tDBCommit, and tDBOutput to the Job design

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 to Load Data from Text File into Database

Within the Talend tDBOutput Components tab, we changed the Database to 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.

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 the table: The action that you want to perform on the Talend_EmpInfo Table. As you can see, it has many options; by the name, you can understand their purpose. Please click the hyperlinks to learn about Create Table, clear means deleting all the records, and Truncate.

Turn on Identity Insert: By default, Tables don’t allow you to insert Identity values. So, check this Talend option to enable them while loading data from the Text File into the Database.

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.

Action ON Table options in Talend Load Data from Text File to Database

By clicking the Edit Schema button beside the Sync Columns button, the following window will open. Please check and change the DB types, Lengths, and precession of the tDBOutput columns. 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 it as the Action on data.

Talend Load Data from Text File to Database 11

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

Run the Talend Load Data from Text File to Database Job

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

Result Table

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 must assign the integer value of the 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 a 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 changes that we made, and change the Action option from Insert to Insert If not exit and Run. Well! Nothing will happen because the table already has those records.