export from oracle with toad using colsep

bceagles
bceagles used Ask the Experts™
on
I have been using this tab formatting without problem using Toad version 9.  I can spool to a file with xls extension and open it as a perfectly formatted excel file

col TAB# new_value TAB NOPRINT
select chr(9) TAB# from dual;
set feed off
set linesize 2000
set pagesize 50000
set heading on
set colsep '&TAB'

Now with an upgrade to Toad 10, the colsep is no longer recognized in the output; the columns are not separated at all.  The script has not changed.  Could there be some setting in Toad that is overriding my designated formatting?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
Not a Toad person so I can't help with that directly.

The old-school way was to just concatenate the columns:

select col1 || chr(9) || col2 ...
I have tried running the sample code in both Toad 9.7.2.5 and 10.1.1.8 and in both cases the file loaded into excel is treated as having a single value, not multiple values (one to each cell) i.e. the tab is being ignored!
I always use comma-delimited fields when I want to create a csv file to display correctly in Excell.
Commented:
I figured it out in the meantime.  The csv with comma delimiter works but I had to apply more formatting solutions to retain leading zeros in some fields when opened in Excel and handle field that also contain commas.  I would still like to know what happened with the two Toad versions, but I have a solution now.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial