The
University
of
Vermont

 

Producing a file in "Delimited" format. 


Frequently, you will want to grab some data out of a relational database repository,
and then "do something with it".  Something like: Use a word-processing program to do a
"mail merge" and generate letters that include some fields from your query; or pull 
the data into a spreadsheet to produce a graph for a report or presentation; or
pull the data in a desktop database like approach for further manipulation.

One easy way to do this is to use SQL to produce a file in "delimited text file format".

Looks like this:
"First Name","Last Name","Street1","City","State","Zip"
"Kennedy","Keith","108 Lois Lane","Warren","VT","05674"
"Clinton","William","1600 Pennsylvania Ave","Washington D.C.","DC","20500"

To create a file like this follow these steps:
1) Tell SQLPLUS that you don't want headings, footings, timing information,
page breaks,etc.etc.etc. Use something like this:

/* This is what you need to do to get a "flat"  */
/* file output.                                  */
SET NEWPAGE 0
SET SPACE 0
SET LINESIZE 300
SET PAGESIZE 0
SET ECHO OFF
SET FEEDBACK OFF
SET HEADING OFF
SET TIME OFF
SET TIMING OFF
set wrap off

2) Tell SQLPLUS that you want the results from your SELECTs written to a file.
     SPOOL Dec1998_address_file.txt

     Note: Some programs look at the file extension, and decide what to do with the
     file based on the extension.  I'm using ".txt" here as that seems to wok well
     most of the time. if you don't specify an extension explicitly in the SPOOL command,
     SQLPLUS uses ".lst".  If the program you are importing into just doesn't get that this
     is a delimited file, you may need to use a different extension.

3) Generate the "Labels" line.  Use the "select <literal> from dual" technique.
     SELECT '"First Name","Last Name","Street1","City","State","Zip"' from dual;

4) Generate the data lines. 

    SELECT '"'||SPRIDEN_FIRST_NAME||'","'||
                       SPRIDEN_LAST_NAME||'","'||
                       SPRADDR_STREET_LINE1||'","||
                       SPRADDR_CITY||'","'||
                       SPRADDR_STAT_CODE||'","'||
                       SPRADDR_ZIP||'"'
     FROM SPRIDEN,SPRADDR
     WHERE whatever;

5) Copy the file to your desktop computer.
     Fire up WS-FTP (See http://cit.uvm.edu/software/ to download a copy if necessary).
     Connect to giraffe.uvm.edu.
     Use your regular giraffe account name.
     When prompted for password, use your PIN and SecureID combo (Passcode).
     Select the desired target directory in the left hand window.
     Find the file in the right hand window.
     Select ASCII mode.
     Press the arrow to copy the file.

6) Open up your desktop program, and either "OPEN" or "IMPORT" the .txt file.
     You may need to tell your program that it is a "delimited text file".
 
 
 
Brought to you through the courtesy of Computing and Information Technology, University of Vermont. Copyright © 1996 The University of Vermont and others. All rights reserved.

The University supports both institutional and personal web pages. The views expressed on personal web pages are strictly those of the author, and are not reviewed or approved by the University of Vermont

Send questions and comments to  Keith.Kennedy@uvm.edu

Read the Webmaster's Policies.
Last page update: November 11, 1998