CSV - comma separated values - and PL/SQL

Summary:

How to very easily load a CSV record using DBMS_UTILITY instead of writing a lot of code

CSV - comma separated values - and PL/SQL

thomasbcox 15 Feb 2008 23:12

Never write your own code when you can just use a built-in capability of the Oracle database or the PL/SQL language.

In my case, I needed to take a CSV input - a record or line of text containing comma-separated values enclosed with
double quotes - and turn it into a set of discrete field values.

Most of the way through writing a string parser with INSTR, SUBSTR, REPLACE, TRANSLATE, etc. I realized I could do it
all in one pass by calling DBMS_OUTPUT.COMMA_TO_TABLE, as follows:

FUNCTION comma_to_table(iv_raw IN VARCHAR2) RETURN dbms_utility.lname_array IS
   ltab_lname dbms_utility.lname_array;
   ln_len     BINARY_INTEGER;
BEGIN
   dbms_utility.comma_to_table(list   => iv_raw
                              ,tablen => ln_len
                              ,tab    => ltab_lname);
   FOR i IN 1 .. ln_len LOOP
      dbms_output.put_line('element ' || i || ' is ' || ltab_lname(i));
   END LOOP;
   RETURN ltab_lname;
END;

Next, I need to grab a particular field from the input, say at position 'n':

FUNCTION get_field
   (
      iv_raw   IN VARCHAR2
     ,in_pos   IN NUMBER
     -- ,iv_delim IN VARCHAR2 DEFAULT gcv_delim
     -- ,iv_encl  IN VARCHAR2 DEFAULT gcv_encl -- enclosure not supported yet
   ) RETURN VARCHAR2 IS 
      ltab_lname dbms_utility.lname_array;
      lv_retval VARCHAR2(32760);
   BEGIN 
      ltab_lname := comma_to_table(iv_raw);
      lv_retval := ltab_lname(in_pos);
      RETURN lv_retval ; 
   END ;

Just to make you laugh, here is the incomplete and somewhat bug-riddled code I was writing before I came to my senses. There are
all sorts of cases that this won't handle well.

--   FUNCTION get_field
--   (
--      iv_raw   IN VARCHAR2
--     ,in_pos   IN NUMBER
--     ,iv_delim IN VARCHAR2 DEFAULT gcv_delim
--     -- ,iv_encl  IN VARCHAR2 DEFAULT gcv_encl -- enclosure not supported yet
--   ) RETURN VARCHAR2 IS
--      ln_start  PLS_INTEGER := 0;
--      ln_end    PLS_INTEGER := 0;
--      ln_length PLS_INTEGER := 0;
--      lv_retval VARCHAR2(32760);
--      ln_nth    PLS_INTEGER := in_pos;
--      ln_fieldcount PLS_INTEGER := 0;
--   BEGIN
--      -- field count cannot exceed delimeter count + 1
--      -- count delimiters by comparing lengths before and after
--      -- removing the delimeter, adjusting for the length of the 
--      -- delimeter itself.  Field count = delimeter count +1.
--      ln_fieldcount := ((length(iv_raw) -
--                       length(translate(iv_raw
--                                        ,'a' || iv_delim
--                                        ,'a'))
--                       )/length(iv_delim)) + 1;
--      IF ln_fieldcount < 2 THEN 
--         lv_retval := iv_raw;
--      ELSIF ln_fieldcount < ln_nth THEN 
--         lv_retval := NULL ;
--      ELSE
--         IF ln_nth = 1 THEN
--            ln_start := 1;
--         ELSE
--            ln_start := instr(iv_raw
--                             ,iv_delim
--                             ,1
--                             ,ln_nth-1) + 1;
--         END IF;
--         IF ln_nth = ln_fieldcount THEN  
--            ln_end := length(iv_raw);
--         ELSE 
--            ln_end := instr(iv_raw
--                             ,iv_delim
--                             ,1
--                             ,ln_nth ) - 1; 
--         END IF ;
--         ln_length := (ln_end - ln_start) +1 ;
--         lv_retval := substr(iv_raw,ln_start,ln_length);
--      END IF ; -- ln_fieldcount < 2
--      RETURN (lv_retval) ;
--   END;

Re: CSV - comma separated values - and PL/SQL

thomasbcox 06 Apr 2008 21:49

Here's some code from my friend Mike Zhang that does a better job even than DBMS_UTILITY does. It doesn't handle
embedded carriage returns or line feeds. It handles almost everything else.

