Split Flat File String into multiple columns in Informatica

Question

I have a flat file (csv) with 1 column (ColumnA) having data in the format ==> 100-000500-10-40000-000-200-600-000

I need it split into 8 columns (values adjacent to the hyphens) and concatenate the 2,4 and 5 splitted sub values. How do I do this in an expression transformation? I have tried the following but didnt help, table is loading these fields as blank.

Variable port 1: SUBSTR(ColumnA,3,6) Variable port 2: SUBSTR(ColumnA,12,5) Variable port 3: SUBSTR(ColumnA,18,3)

Then I have made output ports of each 3 of them V_ACCOUNT_SEG2_CODE, V_ACCOUNT_SEG4_CODE, V_ACCOUNT_SEG5_CODE and then concatenated like below:

Final Variable Port: RTRIM(V_ACCOUNT_SEG2_CODE||'-'||V_ACCOUNT_SEG4_CODE||'-'||V_ACCOUNT_SEG5_CODE,'-').

Can someone advice what is wrong with the code and what can be the correct working code? I am getting data from ColumnA (csv flat file) to my target table (Oracle) but these additional columns are coming as null.

My Expression Transformation Here

Answer 1

More efficient REGEX:

v_col1:= REG_EXTRACT(data_col,'([^-]+)-([^-])-([^-]+)',1)
v_col2:= REG_EXTRACT(data_col,'([^-]+)-([^-])-([^-]+)',2)
v_col3:= REG_EXTRACT(data_col,'([^-]+)-([^-])-([^-]+)',3)
...

Answer 2

You need to use combination of SUBSTR and INSTR. You shouldn't use hard code values since they can change depending on your data. Probably that's what is happening in your mapping.
v_col1= SUBSTR (data_col,1,instr(data_col,'-')-1 ) v_col2 = SUBSTR (data_col,instr(data_col,'-')+1, instr(data_col,'-',1,2)-1 ) v_col3 = SUBSTR (data_col,instr(data_col,'-',1,2)+1,instr(data_col,'-',1,3)-1 ) ...
Alternately you can use REGEX as well like below. But issue is this works ONLY when you have a data with 3 '-' like 'abc-defg-hij'. Any other combination, split wont work.

v_col1:= REG_EXTRACT(data_col,'([^-]+)-+([^-])-+([^-]+)',1) v_col2:= REG_EXTRACT(data_col,'([^-]+)-+([^-])-+([^-]+)',2) v_col3:= REG_EXTRACT(data_col,'([^-]+)-+([^-])-+([^-]+)',3) ... Alternately you can load the file data into a temp oracle table and use regex_substr() to split them.

Answer 3

You can create the Source Transformation for the Flat Files and use the hyphen as delimiter. This will create the columns for you. All you'd need to do then is just concatenate the appropriate columns in Expression Transformation.