Additional function to process MS Excel CSV record.

MS Excel record could contain additional delimiters such as " and have commas inside
the individual cells. MergeStrings function offers additional functionality allowing to process these or any other additional delimiters.
This can be useful when you need more control over the imported data or invoke Oracle APIs to upload these records into Oracle APPS tables.

Below is the example.

/*
* GetStrings(myString Varchar2) Return Table of Varchar2(300) Index by Binary_Integer
*/
declare

myString Varchar2(20000):='AX,AXX,AXXX,Anne,3333,Merchant,xxxx@yahoo.com,Manager I,18335,DOOM 300,300,"First, Middle Last (Nick)",2434,46678 BEAUMONT FOUNTAIN PLAZA,3050 DOWJON RD,,,Beaumont,TX,77706-7214,TX,"Goor, Stephen J","Duke (Lion)",Full Time >=35 hrs,Exempt,185431,,No,,,'; -- just an example

Type v_type is Table of Varchar2(3000) index by Binary_Integer;

v v_type;

---------------------------------------------------------

Function mergeStrings(v v_type,p_delimiter Varchar2,p_globalDelimiter Varchar2) Return v_type
is


i number:=1;
i_Temp Number :=1;
k Number:=0;


v_Temp V_type;

Begin



while(i_Temp <= v.LAST) Loop


if (v(i_temp) is null) Then v_temp(i):=v(i_temp); i:=i+1;i_temp:=i_temp+1;
Else

if regexp_instr(v(i_temp),'^'||p_delimiter)>0 and regexp_instr(v(i_temp),p_delimiter||'$') >0 Then
v_temp(i):=v(i_Temp);
i:=i+1;
i_temp:=i_temp+1;


Elsif regexp_instr (v(i_temp),'^'||p_delimiter)<1 and regexp_instr(v(i_temp),p_delimiter||'$')<1 Then
v_temp(i):=v(i_Temp);
i:=i+1;
i_temp:=i_temp+1;

ElsIf (regexp_instr (v(i_temp),'^'||p_delimiter)>0 and instr(v(i_temp),p_delimiter||'$')<1) Then
v_Temp(i):=v(i_temp);
i_temp:=i_temp+1;


k:=nvl(regexp_instr(v(i_temp),p_delimiter||'$'),0);

if k=0 Then

while ( k=0) Loop

v_Temp(i):=v_Temp(i)||p_globalDelimiter||v(i_temp);
i_Temp:=i_Temp+1;

k:=nvl(regexp_instr(v(i_temp),p_delimiter||'$'),0);


End Loop;


End If;

v_Temp(i):=v_Temp(i)||p_globalDelimiter||v(i_temp);

v_Temp(i):=ltrim(v_Temp(i),p_delimiter);
v_Temp(i):=rtrim(v_Temp(i),p_delimiter);

i:=i+1;
i_temp:=i_temp+1;

End If;

End If;

v_Temp(i):=ltrim(v_Temp(i),'"');
v_Temp(i):=rtrim(v_Temp(i),'"');

End Loop;

return v_Temp;

Exception
When NO_DATA_FOUND Then

dbms_output.put_line('String '||v(1) ||' cannot be pro');

return v_temp;

End;


------
Function GetStrings
(p_items Varchar2 Default ' ',
p_delimiter Varchar2 Default ',',
p_enclosed Varchar2 DEfault '"')
Return v_type


is

v_varTab_i v_type;
tempString Varchar2(16000);
i Binary_Integer:=1;
i_s number;
i_e number;
i_sp number;
i_ep number:=1;
t_items Varchar2(16000);
i_ds Number:=length(p_delimiter);
i_Length Number;
i_countEnclosed Number:=1;

v_debug Number:=0;

i_Exit Number:=0;

Begin

t_items:=rtrim(p_items,p_delimiter);
t_items:=t_items||p_delimiter;

v_debug :=1;

i_s :=1;
tempString:=substr(t_items,1);
i:=1;

while i_ep > 0
Loop

i_ep:=instr(tempString,p_delimiter,1);

v_debug :=2;

if i_ep >0 then
if i_ep-i_s = 0 then v_varTab_i(i):= NULL;
else
v_varTab_i(i):=substr(tempString,1,i_ep-i_ds);


end if;
i:=i+1;
i_s:=1;
tempString:=substr(tempString,i_ep+length(p_delimiter));
else
v(1):=p_items; -- in case no delimiter present
end if;

v_debug :=3;


End Loop;


v:=v_varTab_i;

v_varTab_i:=mergeStrings(v,'"',p_delimiter);


Return v_varTab_i;

Exception
when others then
dbms_output.put_line('GetString '||substr(SQLERRM,1,200));
return v_varTab_i;


End GetStrings;
-------------------------------------------------------------------------------------
Begin

v:=GetStrings(myString,',');

For j in 1..v.LAST Loop

dbms_output.put_line('Element='||v(j));

End Loop;

End;