CODEWITHSUNDEEP
oracle-databasecsvplsqloracle11gtoad
Random guy
Random guy

Reputation: 923

how to show data in CSV in different colums using plsql?

I have written sql to generate the output data in CSV format.I have used spool to generate in CSV.

SET LINESIZE 1000 TRIMSPOOL ON FEEDBACK OFF

SPOOL E:\oracle\extract\emp2.csv

  SELECT emp_id,
           emp_name

    FROM   offc.employee
    ORDER BY emp_id;

SPOOL OFF

SET PAGESIZE 14

The output is coming in location.The output is generated correctly but when I see the CSV file ,then all the data are coming in same column A in CSV.

enter image description here

I want emp_id in column A and emp_name in columnB.Why they are coming in same column? What is the problem here?

Upvotes: 0

Views: 1091

Answers (3)

Popeye
Popeye

Reputation: 35900

Here, the issue is the space is considered as the separator for your column data and excel can not separate the column values which are space separated.

You can use colsep attribute along with other attributes as following:

set colsep , -- defines column data separator
set pagesize 14 -- defines size of the page. Keep it large so that header is not repeated
set trimspool on -- remove trailing blanks
set lines 1000 -- linesize should be more than sum of width of the all columns
set FEEDBACK OFF -- removes the comment at the end of the data

SPOOL E:\oracle\extract\emp2.csv

  SELECT emp_id,
           emp_name

    FROM   offc.employee
    ORDER BY emp_id;

SPOOL OFF

Cheers!!

Upvotes: 3

APC
APC

Reputation: 146209

What is the problem here?

Excel expects a Comma Separated Values file to have values in columns separated by commas. Your query outputs two columns of data but doesn't include an explicit separator. So when Excel reads the file it doesn't find any commas so it renders a single column of data.

There are various ways of solving this. One is include your own explicit separator in the query:

SELECT emp_id
      || ', ' ||
      emp_name
FROM   offc.employee
ORDER BY emp_id;

Another is to use the SQL*Plus colsep command to format the output in the file. A third option is to use a tool like SQL Developer, whose Export feature handles all this for us.

Upvotes: 2

Littlefoot
Littlefoot

Reputation: 142705

Talking about my own experience: although Excel knows how to open a CSV file, it is kind of stupid and still puts everything into the first column. Therefore, I prefer creating a TXT file instead, using a column separator (so - yes, it basically is a comma-separated-file (or whichever separator you choose)).

For example:

SQL> set pagesize 100
SQL> set linesize 100
SQL> set colsep ";"
SQL>
SQL> spool emp.txt
SQL>
SQL> select * from emp;

     EMPNO;ENAME     ;JOB      ;       MGR;HIREDATE;       SAL;      COMM;    DEPTNO
----------;----------;---------;----------;--------;----------;----------;----------
      7369;SMITH     ;CLERK    ;      7902;17.12.80;       800;          ;        20
      7499;ALLEN     ;SALESMAN ;      7698;20.02.81;      1600;       300;        30
      7521;WARD      ;SALESMAN ;      7698;22.02.81;      1250;       500;        30
      7566;JONES     ;MANAGER  ;      7839;02.04.81;      2975;          ;        20
      7654;MARTIN    ;SALESMAN ;      7698;28.09.81;      1250;      1400;        30
      7698;BLAKE     ;MANAGER  ;      7839;01.05.81;      2850;          ;        30
      7782;CLARK     ;MANAGER  ;      7839;09.06.81;      2450;          ;        10
      7788;SCOTT     ;ANALYST  ;      7566;09.12.82;      3000;          ;        20
      7839;KING      ;PRESIDENT;          ;17.11.81;      5000;          ;        10
      7844;TURNER    ;SALESMAN ;      7698;08.09.81;      1500;         0;        30
      7876;ADAMS     ;CLERK    ;      7788;12.01.83;      1100;          ;        20
      7900;JAMES     ;CLERK    ;      7698;03.12.81;       950;          ;        30
      7902;FORD      ;ANALYST  ;      7566;03.12.81;      3000;          ;        20
      7934;MILLER    ;CLERK    ;      7782;23.01.82;      1300;          ;        10

14 rows selected.

SQL> spool off;

Now, start Excel and go to Open; choose "All files" (i.e. not only Excel-type files) so that you'd see emp.txt listed. Excel then - in its "Text Import Wizard" - asks you which kind of a file it is (choose delimited):

set the separator (semi-colon in our example)

and - open the file:

Everything is now in its own column.

Upvotes: 0

Related Questions