CSV output displayed in a single row in Excel

Question

I am generating a CSV file using the UTL_FILE package. When I open the file in Excel, all of the data is appearing in a single row. But values are in each column. I am not sure how to get the data in seperate rows.

Here is my PL/SQL block:

declare
  v_file  utl_file.file_type;

  v_line_num number(7);

 begin 

  for orec in ( select distinct --) loop

   dbms_output.put_line('route '||orec.r_no);

  v_file := utl_file.fopen('EXPORT_CSV','DAILY_REPORT_OF_'||OREC.r_no||'.csv','W',32767);

  utl_file.put_line(v_file,'"SERVICEDAY","OP","PAC","RNO","DESC","DIRECTION","BLK","T_ID","T_NO","DUTY","S_STOP_SEQ","E_STOP_SEQ","LOC_FROM","SCH_TIME_FROM","OBS_TIME_FROM","LOC_TO","SCH_TIME_TO","OBS_TIME_TO","IS_SPLIT","CAUSE","M_AGE","OP_REASON","OP_COMMENT","L_COMMENT","L_AMENDED_CODE","STATUS"');

  v_line_num:=0;

  for irec in (select --) loop

  utl_file.put(v_file, '"'||nvl(irec.SER,''));
  utl_file.put(v_file,'","' || nvl(irec.OP,''));
  utl_file.put(v_file,'","' || nvl(irec.PAC,''));
  utl_file.put(v_file,'","' || nvl(irec.RNO,''));
  utl_file.put(v_file,'","' || nvl(irec.DESC,''));
  utl_file.put(v_file,'","' || nvl(irec.DIRECTION,''));
  utl_file.put(v_file,'","' || nvl(irec.BLK,''));
  utl_file.put(v_file,'","' || nvl(irec.T_ID,''));
  utl_file.put(v_file,'","' || nvl(irec.T_NO,'')); 
  utl_file.put(v_file,'","' || nvl(irec.DUTY,''));
  utl_file.put(v_file,'","' || nvl(irec.S_STOP_SEQ,''));
  utl_file.put(v_file,'","' || nvl(irec.E_STOP_SEQ,''));  
  utl_file.put(v_file,'","' || nvl(irec.LOC_FROM,'')); 
  utl_file.put(v_file,'","' || nvl(irec.SCH_TIME_FROM,''));
  utl_file.put(v_file,'","' || nvl(irec.OBS_TIME_FROM,''));
  utl_file.put(v_file,'","' || nvl(irec.LOC_TO,'')); 
  utl_file.put(v_file,'","' || nvl(irec.SCH_TIME_TO,''));
  utl_file.put(v_file,'","' || nvl(irec.OBS_TIME_TO,''));
  utl_file.put(v_file,'","' || nvl(irec.IS_SPLIT,''));
  utl_file.put(v_file,'","' || nvl(irec.CAUSE,''));
  utl_file.put(v_file,'","' || nvl(irec.M_AGE,''));
  utl_file.put(v_file,'","' || nvl(irec.OP_REASON,''));
  utl_file.put(v_file,'","' || nvl(irec.OP_COMMENT,''));
  utl_file.put(v_file,'","' || nvl(irec.L_COMMENT,''));
  utl_file.put(v_file,'","' || nvl(irec.L_AMENDED_CODE,''));
  utl_file.put(v_file,'","' || nvl(irec.STATUS,'')); 

  utl_file.put_line(v_file,chr(13) || chr(10));

  v_line_num:=v_line_num+1;

  end loop;  
  dbms_output.put_line('lines: '||v_line_num);

  utl_file.fclose(v_file); 

  end loop;

--utl_file.fclose(v_file);

--utl_file.fclose_all;


/*
  exception
  when others then
  utl_file.fclose_all;
  dbms_output.put_line(sqlerrm);
*/
  end;

Answer 1

At the end of each time around the loop you are doing:

  utl_file.put(v_file,'","' || nvl(irec.STATUS,'')); 

  utl_file.put_line(v_file,chr(13) || chr(10));

There is no closing double-quote after the last value on the line, so the chr(13) and chr(10) are inside a double-quoted value - and therefore seen as part of that value and not actually as carriage return and line breaks when the file is read. Thus, everything is all on one line.

You need to do something like:

  utl_file.put(v_file,'","' || nvl(irec.STATUS,'')); 

  utl_file.put_line(v_file, '"' || chr(13) || chr(10));

although as put_line() includes the line terminator, you probably only really need:

  utl_file.put(v_file,'","' || nvl(irec.STATUS,'')); 

  utl_file.put_line(v_file, '"');

If you're generating the file on a Unix-y server and then transferring it to Windows you might still need the chr(13), though it would probably be better to do that as part of the transfer; and I think Excel will be happy without it anyway.

---

Incidentally, all your nvl() calls are pointless. When you do nvl(irec.SER,''), if irec.SER is null you include '' instead, but in Oracle '' is the same as null; from the documentation

Oracle Database treats a character value with a length of zero as null.

so you're really doing nvl(irec.SER,null), which makes no difference.

---

Personally I'd find it clearer to include the closing double-quote on the same line as the opening one, for all the values:

  utl_file.put(v_file,  '"' || irec.SER || '"');
  utl_file.put(v_file, ',"' || irec.OP || '"');
  ...
  utl_file.put(v_file, ',"' || irec.STATUS || '"'); 

  utl_file.new_line(v_file);

and it would probably be a good idea to add explicit to_char() calls for datetime (and maybe number) values so you can specify a format Excel will understand.