Need Inputs-while inserting file from s3 to oracle

Six Stars

Need Inputs-while inserting file from s3 to oracle

Hi Everyone,

At the very last moment i am stuck with an unexpected error.

I have like 100 of files.10-20 have files names like abc786.csv,bcd786.csv,efg786.csv etc...

786 the code of the files which is there after name of every file.

now,I have another set of files with the same name list12312786.csv

list5678786.csv,

list987645786.csv etc..

If you notice the list files ,you will get 786.csv as suffix here as well.

 

So now,i have to enter all files with the change of name: STG_abc,

STG_bcd,

STG_list12312,

STG_5678 etc...removing 786.csv and adding STG_ at prefix.

I used an expression in my tOracleoutput component in table name place:

 

"STG_"+StringHandling.EREPLACE("<FileName>","786.csv","")

 

which will add STG_ as prefix and remove "786.csv" from suffix.

 

It was working fine while i was testing for 2-3 file.But Today when i run my job for entire file set.It did not work for "List files"

It is giving me the name Smiley FrustratedTG_List5678.csv, (removing 786 but not removing csv)

 

As far as i know the expression  ("STG_"+StringHandling.EREPLACE(context.MAPPING_FILE,"786.csv","") ) should work fine but now i got stuck in this.

Could you please help me in resolving the issue.

 

Regards,

Mohit

 

 

 


Accepted Solutions
Six Stars

Re: Need Inputs-while inserting file from s3 to oracle

Hi Nikhil,

 

I have given hardcoded value and it ran fine but for Context.Mapping File it was giving issue.

but Now issue has been resolved.

I modified your command like this :

 

"STG_"+context.MAPPING_FILE.replaceAll("786","").replaceAll(".csv","")

 

and it ran successfully.

Thanks for your help Smiley Happy

 

Regards,

Mohit

View solution in original post


All Replies
Employee

Re: Need Inputs-while inserting file from s3 to oracle

Hi Mohit,

 

     If I understood your query correctly, you want totrim the last 6 characters ie "786.csv".

 

      Why don't you using a replaceall as below.

 

"STG_"+row1.input.replaceAll("786.csv","") 

      Please see the output below for sample record.

image.png

 

image.png

 

Another item I would say to check is whether you are picking the file extension also to the context variable when you are reading the file. That could be another reason why .csv is still present. Or you might be adding .csv later in your flow. 

 

 

If the answer has helped you, could you please mark the topic as resolved? Kudos are also welcome :-)

 

Warm Regards,

 

Nikhil Thampi

Six Stars

Re: Need Inputs-while inserting file from s3 to oracle

Hi Nikhil,

 

Unfortunately,it did not work.

 

System.out.println("STG_"+context.MAPPING_FILE.replaceAll("786.csv",""));

 

It is not removing .csv.

 

I am not using rowgenerator.I am just taking one file and try to remove 786.csv.

 

It is working for hardcoded value but it is not working when i pass the file name as Context.FileName

 

regards,

Mohit

Employee

Re: Need Inputs-while inserting file from s3 to oracle

Hi,

 

     It is interesting. Could you please print the value of context.MAPPING_FILE and also the below statement side by side.

 

System.out.println("STG_"+context.MAPPING_FILE.replaceAll("786.csv","")) 

 

    Lets see the output. Meanwhile I will check whether the full stop is doing any special character conversion.

 

Warm Regards,

 

Nikhil Thampi

 

 

Six Stars

Re: Need Inputs-while inserting file from s3 to oracle

Hi Nikhil,

 

I have given hardcoded value and it ran fine but for Context.Mapping File it was giving issue.

but Now issue has been resolved.

I modified your command like this :

 

"STG_"+context.MAPPING_FILE.replaceAll("786","").replaceAll(".csv","")

 

and it ran successfully.

Thanks for your help Smiley Happy

 

Regards,

Mohit

View solution in original post

Employee

Re: Need Inputs-while inserting file from s3 to oracle

Good work Mohit.

 

Could you please mark your last post as answer to the query and close it?

 

Warm Regards,

 

Nikhil Thampi

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 2

Part 2 of a series on Context Variables

Blog

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