Oracle UTL_FILE - multiple rows in excel for a single cell

Question

I have a requirement to display the below in a csv file which is generated using UTL_FILE package of PL/SQL.

Heading1 Heading2 Heading3 ABCDE 123 987641213 xxx street yyyy

For each value in 'Heading1', I get 3 rows for address from a particular table. In the output csv file, I need to display the address in a single cell with data separated by a new line.

I tried using a cursor and appending data using chr(10), chr(12), chr(15) but in vain.

Kindly help me in getting the output.

score 1 · Answer 1 · answered Dec 05 '12 at 13:29

1

you have to quote your field that has carriage returnseg

heading1,headiing2,heading3
abcde,123,"1 street
town
90210"
abcde,124,"13 street
town
43245"

answered Dec 05 '12 at 13:29

DazzaL

21,638
3
49
57

Hi Dazzal, I tried using carriage return ASCII value. I am writing the UTL_FILE output to a csv file with comma separated list. But I am getting these particular carriage returns as special character like boxes between each line. Also they are displayed on the same line. – Avinash Dec 10 '12 at 10:05
what character sequence are you using for carriage return? it should be chr(13)||chr(10). – DazzaL Dec 10 '12 at 10:17

BulentB · Answer 2 · 2012-12-05T17:01:51.463

What DazzaL proposed should work but if you can't change the input file in your case you can use this code :

declare
    v_line varchar2(2000);
    v_c1   varchar2(10);
    v_c2   varchar2(10);
    v_c3   varchar2(1980);
    nb_line_per_record integer := 3;
    v_line_in_record   integer;
    v_seperator varchar2(1) := ',';
    v_file UTL_FILE.FILE_TYPE;
begin
    v_file := utl_file.FOPEN(your_directory, 'your_file','r');
    v_line_in_record := 0;
    --we skip the first line
    UTL_FILE.GET_LINE(v_file, v_line);
    loop
        v_line_in_record := v_line_in_record + 1; 
        begin
            UTL_FILE.GET_LINE(v_file, v_line);
        EXCEPTION
            WHEN no_data_found THEN
                exit;
        END;
        --first line of record we cut by v_seperator
        if v_line_in_record = 1 then
           v_c1 := substr(v_line,1,instr(v_line,v_seperator,1,1)-1);
           v_c2 := substr(v_line,instr(v_line,v_seperator,1,1)+1,
                          instr(v_line,v_seperator,1,2)-                                
                          instr(v_line,v_seperator,1,1)-1);
           v_c3 := substr(v_line,instr(v_line,v_seperator,1,2)+1) 
                          || char(10);--if you want new line in adresse 
        else
            --not first line we concatanate to adress with new line
            v_c3 := v_c3 || v_line ||
                    case when v_line_in_record = 2 then char(10) else '' end; 
        end if;
        if v_line_in_record = 3 then
            v_line_in_record := 0;
            --do something with your record 
            insert into yourtable values (v_c1,v_c2,v_c3);
        end if;
    end loop;
    UTL_FILE.FCLOSE(v_file);
    COMMIT;
end;

But this would work only if you are absolutely certain that every record is 3 lines. If i were you i would add a record delimiter after each record and use SQLLOADER to load this file into a table. You can define a Record delimiter with SQLLOADER.Here is an exemple ctl which uses & as record delimiter

options (skip=1) 
  load data 
  infile "yourfile" "str '&'" 
  truncate into table your_table 
  fields terminated by "," 
  trailing nullcols 
  (heading1, heading2, heading3)

And if you use a Record delimiter you can even create an External Table on your file.

Hi Bulent, Thanks for your wonderful query. But the problem is char(10) would make the 2nd row of the 3row output to be displayed in the next line of excel... I need those 3 rows to be displayed something like how we would use ALT+ENTER in excel to enter multiple lines of data within a single cell.... Basically, it should be ALT+ENTER ALT+ENTER This would allow all data to appear in a single cell. — Avinash, Dec 10 '12 at 10:10

Oracle UTL_FILE - multiple rows in excel for a single cell

2 Answers2