To Generate a CSV file

To create a CSV file, commas need to be concatenated between the fields.

Sets:

• Pagesize set to zero turns off page breaks and headers
• Feedback set to off removes the record counts from the bottom of the file
• Trimspool set to off removes extra spaces from the end of the record

Alpha fields must be concatenated with double quotes in case it contains a comma.

Set Echo Off
Set Term Off
Set Pagesize 0
Set Feedback off
Set Trimspool On

Spool emp1.csv

Select -----------------------------------------------------
     -- Trim the leading spaces off a number
     -----------------------------------------------------
     LTrim(Sal,' ')  
     ||   
     -----------------------------------------------------
     -- Insert a comma for a column separator
     -----------------------------------------------------
     ','             
     ||   
     -----------------------------------------------------
     -- An Alpha Field with possible embedded commas need
     -- double quotes
     -----------------------------------------------------
    '"'|| EName ||'"'      
From Emp;

Spool off

Set Term on

Prompt EMP1.CSV has been generated.

Create a Flat File

SETS--

• SPACE: set spacing between columns to none
• PAGESIZE: set pagesize to 0 to turn off all headings
• TRIMSPOOL: set off so blanks at the end are not removed
• FEEDBACK: set off -- no record count needed at end
• TERM: set off the terminal to speed up the spooling
• LINESIZE: set the output file record length

Select fields as shown:

Filler Fields:
The placement of each field is dependent on the one before it. If you need a filler field, code it as '  ' (Like the 3rd field listed below.)

For alphabetic fields:
1) Always include SUBSTR function. With it, if the database column size is increased, it will be handled.
2) Use NVL on all columns that can potentially be NOT NULL. The number of spaces between the quotes should match the substr length.

For numeric fields:
1) Change it to character with TO_CHAR (This will left-justify it).
2) Left pad with zeros up to the necessary length with LPAD.

SET SPACE 0
SET PAGESIZE 0 
SET TRIMSPOOL OFF
SET FEEDBACK OFF
SET TERM OFF    
SET LINESIZE 40
/*      The spool file name should be lower case if writing to Unix.      */
SPOOL flatfile.txt

SELECT LPAD(TO_CHAR(sal),8,0)              ,
       NVL(SUBSTR(ename,1,15),'          '),
       'HELLO'                                 -- write a literal
  FROM Emp;

SPOOL OFF

/*If writing to Unix, set permissions for all to access */
-- host chmod 777 flatfile.txt