1

I have generated a csv file using the below query, but all column data are coming in one column in the csv file. Please help me to separate these column data.

create or replace procedure csv_generate as
CURSOR c1 is SELECT * FROM emp;
v1 c1%rowtype;
z1 VARCHAR2(2000);
z2 VARCHAR2(2000);
f1 utl_file.file_type;
BEGIN
FOR i IN c1
loop
--play(i.ename);
f1:=utl_file.fopen('TEST_DIR1','out.dbf','W');
z1:=i.empno||' '||i.ename||'    '||i.JOB||'   '||i.sal||'  '||i.deptno;
z2:=z2||chr(10)||z1;
utl_file.put_line(f1,z2);
utl_file.fclose(f1);
END loop;
--utl_file.put_line(f1,z2);
END;
Alok
  • 61
  • 1
  • 8
  • 1
    You are using spaces as separator; to build a CSV you should add a separator, say a comma, instead of spaces. Also, you are opening and closing the file once per row, while you should better do this operations outside the loop. And even, aren't you concatenating too much content in the variable z2? – Aleksej Apr 27 '17 at 07:17
  • Ok thanks Aleksej. But how can get the ename data in second column of csv – Alok Apr 27 '17 at 07:38
  • If you use spaces as separator, you will always have a single column. – Aleksej Apr 27 '17 at 07:42
  • So, what should i use there to separate the columns? – Alok Apr 27 '17 at 08:06
  • It depends on your requirement. The usual separator is a comma, but you can use a semicolon, a pipe, whatever you need – Aleksej Apr 27 '17 at 08:07
  • Yes. Thanks man it worked. – Alok Apr 27 '17 at 08:12

1 Answers1

0

comma separator worked.

create or replace procedure text_generate as
CURSOR c1 is SELECT * FROM emp;
v1 c1%rowtype;
z1 VARCHAR2(2000);
z2 VARCHAR2(2000);
f1 utl_file.file_type;
BEGIN
f1:=utl_file.fopen('TEST_DIR1','out.xls','W');
FOR I IN C1
loop
z1:=i.empno||','||i.ename||','||i.JOB||','||i.sal||','||i.deptno;
Z2:=Z2||CHR(10)||Z1;
END LOOP;
UTL_FILE.PUT_LINE(F1,Z2);
utl_file.fclose(f1);
END;
Alok
  • 61
  • 1
  • 8