/*
   This formal grammer of CSV is copied from: http://tools.ietf.org/html/rfc4180

   The ABNF grammar of the comma separated values format (CSV) appears as follows:
   file = [header CRLF] record *(CRLF record) [CRLF]

   header = name *(COMMA name)

   record = field *(COMMA field)

   name = field

   field = (escaped / non-escaped)

   escaped = DQUOTE *(TEXTDATA / COMMA / CR / LF / 2DQUOTE) DQUOTE

   non-escaped = *TEXTDATA

   COMMA = %x2C ; Dec#=44, ","

   CR = %x0D ; Dec#=13, "^M"

   DQUOTE = %x22 ; Dec#=34, """

   LF = %x0A ; Dec#=10, "^J"

   CRLF = CR LF

   TEXTDATA = %x20-21 / %x23-2B / %x2D-7E ; / Dec#32-33 / Dec#35-43 / Dec#45-126

   (Sp,! / "",#,$,%,&,',(,),*,+ / -,.,/,0-9,:,;,<,=,>,?,@,A-Z,[,\,],^,_,`,a-z,{,|,},~)

   %x22 (Dec#34) is "
   %x2c (Dec#44) is ,

   */
   -- This function will return the field specified by i_field_count of the line from CSV file
   -- Since only one line can be passed in, CR and LF are not supported inside " "
   FUNCTION get_next_field (
      i_input_line    IN   VARCHAR2,
      i_field_count   IN   INTEGER
   )
      RETURN csv_field_rectype IS
      li_current_pos                PLS_INTEGER;
      lv_input_line                 VARCHAR2 (4001);
      lv_return_field               VARCHAR2 (4000);
      csv_field_rec                 csv_field_rectype;
   BEGIN
      li_current_pos     := 1;
      lv_return_field    := NULL;
      csv_field_rec      := NULL;

      -- First, append , at the end of the line if there is no ,
      -- Please note that this function will not handle CR / LF inside the " as escaped feild
      -- as defined in above grammar.
      IF SUBSTR (i_input_line, -1, 1) <> ',' THEN
         lv_input_line    := i_input_line || ',';
      ELSE
         lv_input_line    := i_input_line;
      END IF;

      IF    (i_input_line IS NULL)
         OR (INSTR (lv_input_line, ',', 1, i_field_count) = 0) THEN
         -- Out of range.
         raise_err ('csv_range_err');
      ELSE
         IF i_field_count = 1 THEN
            DECLARE
               lv_local_field                VARCHAR (4000);
               li_doubl_quote_pos            INTEGER;
               li_doubl_quote_count          INTEGER;
               li_2doubl_quote_count         INTEGER;
               lb_comma_inside_escaped_field BOOLEAN;
            BEGIN
               li_current_pos        := INSTR (lv_input_line, ',', li_current_pos, 1);
               lv_local_field        := SUBSTR (lv_input_line, 1, li_current_pos - 1);
               -- Search to see if any " before this first comma
               -- The lv_local_field can be NULL if the field is empty
               -- Trim out the space before " if any.
               li_doubl_quote_pos    := INSTR (NVL (TRIM (lv_local_field), 'NULL'), '"', 1, 1);

               IF li_doubl_quote_pos = 0 THEN
                  -- This field is not double quoted, an non-escaped field, simplest situation.
                  -- Tested. 'CAMRA_Position,,As of ,03/25/2008,,,,,'
                  lv_return_field    := lv_local_field;
               ELSIF li_doubl_quote_pos = 1 THEN
                  lb_comma_inside_escaped_field    := FALSE;
                  -- " found, trim out the space before " if any.
                  lv_local_field                   := TRIM (lv_local_field);

                  -- This field is possible double quoted.
                  -- According to the CSV file grammer, this field is escaped,
                  -- there must be a double quote at the end of this field.
                  IF SUBSTR (lv_local_field, -1, 1) = '"' THEN
                     -- Check to see how many " inside the " "
                     lv_return_field         := SUBSTR (lv_local_field, 2, LENGTH (lv_local_field) - 2);
                     -- The number of single "
                     li_doubl_quote_count    := string_occurrence_count (lv_return_field, '"');

                     IF li_doubl_quote_count <> 0 THEN
                        -- The number of two "
                        li_2doubl_quote_count    := string_occurrence_count (lv_return_field, '""');

                        -- "" is used for excaping the " inside " " for escaped field,
                        -- count of " is two times as count of ""
                        -- The following code uses the lazy evaluation because li_2doubl_quote_count could be 0
                        -- :-)
                        IF    (li_2doubl_quote_count = 0)
                           OR (li_doubl_quote_count / li_2doubl_quote_count != 2) THEN
                           -- This is the situation when , is inside the escaped field, not a delimiter
                           lb_comma_inside_escaped_field    := TRUE;
                        ELSE   /* IF    (li_2doubl_quote_count = 0)
                                     OR (li_doubl_quote_count / li_2doubl_quote_count != 2) THEN */
                           -- There are "" inside the " "
                           -- do nothing. This ELSE is just for logic.
                           -- Tested. '"CAMRA_""""Position",,As of ,03/25/2008,,,,,'
                           NULL;
                        END IF;
                     ELSE   -- IF li_doubl_quote_count <> 0 THEN
                        -- There is no " inside the " "
                        -- Do nothing. This ELSE is just for logic.
                        -- Tested. '"CAMRA_Position",,As of ,03/25/2008,,,,,'
                        NULL;
                     END IF;
                  ELSE   -- IF SUBSTR (lv_local_field, -1, 1) = '"' THEN
                     -- Double quote found at the first place of field
                     -- but the character before , is not a double quote
                     -- Error or , is inside the escaped field, not a delimiter
                     lb_comma_inside_escaped_field    := TRUE;
                  END IF;

                  IF lb_comma_inside_escaped_field THEN
                     -- The " is at the beginning of line, have to find out the next ",
                     DECLARE
                        lv_field_after_comma          VARCHAR2 (4000);
                        li_previous_current_position  PLS_INTEGER;
                        lb_continue_loop              BOOLEAN := TRUE;
                     BEGIN
                        WHILE lb_continue_loop LOOP
                           li_previous_current_position    := li_current_pos;
                           li_current_pos                  := INSTR (SUBSTR (lv_input_line, li_current_pos), '",', 1, 1);

                           IF li_current_pos = 0 THEN
                              raise_err ('miss_dquote_err');
                           ELSE
                              lv_field_after_comma     :=
                                                     SUBSTR (lv_input_line, li_previous_current_position + 1, li_current_pos - 1);
                              -- Combine the fields before and after , to become the new lv_local_field
                              lv_local_field           := lv_local_field || ',' || lv_field_after_comma;
                              -- Remember this new lv_local_field is inside " ", so strip them out
                              lv_return_field          := SUBSTR (lv_local_field, 2, LENGTH (lv_local_field) - 2);
                              -- The number of single "
                              li_doubl_quote_count     := string_occurrence_count (lv_return_field, '"');
                              -- The number of two "
                              li_2doubl_quote_count    := string_occurrence_count (lv_return_field, '""');

                              IF li_2doubl_quote_count = 0 THEN
                                 -- This is the situation where , is inside the " " but there is no "" inside
                                 lb_continue_loop    := FALSE;
                              ELSIF    (li_2doubl_quote_count = 0)
                                    OR (li_doubl_quote_count / li_2doubl_quote_count != 2) THEN
                                 -- Too bad, even this ", is not the delimiter, continue until found it.
                                 lb_continue_loop    := TRUE;
                              ELSE
                                 lb_continue_loop    := FALSE;
                              END IF;

                              -- Set the current position because we will need it later in the program
                              li_current_pos           := li_current_pos + li_previous_current_position;
                           END IF;
                        END LOOP;
                     END;
                  END IF;
               ELSE
                  -- The double quote found but is not at position 1
                  -- Error, see error definition for details.
                  -- Tested: 'CAMRA_"""Position,,As of ,03/25/2008,,,,,'
                  raise_err ('csv_dquote_err');
               END IF;
            END;

            -- The 2 double quote is acutally 1 double quote in the escaped field
            csv_field_rec.return_field    := REPLACE (lv_return_field, '""', '"');
            csv_field_rec.current_pos     := li_current_pos;
         ELSE   -- IF i_field_count = 1 THEN
            -- Recursive call.
            -- The idea is to strip out all the fields before the field being asked.
            csv_field_rec    := get_next_field (lv_input_line, 1);
            lv_input_line    := SUBSTR (lv_input_line, csv_field_rec.current_pos + 1);
            csv_field_rec    := get_next_field (lv_input_line, i_field_count - 1);
         END IF;
      END IF;

      RETURN csv_field_rec;
   END;

