How to store part of file name in MySQL table using Talend

Six Stars

How to store part of file name in MySQL table using Talend

Hello Folks,

 

I have a files something like below,

unicsv_unibill_BrandEnergy_3667_20180201_IIQ473894_2182018112138PM_1.txt

unicsv_unibill_Envestnet_927133_20180131_IIQ473100_214201823011PM_1.txt

etc.,

And I want to store particular string value in the above case it is BrandEnergy and Envestnet - in MySQL database table for each file.

For example first file will have 10 records and in the my table I have column called 'Client' and 'BrandEnergy' value should be stored in all 10 records. 

 

Can any one help with this please?

 

Regards,

Deepak

Community Manager

Re: How to store part of file name in MySQL table using Talend

Hi Deepak1

Using a tfileList to iterate each file, there is a global variable stores the file name, extract the client value from the file name and store it to a context variable for used later, the job design looks like:

tFileList--iterate--tJava--tFileInputDelimited--main--tMap---tMysqlOutput

 

on tJava, extract the client value from the file name and store it to a context variable, eg:

String file_name=((String)globalMap.get("tFileList_1_CURRENT_FILE"));

String temp_string=foo.replace("unicsv_unibill_", "");
context.client_name=temp_string.substring(0, temp_string.indexOf('_'));

 

tFileInputDelimited: read the records from the file.

tMap: add a new column on the output table, and set its expression as: context.client_name

 

Regards

Shong

----------------------------------------------------------
Talend | Data Agility for Modern Business
Fifteen Stars TRF
Fifteen Stars

Re: How to store part of file name in MySQL table using Talend

Hi,

Connect a tJava after the tFileList you us eto iterate over the file list.
Add a field called accountName (for example) to your output schema in this component.
Use the following code:

int start = (String)globalMap.get("tFileList_1_CURRENT_FILE").indexOf("_")+1;
start = (String)globalMap.get("tFileList_1_CURRENT_FILE").indexOf("_", start)+1; 
int end = input_row.filename.indexOf("_", start);
globalMap.put("client", (String)globalMap.get("tFileList_1_CURRENT_FILE").substring(start, end));

Now you are able to reuse this global variable to populate the client column of your table.

You just have to a new field to the schema after the tFileInputDelimited using a tJavaRow or a traditional tMap.

 

Hope this help.


TRF

What’s New for Talend Spring ’19

Watch the recorded webinar!

Watch Now

Tutorial

Introduction to Talend Open Studio for Data Integration.

Watch

Downloads and Trials

Test drive Talend's enterprise products.

Downloads

Definitive Guide to Data Integration

Practical steps to developing your data integration strategy.

Download