Search code examples
oraclecsvoracle-sqldeveloper

Avoid double quotes around column while spooling to csv from oracle

I need to export a select query output to text file using spool. The output by default adds double quotes around the column. How to Prevent it? The query returns only one column.

Ideally i need to remove headers as well as the double quotes from the output. below is the script file i use in oracle Developer. but heading is also not removed.

set echo off
SET HEADING OFF
SET PAGESIZE 0
SET COLSEP ''
spool 'D:\public\cvs_txPCG.txt'
select /*csv*/ 
pcg from temptx;
spool off;

output

"PCG"
"76259737020150320000504281565213310052440093515652109.2909.290101"
"19519905620160502000504283153419040044861008644759203.3903.390101"
"49424051620160220000504284594314590009220713032964404.3804.380202"
"88761197020151025000504284594315180036700812132964401.9901.990101"
Solution

  • You can't get rid of the double-quotes with the /*csv*/ directive. The slightly more convenient way of doing this now is with the sqlformat command, but even with sqlformat delimited you have to have the string enclosed.

    Using CSV format for a single column seems rather pointless anyway though, unless you have delimiters in your string you want to escape. If you are really exporting that one column and don't have delimiters then just leave out the /*csv*/ directive. If you have multiple columns or strings that may contain delimiters then the enclosures are useful to stop those delimiters being misinterpreted when opening in Excel or some other external tool.

    If you really want CSV format without enclosures you can either build up the output manually by concatenating the column(s) with comma literals, which still allows you to spool to a file:

    select pcg ||','|| some_other_col ||','|| etc from ...

    Or you can run the query using Run Statement (Ctrl-Enter) and then export the results from the data grid, from the context menu that appears when you right-click on the output. When you export and choose CSV or delimited format you can choose whether to include the header and which delimiter and enclosures to use - and you're allowed to choose 'none'. If you don't want to set that on each export you can set it as a default, from Tools->Preferences:

    enter image description here

    You can't do it with the /*csv*/ directive or sqlformat csv though.