CODEWITHSUNDEEP
oracle-databasecsvplsqlutl-file
Avinash
Avinash

Reputation: 55

Oracle UTL_FILE - multiple rows in excel for a single cell

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.

Upvotes: 1

Views: 5042

Answers (2)

BulentB
BulentB

Reputation: 316

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.

Upvotes: 0

DazzaL
DazzaL

Reputation: 21973

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"

Upvotes: 1

Related Questions