Home » SQL & PL/SQL » SQL & PL/SQL » Parse string from CSV using Utl_file read
icon5.gif  Parse string from CSV using Utl_file read [message #189473] Thu, 24 August 2006 14:46 Go to next message
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 #189480 is a reply to message #189473] Thu, 24 August 2006 15:07 Go to previous messageGo to next message
Bill B
Messages: 1482
Registered: December 2004
Senior Member
See the following link

http://download-east.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_util.htm#sthref9323
Re: Parse string from CSV using Utl_file read [message #189483 is a reply to message #189480] Thu, 24 August 2006 15:33 Go to previous messageGo to next message
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 #189484 is a reply to message #189483] Thu, 24 August 2006 15:37 Go to previous messageGo to next message
Bill B
Messages: 1482
Registered: December 2004
Senior Member
The function is also available in 9i. See

http://download-east.oracle.com/docs/cd/B10501_01/appdev.920/a96612/d_util2.htm#1002109
icon14.gif  Re: Parse string from CSV using Utl_file read [message #189485 is a reply to message #189483] Thu, 24 August 2006 15:52 Go to previous messageGo to next message
intelinside_1980
Messages: 4
Registered: August 2006
Location: CALIFORNIA
Junior Member

thanks for ur speedy response Bill.
i missed on that one !!!

Re: Parse string from CSV using Utl_file read [message #189489 is a reply to message #189485] Thu, 24 August 2006 16:59 Go to previous messageGo to next message
intelinside_1980
Messages: 4
Registered: August 2006
Location: CALIFORNIA
Junior Member

It only works with stuff that is a valid Oracle name i.e. what you could use when naming a column. 'A1' is valid, '1A' isn't nor is 'A%'.

is there any way around if i am going to use this in this case :::
 create or replace procedure read_min(V_min        IN   Varchar2)
   AS
   line_tab_t1   dbms_utility.uncl_array;
   len            pls_integer;
   cnt            pls_integer:=1;
   error_status   integer;
   BEGIN
   DBMS_UTILITY.COMMA_TO_TABLE(V_min,len,line_tab_t1);
   for i in 1..line_tab_t1.count loop
   dbms_output.put_line( cnt||line_tab_t1(i) );
   cnt := cnt+1;
   end loop;
   --error_status_out := 0;
   EXCEPTION
   WHEN others THEN
   error_status := -1 ;
   END;

TRY BOTH THE EXEC !!!

exec read_min('243267,78,848,38');

exec read_min('one,two,three');
Re: Parse string from CSV using Utl_file read [message #306534 is a reply to message #189473] Fri, 14 March 2008 10:33 Go to previous message
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;

Previous Topic: checking concesutive record's dates to find whether it completed within 3 days (merged)
Next Topic: SQL to Rebuild the references of a table
Goto Forum:
  


Current Time: Sun Dec 04 14:44:18 CST 2016

Total time taken to generate the page: 0.06435 seconds