Registration Forgot password?
Login
Password
Remember me
Login through VKontakte
General > Articles > Saving result of select into csv file

Saving result of select into csv file

When I need fast send csv file with selecting results, I can't see better solution that sqlplus + spool

-- set name of output file
spool output.csv

-- switch off displaing time after select
set timing off
-- switch off displaying column headers
set heading off
-- set space between new page start and old page finish (line count)
-- if you have 64-bit OS (Windows 7 for example) and 32-bit database (Oracle 11 XE for example) when you 
-- set newpage parameter to 0 you can found in output file character with code 255 (0xFF) in this case 
-- simples way set newpage 1 and just skip empty line on loading csv file
set newpage 0
-- switch off output selecting data on screen
set termout off
-- set line size must be large or equal output string length
set linesize 1000
-- switch off blank space from end of output line to [linesize]
set trimspool off
-- switch off rows count output
set feedback off


-- there your select just concatenate all your columns in one (to remove blank space in output file)
select level || ',' || 
       'Some test string' || 
       to_char(sysdate - level / 10, 'DD.MM.YYYY HH24:MI:SS') 
  from dual connect by level < 1001;


spool off

quit
You can add comment on current page, or on forum page there.

Last comment

Name*E-mail
Code*