Re: CSV - comma separated values - and PL/SQL

thomasbcox 06 Apr 2008 21:51

Also:

TYPE csv_field_rectype IS RECORD (
      return_field                  VARCHAR (4000),
      current_pos                   PLS_INTEGER
   );

Pawan kumar (guest) 23 Jun 2012 19:04

Write a Program to read a CSV file and extract a list of unique entries.

I have given sample inputs and outputs for the tasks

Input.txt:-

name,city
Ramesh,Delhi
Suresh,Mumbai
Ram,Banglore
Shyam,Mumbai
Dilip,Calcutta
Sita,Banglore

Output.txt :-

Delhi
Mumbai
Banglore
Calcutta

I need code of the above given question

Re: CSV - comma separated values - and PL/SQL

thomasbcox 26 Jun 2012 14:10

I'm not going to do your CS-101 homework for you.

For Oracle users… Once you have the above function installed, you can SELECT UNIQUE (field2) FROM (csv-file).

Gopal (guest) 27 Jul 2012 11:40

Hi - I've a comma separated file. I wish to write a PL/SQL program to insert into a table. for example:
Age,Emp_id,Case_id
21,501,1001
22,502,1002
23,503,1003

I've insert the above values inside a existing table.

Kindly let me know how to split in PL/SQL. I've profound knowledge in unix shell scripting but not in PL/SQL.

Thanks…
Gopal

Not sure what you want to accomplish

thomasbcox 27 Jul 2012 21:19

The way to use this code is, you create the functions above. Then you put your CSV in the correct location where the DBMS can see it. Then you can treat it as a table.

When you

select * from comma-to-table ('csv-file-name.csv')

you're most of the way there.

Then you can

insert into target-table (field1, field2, ...) 
select * from comma-to-table ('csv-file-name.csv')

This is pseudo code, obviously.

SAITHINI (guest) 11 Oct 2012 06:28

HI Folks,

Please help in following scenario.

I am passing the comma separated string to oracle procedure to insert in to a table.
like,
string str='1,abc,1000'
the table emp has columns like : eno,ename,sal

so i need a procedure in oracle to read the string and insert as a record in table emp.

Thanks In Advance
AR SAITHINI