Extracting From Oracle Into a CSV File

[beachdad]

I need to extract data from Oracle 7.3.4 tables into a *.CSV file. That file will be used by Lawson to load into thier Oracle database (this is a data conversion effort). I am writing scripts to extract the data into temp tables that I have created, but I am not sure how to pull that data from the temp tables into a *.CSV file. I'm using SQL on a HPUX Unix system. Any advice would be welcome. Thank you. beachdad@hotmail.com.

 

[stefanhei]

Hi.
No real need for temp tables. I use following (SQL*Plus)settings to create csv's:

set feedback off
set flush off
set heading off
set headsep off
set timing off
set pagesize 0
set recsep off
set termout off
set trimspool off
set verify off
set wrap off
set colsep ';'
set pause off

Spool to the file(s) you need.
Btw. I prefer db-links when I have to transfer Oracle to Oracle. No external files needed for that.

Stefan

 

[SantaMufasa]

Dad,

Here is code that I run from a script that I call "GenASCII.sql". It prompts you for a table name and an output file into which you want .CSV results to appear.

Section 1 -- GenASCII.sql code:

REM **************************************************************
REM David L. Hunt (file author) distributes this and other 
REM files/scripts for educational purposes only, to illustrate the 
REM use or application of various computing techniques. Neither the 
REM author nor Dasages, LLC, makes any warranty regarding this 
REM script's fitness for any industrial application or purpose nor is 
REM there any claim that this or any similarly-distributed scripts 
REM are error free or should be used for any purpose other than
REM illustration.
REM **************************************************************
set echo off
accept x prompt "Enter the table to ASCII: "
accept y prompt "Enter the flat file to write: "
prompt
prompt Following output is generated script that writes text output from table "&x"
prompt 
set verify off
set feedback off
set heading off
set pagesize 0
set linesize 32767
set trimout on
set trimspool on
spool temp.sql
prompt set echo off
prompt set feedback off
prompt set heading off
prompt set pagesize 0
prompt spool &y
prompt Select
select decode (rownum,1,'''"''||','||'',"''||') || column_name || '|| ''"''' 
  from user_tab_columns
 where table_name = upper('&x');
prompt from &x
prompt /
prompt spool off
prompt set feedback on
prompt set heading on
prompt set pagesize 20
spool off
prompt
prompt Following is text output written to file "&y"
prompt
@temp
set echo off
prompt
prompt Output file = "&y"
prompt

Section 2 -- Sample GenASCII invocation and results:

SQL> @genascii
Enter the table to ASCII: s_dept
Enter the flat file to write: temp.txt

Following output is generated script that writes text output from table "s_dept"

set echo off
set feedback off
set heading off
set pagesize 0
spool temp.txt
Select
'"'||ID|| '"'
||',"'||NAME|| '"'
||',"'||REGION_ID|| '"'
from s_dept
/
spool off
set feedback on
set heading on
set pagesize 20

Following is text output written to file "temp.txt"

"10","Finance","1"
"31","Sales","1"
"32","Sales","2"
"33","Sales","3"
"34","Sales","4"
"35","Sales","5"
"41","Operations","1"
"42","Operations","2"
"43","Operations","3"
"44","Operations","4"
"45","Operations","5"
"50","Administration","1"
"51","Information Tech","1"

Output file = "temp.txt"

You can easily modify the GenASCII.sql code to run against an entire Oracle schema of tables if you need. Let me know if this resolves your need.

Mufasa
(aka Dave of Sandy, Utah, USA @ 07:27 (03Jun04) UTC (aka "GMT" and "Zulu"), 00:27 (03Jun04) Mountain Time)

 

[khobar]

Hi

Added to dave's point you can get an extract of csv


a. By using UTL_FILE command(Not sure oracle 7.0 supports it)


b. You can use tools like toad query editor here you have an option to save the output in different tab limited format

regards
khobar

 

[Spirit1]

Mufasa,
I used your GenASCII and it works great. But I have a few tables that I need to skip one column of information. Its a "audit code" field which causes the rest of the record not to convert when I pull into Access.
How could I modify the script. I'm not knowledgable on script writing so please show me exactly which lines would need modified.
Thanks in Advance!

 

[SantaMufasa]

Spirit,

Good question. I actually wrote the GenASCII.sql script for ease in making such modifications. I recommend your making a copy of the GenASCII.sql script named GenASCIIMod.sql. From the new script, remove the last eight lines (the lines following the "spool off" command.)

Use the new version of the script to create the "temp.sql" file. Before running "temp.sql", edit that script to remove the line of coe that references the "audit_code" field. Save and run the script by issuing the command "@temp" from the SQL*Plus prompt.

Let us know how these changes work for you.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[Spirit1]

Mufasa,
The modification works fine. By looking at the @temp file I also can see all the column names.

One more question? I want to be able to select on a column if it is Not Null in a table. What would be the select statement?

 

[SantaMufasa]

Please clarify: Are you saying that you want to see only those columns from a table which have a NOT NULL constraint and you do not want to see the contents of other columns if the column is nullable? Or are you asking for some other display algorithm, but I have misunderstood?


Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[SantaMufasa]

Rather than wait for your clarification, if you wish to output just the NOT NULL columns in the above script, you could make the following simple adjustment:

select decode (rownum,1,'''"''||','||'',"''||') || column_name || '|| ''"''' 
  from user_tab_columns
 where table_name = upper('&x')
   [b]and NULLABLE = 'N'[/b]
;

Let us know if this satisfies your request.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.