Navigation
Recent Posts
- Macro to insert footer
- $this PHP Variable
- php5 extensions directory and mysql undefined function
- Windows Vista biggest disappointment of 2007
- Excel giving incorrect results
- SnagIt 8.2.0 workaround
- Find Matching Text in Excel
- Excel macro to copy to Custom Template
- UK Post Code GeoCoding
- Accept User Input in SQL*Plus
- Exit SQLPlus from Batch File
- Run Multiple SQLPlus Queries via DOS batch file
- CSS Browser Differences IE and Firefox
- Create csv import file from Excel
- Use Query Result as Variable in Batch File
Archive
- November-2009
- August-2008
- January-2008
- December-2007
- November-2007
- October-2007
- September-2007
- August-2007
- June-2007
- May-2007
- April-2007
- March-2007
- February-2007
- January-2007
SQL*Plus Spool File
Saturday 24 Mar, 2007 - 12:40pm | 1 comments |
If you want to produce a csv file from a SQL select query in SQL*Plus use the following syntax.
set echo off
set heading off
set feedback off
set pagesize 0
spool c:\filename.csv
select col1||','||col2 from db_table;
spool off
set heading off
set feedback off
set pagesize 0
spool c:\filename.csv
select col1||','||col2 from db_table;
spool off
This will produce a csv formatted file. Straight to excel, change csv to xls but bear in mind two potential issues (a) row limit in Excel, and (b) leading zeros in number fields will be lost in excel.
The leading zeros can be resolved if you instead spool the file as a txt as you will be forced to use excels conversion wizard to open the file.
Posted in: Business
Tags: Oracle | SQL | SQL*Plus | csv
..
Wednesday 29 Sep, 2010 - 15:44pmYou can use:
set colsep ,
instead of concatenating the fields...
--
Andres Santana
Posted by: Anonymous