On the move:

I'm currently moving the following to their own blogs: Unix, HTML for Blogs, Perl & Java.
The links are on the left side.

Monday, May 26, 2008

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.

No comments:

Post a Comment