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

Desired output in Informatica & Notepad

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

CodePudding user response：

use an expression transformation to create 3 columns from one column. Read the data in a single column.

in_inp_data
out_Cust_Id = substr(in_inp_data,1, instr(in_inp_data,','))
var_length_cust_name= instr(in_inp_data,',',2) - instr(in_inp_data,',') -1
out_Cust_name =  substr(in_inp_data,instr(in_inp_data,',') 2,var_length_cust_name)
out_Cust_Address = substr(in_inp_data, instr(in_inp_data,',',2) 1)
Cust_Salary = substr(in_inp_data, instr(in_inp_data,',',-1))

Explanation - 1,Name1,Address,12,Road,40,10000 substr(in_inp_data,1, instr(in_inp_data,',')) - this produces 1 because it cuts first character till first comma which is 1.
substr(in_inp_data,instr(in_inp_data,',') 2,var_length_cust_name) - this produces Name1 because it cuts string after first comma upto length of name.

substr(in_inp_data, instr(in_inp_data,',',-1)) - this extracts last string before first comma from the end of string which is salary.

CodePudding user response：

Fourth Scenario Answer (Oracle/SQL Developer)

Table Structure

Create Table Cust_Data
(Input_Data Varchar2(255));
            
Insert Into Cust_Data Values ('1,Name1,Address,12,Road,40,10000');
Insert Into Cust_Data Values ('2,Name2,Addressline,2,15,20000');
            
Select * From Cust_Data;

Syntax

Select
  Substr (Input_Data, 1,
  Instr(Input_Data,',')-1) Cust_Id,
  Substr (Input_Data,
  Instr (Input_Data, ',', 1)  1,
  Instr(Input_Data,',', 1, 2) - Instr(Input_Data,',')-1) Cust_Name,
  Substr (Input_Data,
  Instr (Input_Data, ',', 1, 2)  1,
  Instr(Input_Data,',', -1, 1)- Instr (Input_Data, ',', 1, 2)-1) Cust_Address,
  Substr (Input_Data,
  Instr (Input_Data, ',', -1, 1)  1 ) Salary
From Cust_Data;

CodePudding user response：

First Scenario Answer (Informatica Powercenter)

Expression Transformation
in_Input_Data (nstring)
out_Cust_Id (integer) = SUBSTR (in_Input_Data,1, INSTR (in_Input_Data,','))
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)
Cust_Salary (Double) = SUBSTR(in_Input_Data,INSTR(in_Input_Data,',',-1,1) 1)

Link all this Output Port to Target.

CodePudding user response：

Second Scenario Answer (Notepad )

1. Open Notepad
2. Press Ctrl H Button.
3. In Find What Zone: ^(. ?),(. ?),(. ),(. ?)$
4. In Replace With Zone: $1|$2|$3|$4
5. Select Wrap Around.
6. Select Regular Expression.
7. And Click Replace All.