-- extract_to_csv.sql   Extract to CSV file Example

--**Use this information and these scripts at your own risk. As a condition of using these scripts and information from this site, you agree to hold harmless both the University of Arkansas Cooperative Extension Service and Bruce Knox for any problems that they may cause or other situations that may arise from their use, and that neither the Extension Service nor I will be held liable for those consequences. The scripts and information are provided "as is" without warranty, implied or otherwise. Limitation of liability will be the amount paid to the University of Arkansas specifically for this information. (It was free:) 

SET ECHO OFF

-- change log
-- 08/22/07 bknox Created


-- CSV or Comma Separated Values files are frequently used to move 
-- between applications or platforms. 


-- This variation of the Delimited Text File is very portable.
-- This flat file is often very busy, but readable with a simple editor.

-- The usual way is to always "optionally" enclose the values in double quotes.
-- This prevents errors when embedded commas or newlines are unexpectedly found in the data.  

-- With these files, embedded double quotes must be quoted, i.e., doubled.
-- I suggest using REPLACE(value,'"','""') only when double quotes prove to be a problem.


-- Unexpected Results in Excel?

-- By default Excel will display values with leading zeros stripped, 
-- leading and trailing spaces stripped, and large numbers converted to scientific notation. 

-- These display format changes can be made to display properly by your Excel users
-- and if the file is to be used with Excel only, 
-- you can prevent them from appearing by 
-- importing the values as an Excel Formula for text. 

-- What could possibly be that large and still need to be read? A code or an account number. 

-- Excel ignores that you have defined a number as text by 
-- enclosing the text string "number" with double quotes.  A number is a number to Excel.

-- If you do not want these Excel display conversions to appear, 
-- then use a formula to convert the numbers to text:
-- use ="value" instead of "value"
-- Since Excel by default does not display the Formula, only the results, 
-- then your text "numbers" will appear as intended.  

-- This trick will also work for leading and trailing spaces (or unprintable characters).

-- Retaining Formats when a .csv file is opened by Excel using an Excel Formula probably will 
-- cause other spreadsheet apps to fail.

-- By the way, when the spreadsheet generated is saved from Excel as .csv, 
-- the optional double quotes and the Excel Formulas are dropped.  
-- It will provide double quotes where needed.


-- You can concatenate the values exported with commas using 
-- ',' or CHR(44) and
-- '"' or CHR(34)

-- '"'||      is good for the start
-- ||'","'||  is good for all but the first and last
-- ||'"'      is good for the last
-- ||'",="'|| will turn the next value into a formula ="value" 


ALTER SESSION SET NLS_DATE_FORMAT = 'MM/DD/YYYY';

SET SHOWMODE OFF
SET TIMING OFF
SET FEEDBACK Off
SET HEADING OFF
SET LINESIZE 512
SET TRIMSPOOL ON
SET PAGESIZE 0          
SET NEWPAGE 0
SET VERIFY OFF
SET TERMOUT OFF

-- SET ECHO OFF     Suppress Display of commands
-- SET FEEDBACK OFF Suppress Display of record counts
-- SET LINESIZE n   Set line length
-- SET PAGESIZE n   Set lines per page
-- SET SHOWMODE OFF Suppress Display of old and new setting of SQL*Plus system variables
-- SET NEWPAGE  0   Set no lines before TITLE and eject frist page
-- SET VERIFY   ON  Show each line of the file before and after substitution
-- SET TERMOUT  OFF Suppress Display of output

CLEAR COLUMNS

COLUMN Sort_Name NOPRINT

TTITLE OFF
BTITLE OFF

CLEAR BREAK
CLEAR COMPUTE

SET TERMOUT OFF
SET HEADING OFF
SET PAGESIZE 0   
-- no page breaks

SET WRAP OFF
SET SPACE 0      
-- no space(s) between columns

SET TIME OFF
SET TIMING OFF   
-- prevent time stamp at end of report

SET FEEDBACK OFF

COLUMN Sort_Order NOPRINT

SET LINESIZE 512
SET TRIMSPOOL ON


!echo
!echo "extract_to_csv beginning"
!echo

!rm -f extract_to_csv.csv
SPOOL extract_to_csv.csv

-- First Row will be used for Column names 
SELECT '"Name","Trans_Number","Trans_Date","Amt","Commodity"' FROM DUAL;

SELECT  '"'||
 Name      ||'",="'|| -- note the use of formula ="value" to preserve the text display for the large transaction number following:
 Trans_Num ||'","'||
 Trans_Date||'","'||
 Amt       ||'","'||
 SalesTax  ||'","'||
 Commodity ||'"'
  FROM tablename
ORDER BY Name, Trans_Date;


SPOOL OFF

CLEAR COLUMNS
CLEAR BREAKS
CLEAR COMPUTES
SET NUMWIDTH 10
SET HEADING ON
SET SPACE 1

!echo
!echo "extract_to_csv complete"
!echo

SET FEEDBACK ON
SET VERIFY OFF

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YY';

SET TERMOUT ON