Export a comma or vertical bar delimited file?
 
Home

Resources
Library
How Can I?
Presentations
Links
Book Reviews
Downloads
User Groups


General
Contact Us
About Us
Services
Legal Notices & Terms of Use
Privacy Statement
Working with LONGs
Too often, in the Oracle Technology Database Forums, I see questions regarding how to write data from one or more tables into a comma delimited file for use in Excel or some other product.

I personally think moving data out of the database a bad practice. And that moving anything into Excel is essentially a statement that "audit trails and the quality of our data are unimportant." But given the large number of requests I have put together these demos showing how to capture string, numeric, and date column values.

So step one lets create a table and insert some data we can export.
CREATE TABLE test (
fname   VARCHAR2(20),
lname   VARCHAR2(20),
num_col NUMBER(5,2),
dat_col DATE);

INSERT INTO test (fname, lname, num_col, dat_col) VALUES ('Dan', 'Morgan', 1, SYSDATE-1);
INSERT INTO test (fname, lname, num_col, dat_col) VALUES ('Tom', 'Kyte', 100.42, SYSDATE);
INSERT INTO test (fname, lname, num_col, dat_col) VALUES ('Richard', 'Foote', 0.64, SYSDATE+1);
COMMIT;

SELECT * FROM test;
The first thing we will practice is extracting strings. We will produce the output first by concatenating the string columns: Then we will do so with  delimiters.
SELECT fname, lname
FROM test;

SELECT fname || lname
FROM test;

-- space delimited
SELECT fname || ' ' || lname
FROM test;

-- comma delimited
SELECT fname || ',' || lname
FROM test;

-- vertical bar delimited
SELECT fname || '|' || lname
FROM test;
Now lets add in the numeric column. This works but forces Oracle to perform an implicit conversion. We will modify it to cast num_col as a string too.
SELECT fname || ',' || lname || ',' || num_col
FROM test;

SELECT fname || ',' || lname || ',' || TO_CHAR(num_col)
FROM test;

SELECT fname || ',' || lname || ',' || TO_CHAR(num_col, '999.99')
FROM test;
Now lets add the date column. Remembering again that it is best not to depend on explicit conversion.
SELECT fname || ',' || lname || ',' || num_col || ',' || dat_col
FROM test;

SELECT fname || ',' || lname || ',' || num_col || ',' || TO_CHAR(dat_col)
FROM test;

SELECT fname || ',' || lname || ',' || num_col || ',' || TO_CHAR(dat_col, 'MM/DD/YYYY')
FROM test;

SELECT fname || ',' || lname || ',' || num_col || ',' ||
TO_CHAR(dat_col, 'MM/DD/YYYY HH24:MI:SS')
FROM test;
We can add quotes to the strings too to make it easier to understand the data types of the values
SELECT '''' || fname || '''' || ',' || '''' || lname || '''' || ',' || num_col
FROM test;
And finally output the entire string as an Oracle insert statement that could be run in a different schema.
SELECT 'INSERT INTO test (fname, lname, num_col, dat_col) VALUES (' || '''' ||
fname || '''' || ',' || '''' || lname || '''' || ',' || num_col || ',' || 'SYSDATE);'
FROM test;

-- copy one of the statements and run it.

SELECT * FROM test;
 
 
Related Topics
Oracle Functions
SQL*Plus
 
Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2013 Daniel A. Morgan All Rights Reserved