Split Flat File String into multiple columns in Informatica

0
votes

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

3
splitstring-concatenationinformaticainformatica-powercenterflatfilesource
If Column A is the only one in the CSV, you can set the separator of the CSV to hyphens and Informatica will parse it for you in 8 columns. If it's not the only column, you could do it in 2 mappings : one to extract that single column in another CSV, and the second with hyphens as separator. Another solution would be a Shell command (assuming running on Unix) that changes hyphens to the separator of the file before the mapping starts : tr '-' ',' – Mickaël Bucas

3 Answers

1
votes

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.

0
votes

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.

0
votes

More efficient REGEX:

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