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
Sixteen Stars TRF
Sixteen 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

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 1

Learn how to do cool things with Context Variables

Blog

Migrate Data from one Database to another with one Job using the Dynamic Schema

Find out how to migrate from one database to another using the Dynamic schema

Blog

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog