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

[Anonymous]

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

[Anonymous]

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

[TRF]

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.