Home » SQL & PL/SQL » SQL & PL/SQL » Parse string from CSV using Utl_file read
Parse string from CSV using Utl_file read [message #189473] |
Thu, 24 August 2006 14:46  |
intelinside_1980
Messages: 4 Registered: August 2006 Location: CALIFORNIA
|
Junior Member |

|
|
hi , need some help here this one is kind of complicated for me !!!not getting many ideas ...
i have a csv in the following format :
have a file containing some records in the following format :
10888,25000,"26,10,15,22,35....."
we dont know how many are going to be in the " " which will also be
comma separated .
i need to split all of them like this lets say
10888,25000,"260,10,15"
A1=10888
A2=25000
c1=260
c2=10
c3=15
c4 .. cn if more in the quotes ....
where a1 a2 can be plsql variable and c1...cn is an array or plsql table
i am unable to get this ...
|
|
|
|
Re: Parse string from CSV using Utl_file read [message #189483 is a reply to message #189480] |
Thu, 24 August 2006 15:33   |
intelinside_1980
Messages: 4 Registered: August 2006 Location: CALIFORNIA
|
Junior Member |

|
|
i dont have 10g i am working on 9i ..
i could make a function to split just the comma saparated string but the variable number of items inside the quotes is the problem
create or replace FUNCTION SPLIT_ksr(
delimiter_i IN VARCHAR2, -- DEFAULT NULL
string_i IN VARCHAR2 --DEFAULT NULL
)
RETURN DBMS_SQL.VARCHAR2_TABLE
IS
table_t DBMS_SQL.VARCHAR2_TABLE;
-- deliminator position initailized to the first deliminator
delimiter_pos_t INTEGER := 1;
-- index_position into the table
table_pos_t INTEGER :=0;
start_point_t INTEGER;
end_point_t INTEGER;
BEGIN
IF delimiter_i IS NULL THEN
--null;
RETURN table_t;
END IF;
IF string_i IS NULL THEN
--null;
RETURN table_t;
END IF;
IF (INSTR(string_i,delimiter_i,1,delimiter_pos_t) = 0 )
THEN
table_t(table_pos_t) := string_i;
RETURN table_t;
END IF;
--get the data upto the first deliminator
start_point_t := 1;
end_point_t := INSTR(string_i,delimiter_i,1,1) -1;
table_t(table_pos_t) := SUBSTR(string_i,start_point_t,end_point_t);
WHILE (INSTR(string_i,delimiter_i,1,delimiter_pos_t) > 0 )
LOOP
--move to the next index
table_pos_t := table_pos_t+1;
--gets data that is bounded by the deliminator on the left and right
start_point_t := INSTR(string_i,delimiter_i,1,delimiter_pos_t)+1;
end_point_t := INSTR(string_i,delimiter_i,1,delimiter_pos_t+1)- 2 -INSTR(string_i,delimiter_i,1,delimiter_pos_t)+1;
table_t(table_pos_t) := SUBSTR(string_i,start_point_t,end_point_t);
--move to the delimiter position
delimiter_pos_t := delimiter_pos_t+1;
END LOOP;
--get the data that is from the last deliminator to the end of the line
start_point_t := INSTR(string_i,delimiter_i,1,delimiter_pos_t-1)+1;
table_t(table_pos_t) := SUBSTR(SUBSTR(string_i,start_point_t),1,1000);
RETURN table_t;
END;
|
|
|
|
|
|
Re: Parse string from CSV using Utl_file read [message #306534 is a reply to message #189473] |
Fri, 14 March 2008 10:33  |
gbh22
Messages: 1 Registered: March 2008 Location: Seattle
|
Junior Member |
|
|
DBMS_UTILITY.COMMA_TO_TABLE is for Oracle DBAs, not for PL/SQL developers. It needs valid identifier names, so no numbers, etc.
intelinside_1980, thanks for the code. It works, but I was concerned about that 1000-character limit at the end. I think the below is a good approach. It's main difference is it keeps taking a substring of the previous work string, and always takes the first value of that.
The code is a self-contained demo. It has a list of numbers because that's the problem I was solving. You can easily replace singleVal with a table type OUT or RETURN variable.
DECLARE
sourceStr VARCHAR2(100) := '3,34,55,6';
workStr sourceStr%TYPE;
singleVal sourceStr%TYPE;
charPos BINARY_INTEGER;
sep VARCHAR2(1) := ',';
BEGIN
workStr := sourceStr;
WHILE (INSTR(workStr,sep) > 0 ) LOOP
charPos := INSTR(workStr,sep);
singleVal := SUBSTR(workStr,0,charPos - 1);
workStr := SUBSTR(workStr,charPos + 1);
DBMS_OUTPUT.PUT_LINE('Val: '||singleVal);
END LOOP;
--Last value, or THE value if there is only one.
singleVal := workStr;
DBMS_OUTPUT.PUT_LINE('Val: '||singleVal);
END LOOP;
|
|
|
Goto Forum:
Current Time: Thu Feb 06 16:23:56 CST 2025
|