Menu
Anyone have a good set of sqlplus configuration directives to help transform a given sql query into nicely tab separated output for pulling into a spreadsheet or further processing?
645
The TRIMSPOOL setting controls whether SQL*Plus writes trailing spaces when spooling data to a file. The default setting is OFF, which causes SQL*Plus to write each line to the spool file in its entirety, trailing spaces and all.
As Justin pointed out in his link, using the set colsep function SQLPlus command saves typing a separator for each column.
But for tab-delimited, set colsep Chr(9) won't work.
For UNIX or LINUX, use set colsep ' ' with the space between the single-quotes being a typed tab.
For Windows, use these settings:
col TAB# new_value TAB NOPRINT
select chr(9) TAB# from dual;
set colsep "&TAB"
select * from table;
One particular script that I have stolen on more than one occasion comes from an AskTom thread on extracting data to a flat file. If I needed a quick and dirty flat file out of SQL*Plus. I would tend to prefer the DUMP_CSV function Tom posted earlier on that thread for any sort of ongoing process, though.
35
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
