[SOLVED] Convert Below Source File in Notepad++ and also in Informatica. See the Given Source Table and Output Table I Want

Issue

Source Data -

Cust_Id,Cust_Name,Cust_Address,Cust_Salary
1,Name1,Address,12,Road,40,10000
2,Name2,Addressline,2,15,20000

1. First Scenario, I Want to Convert this Flat File Like Below and Send to the Target Table (Oracle) By using Informatica Powercenter.
2. Second Scenario, I Want to Replace First 2 Columns Commas (Cust_Id, CustName) with Pipe Delimited and Keep the Commas in Address Column Value how to Replace only First Two Column Commas in Notepad++ .
3. Third Scenario, I Want to Convert this Same Source Flat File And Implement Same Logic as like Second Scenario in Unix.
4. Fourth Scenario, Write Query in Oracle for this Scenario and Display Result in Separate, Separate Columns. For Ex: - Desired Output in Oracle

Cust_Id	Cust_Name	Address	Salary
1	Name1	Address,12,Road,40	10000

Desired output in Informatica and Notepad ++

Cust_Id,Cust_Name,Cust_Address|Cust_Salary
1|Name1|Address,12,Road,40|10000
2|Name2|Addressline,2,15|20000

Solution

First Scenario Answer (Informatica Powercenter)

Expression Transformation
in_Input_Data (nstring)
out_Cust_Id (integer) = SUBSTR (in_Input_Data,1, INSTR (in_Input_Data,',')-1)
var_Length_Cust_Name (integer) = INSTR(in_Input_Data,',',1, 2) - INSTR(in_Input_Data,',') -1
out_Cust_Name (nstring) =  SUBSTR(in_Input_Data,INSTR(in_Input_Data,',', 1)+1,v_Length_Cust_Name)
out_Cust_Address (nstring) =SUBSTR(in_Input_Data,INSTR(in_Input_Data,',', 1, 2)+1, INSTR(in_Input_Data,',', -1, 1)- INSTR (in_Input_Data, ',', 1, 2)-1)
Cust_Salary (Double) = SUBSTR(in_Input_Data,INSTR(in_Input_Data,',',-1,1)+1)

Link all this Output Port to Target.

Answered By - Vivek Patil

Answer Checked By - Candace Johnson (WPSolving Volunteer)