Operating System - HP-UX
1748185 Members
4215 Online
108759 Solutions
New Discussion юеВ

How to export Oracle Data

 
SOLVED
Go to solution
Printaporn_1
Esteemed Contributor

How to export Oracle Data

Hi,

How can I export oracle table to file which is comma-seperated that can use tools like excel to see data ?
I understand that exp command only work for Oracle imp.
Thank in advance
enjoy any little thing in my life
12 REPLIES 12
John Palmer
Honored Contributor

Re: How to export Oracle Data

One simple way is to write a SQL query, something like...

set head off
set feed off
set pages 0

select field1||','||field2||',' ...||fieldn from table;

Watch out for data that contains commas though!

Regards,
John
Printaporn_1
Esteemed Contributor

Re: How to export Oracle Data

Thanks John but I have huge table that my requirement is I want the output as a file.
enjoy any little thing in my life
BLADE_1
Frequent Advisor

Re: How to export Oracle Data

hi,

You could issue an select statement on the table and spool the output to a text file.

> spool c:\test.txt
select from ;
spool off

you could also properly format the output by setting format parameters in sqlplus or inside the select statement.


rgds
Blade
fortune favours the brave
T G Manikandan
Honored Contributor

Re: How to export Oracle Data

spool file-name.csv
set termout off
set pagesize 0
set heading off
set feedback off
//your select statements here on the table//
spool off

you can open the .csv file in excel.


Revert on further help

T G Manikandan
Honored Contributor

Re: How to export Oracle Data

Also check this attachment
BLADE_1
Frequent Advisor

Re: How to export Oracle Data


You can import the data into MS EXCEL using ODBC..

Configure odbc for oracle specifying the userid and service name(connect string) and then open MS Excel and go to DATA menu--> import data--> new database query and then enter the options u require i.e table, system table for a particular user and the columns u require to be included in the query...

This works fine..have tried it on OFFICE XP...

rgds
Blade..
fortune favours the brave
Brian Crabtree
Honored Contributor

Re: How to export Oracle Data

John is correct in how to unload that data from the system. The best way to do it would be something like the following:

sqlplus user/pass <> /dev/null
set head off
set feed off
set linesize 500
set pagesize 10000
spool filedata.lst
select fielda||','||fieldb||','||fieldc from tableA
spool off;
exit;
!

You might have to remove the sqlstatement automaticlly placed at the top, but this should output in CSV.

brian
Stan_17
Valued Contributor
Solution

Re: How to export Oracle Data

Hi,

Apart from what others have suggested...

free version of TOAD has a built-in utility of extracting data in csv format. you might want to look at that if you want to extract more and more tables.

hth,
Stan

Yogeeraj_1
Honored Contributor

Re: How to export Oracle Data

hello,

I'm going to suggest UTL_FILE for this task since:

- you have great control over the output file format and you seem to need that here.
- you can easily use dbms_job to schedule this procedure to run automatically every day at a certain time.


Here is a working example. I dump dates in a non-standard format (but one that saves all of the fields). you can modify as you wish.

yd@yddb.ora8i.mu> create or replace procedure dump_table_to_csv( p_tname in varchar2,
2 p_dir in varchar2,
3 p_filename in varchar2 )
4 is
5 l_output utl_file.file_type;
6 l_theCursor integer default dbms_sql.open_cursor;
7 l_columnValue varchar2(4000);
8 l_status integer;
9 l_query varchar2(1000)
10 default 'select * from ' || p_tname;
11 l_colCnt number := 0;
12 l_separator varchar2(1);
13 l_descTbl dbms_sql.desc_tab;
14 begin
15 l_output := utl_file.fopen( p_dir, p_filename, 'w' );
16 execute immediate 'alter session set nls_date_format=''dd-mon-yyyy hh24:mi:ss'' ';
17
18 dbms_sql.parse( l_theCursor, l_query, dbms_sql.native );
19 dbms_sql.describe_columns( l_theCursor, l_colCnt, l_descTbl );
20
21 for i in 1 .. l_colCnt loop
22 utl_file.put( l_output, l_separator || '"' || l_descTbl(i).col_name || '"' );
23 dbms_sql.define_column( l_theCursor, i, l_columnValue, 4000 );
24 l_separator := ',';
25 end loop;
26 utl_file.new_line( l_output );
27
28 l_status := dbms_sql.execute(l_theCursor);
29
30 while ( dbms_sql.fetch_rows(l_theCursor) > 0 ) loop
31 l_separator := '';
32 for i in 1 .. l_colCnt loop
33 dbms_sql.column_value( l_theCursor, i, l_columnValue );
34 utl_file.put( l_output, l_separator || l_columnValue );
35 l_separator := ',';
36 end loop;
37 utl_file.new_line( l_output );
38 end loop;
39 dbms_sql.close_cursor(l_theCursor);
40 utl_file.fclose( l_output );
41
42 execute immediate 'alter session set nls_date_format=''dd-MON-yy'' ';
43 exception
44 when others then
45 execute immediate 'alter session set nls_date_format=''dd-MON-yy''';
46 raise;
47 end;
48 /

Procedure created.

yd@yddb.ora8i.mu> exec dump_table_to_csv( 'emp', '/tmp', 'tkyte.emp' );

PL/SQL procedure successfully completed.

yd@yddb.ora8i.mu> host cat /tmp/tkyte.emp
"EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO"
7369,SMITH,CLERK,7902,17-dec-1980 00:00:00,800,,20
7499,ALLEN,SALESMAN,7698,20-feb-1981 00:00:00,1600,300,30
7521,WARD,SALESMAN,7698,22-feb-1981 00:00:00,1250,500,30
7566,JONES,MANAGER,7839,02-apr-1981 00:00:00,2975,,20
7654,MARTIN,SALESMAN,7698,28-sep-1981 00:00:00,1250,1400,30
7698,BLAKE,MANAGER,7839,01-may-1981 00:00:00,2850,,30
7782,CLARK,MANAGER,7839,09-jun-1981 00:00:00,2450,,10
7788,SCOTT,ANALYST,7566,09-dec-1982 00:00:00,3000,,20
7839,KING,PRESIDENT,,17-nov-1981 00:00:00,5000,,10
7844,TURNER,SALESMAN,7698,08-sep-1981 00:00:00,1500,0,30
7876,ADAMS,CLERK,7788,12-jan-1983 00:00:00,1100,,20
7900,JAMES,CLERK,7698,03-dec-1981 00:00:00,950,,30
7902,FORD,ANALYST,7566,03-dec-1981 00:00:00,3000,,20
7934,MILLER,CLERK,7782,23-jan-1982 00:00:00,1300,,10


See the Supplied Packages guide for info on utl_file. You must set some init.ora parameters to enable it to work correctly....

This can be done via sqlplus as well -- just not as nicely in my opinion.

HTH
Yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)