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 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.
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.
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.
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 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.
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.
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.
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.
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.
We have changed the lengths of all the tDBOutput columns.
Here, we have chosen to create a table if it does not exist, and Insert as the Action on data.
Let us run the Talend load text file data into database job.
Let me open the Management Studio to check whether the Talend job loaded the text file data into Database.
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).
Within the Schema page, you can assign any column as Key column by simply checkmark the Key option beside the Column name.
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.
