Single Source Qualifier column into multiple columns

Question

I have a comma separated value (csv) file which needs to be loaded into a table, using Informatica. First column contains a concatenated value, concatenated by a '~', which needs to be saved into 7 different columns.

Is there any way to do it?

Answer 1

There are four ways i think you can do but all has pros and cons.

1. First read using a Source Qualifier with ~ as delimiter and write it into a csv file. So now you have all columns are delimited by comma. Then use this file as source to your next process. Pro - easy process. cons - two step process so can take time. Also if your data has ,/~ you need to enclose them using "".
2. Use shell script to replace ~ with comma. So now you have all columns are delimited by comma Then use this file as source to your next process. cons - Script need to be careful not to replace ~ inside data.
3. You can use one source qualifier(read file as comma separated) and the split the first column into 7 parts using INSTR/SUBSTR combination. Step1 - first find position of ~ in first column like this -

v_pos1 = InStr( col1, '~', 1, 1)
v_pos2 = InStr( col1, '~', 1, 2)
v_pos3 = InStr( col1, '~', 1, 3)
...

Step 2 -

o_val1 = iif(v_pos1 =0,col1, SubStr( col1, 1, v_pos1 - 1))
o_val2 = iif(v_pos2 =0,'', SubStr( col1, v_pos1 + 1), SubStr( col1, v_pos1 + 1, v_pos2 - v_pos1 - 1))
o_val2 = iif(v_pos2 =0,'', SubStr( col1, v_pos2 + 1), SubStr( col1, v_pos2 + 1, v_pos3 - v_pos2 - 1))
...

4. You can also use new created LOOP transformation. This is an extension and need some bit of research