Home » SQL & PL/SQL » SQL & PL/SQL » Create tab delimited report for excel (10gR2)
Create tab delimited report for excel [message #387315] Wed, 18 February 2009 17:12 Go to next message
jordan_dba
Messages: 19
Registered: May 2007
Location: SF, CA
Junior Member
I created a report that is comma delimited that is spool as a csv file and is opened in Excel but someone just entered some data that includes commas in one of the columns hence Excel doesn't know how to handle that. I am trying to separate the columns using tabs and all my research shows separating the columns using ||chr(9)|| but that hasn't worked well when I open the report in Excel. Any help will be appreciated.

Example :

select 'first_name||chr(9)||middle_name||chr(9)||last_name||chr(9)||social_security'from dual;
select a.first_name||chr(9)||a.middle_name||chr(9)||a.last_name||chr(9)||b.social_security_num
from names_table a,
social_number_table b
where a.id= b.id



When the above is spooled to a csv file and opened in Excel it doesn't open with each column separated.
Re: Create tab delimited report for excel [message #387316 is a reply to message #387315] Wed, 18 February 2009 17:21 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>When the above is spooled to a csv file and opened in Excel it doesn't open with each column separated.
So please explain again how/why the problem with Excel should be solved in an Oracle forum.

Could it be that you lied/confused Excel?

CSV - COMMA Separated Variables!

If you created a file call mydata.csv, do you think Excel would have a problem finding the commas in this file when they do not exist?

What happens if you call the file mydata.txt; instead?

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/

Re: Create tab delimited report for excel [message #387318 is a reply to message #387316] Wed, 18 February 2009 17:40 Go to previous messageGo to next message
jordan_dba
Messages: 19
Registered: May 2007
Location: SF, CA
Junior Member
Excellent catch mate... that seemed to solve it but for the column headers... which is the select from dual part.
Re: Create tab delimited report for excel [message #387371 is a reply to message #387318] Thu, 19 February 2009 00:50 Go to previous message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
SQL> set colsep ','
SQL> set underline off
SQL> select * from emp;
     EMPNO,ENAME     ,JOB      ,       MGR,HIREDATE           ,       SAL,      COMM,    DEPTNO
      7369,SMITH     ,CLERK    ,      7902,17/12/1980 00:00:00,       800,          ,        20
      7499,ALLEN     ,SALESMAN ,      7698,20/02/1981 00:00:00,      1600,       300,        30
      7521,WARD      ,SALESMAN ,      7698,22/02/1981 00:00:00,      1250,       500,        30
      7566,JONES     ,MANAGER  ,      7839,02/04/1981 00:00:00,      2975,          ,        20
      7654,MARTIN    ,SALESMAN ,      7698,28/09/1981 00:00:00,      1250,      1400,        30
      7698,BLAKE     ,MANAGER  ,      7839,01/05/1981 00:00:00,      2850,          ,        30
      7782,CLARK     ,MANAGER  ,      7839,09/06/1981 00:00:00,      2450,          ,        10
      7788,SCOTT     ,ANALYST  ,      7566,19/04/1987 00:00:00,      3000,          ,        20
      7839,KING      ,PRESIDENT,          ,17/11/1981 00:00:00,      5000,          ,        10
      7844,TURNER    ,SALESMAN ,      7698,08/09/1981 00:00:00,      1500,         0,        30
      7876,ADAMS     ,CLERK    ,      7788,23/05/1987 00:00:00,      1100,          ,        20
      7900,JAMES     ,CLERK    ,      7698,03/12/1981 00:00:00,       950,          ,        30
      7902,FORD      ,ANALYST  ,      7566,03/12/1981 00:00:00,      3000,          ,        20
      7934,MILLER    ,CLERK    ,      7782,23/01/1982 00:00:00,      1300,          ,        10

Regards
Michel
Previous Topic: Group By Clause with Order By Clause
Next Topic: a question about explain plan
Goto Forum:
  


Current Time: Sat Feb 15 23:52:55 CST 2025