CODEWITHSUNDEEP
etlinformatica-powercenter
Sajjad
Sajjad

Reputation: 57

How to extract multiple values as multiple column data from filename by Informatica PowerCenter?

I am very new to Informatica PowerCenter, Just started learning. Looking for help. My requirement is : I have to extract data from flat file(CSV file) and store the data into Oracle Table. Some of the column value of the target table should be coming from extracting file name.

For example: My Target Table is like below:

USER_ID Program_Code Program_Desc Visit Date Term

EACRP00127 ER Special Visits 08/02/2015 Aug 2015

My input filename is: Aug 2015 ER Special Visits EACRP00127.csv

From this FileName I have to extract "AUG 2015" as Term, "ER Special Visits" as Program_Desc and "EACRP00127" as Program_Code along with some other fields from the CSV file. I have found one solution using "Currently Processed Filename". But with this I am able to get one single value from filename. how can I extract 3 values from the filename and store in the target table? Looking for some shed of light towards solution. Thank you.

Upvotes: 1

Views: 383

Answers (1)

Koushik Roy
Koushik Roy

Reputation: 7387

Using expression transformation you can create three output values from Currently Processed Filename column. So you get the file name from SQ using this field 'Currently Processed Filename'. Then you can substring the whole string to get what you want.

input/output = Currently Processed Filename
o_Term = substr(Currently Processed Filename,1,9)
o_Program_Desc = substr(Currently Processed Filename,10,18)
o_Program_Code = substr(Currently Processed Filename,28,11)

Upvotes: 2

Related Questions