Results 1 to 8 of 8

Thread: helpppppp

  1. #1
    Join Date
    Feb 2004
    Location
    india
    Posts
    23

    Unanswered: helpppppp

    are there any scripts to extract data in the tables from sql plus into a comma seperated or an xls file?
    Please help.

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: helpppppp

    Originally posted by sching
    are there any scripts to extract data in the tables from sql plus into a comma seperated or an xls file?
    Please help.
    Here is a simple procedure (attached). It uses DBMS_OUTPUT, so is limited to 1 million bytes of output (not suitable for very large tables).

    Works like this:

    SQL> set serverout on size 1000000
    SQL> exec table_to_csv('select * from emp')
    "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","19-apr-2087 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","23-may-2087 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"
    Attached Files Attached Files

  3. #3
    Join Date
    Sep 2003
    Posts
    156

    Re: helpppppp

    Originally posted by sching
    are there any scripts to extract data in the tables from sql plus into a comma seperated or an xls file?
    Please help.
    example...

    -- start of script

    set linesize 5000
    set pagesize 0
    set feedback off
    set trimspool on
    select a.COL1 || '|' || a.COL2 || '|' || a.COL3 || '|' || a.COL4
    from owner.table_name a;

    spool filename.csv
    /
    -- end of script
    rgs,

    Ghostman

  4. #4
    Join Date
    Feb 2004
    Location
    india
    Posts
    23

    Re: helpppppp

    hi,
    Thanks very much for your help. But I want the output to automatically go into a .csv file. How should I proceed?



    Originally posted by andrewst
    Here is a simple procedure (attached). It uses DBMS_OUTPUT, so is limited to 1 million bytes of output (not suitable for very large tables).

    Works like this:

    SQL> set serverout on size 1000000
    SQL> exec table_to_csv('select * from emp')
    "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","19-apr-2087 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","23-may-2087 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"

  5. #5
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: helpppppp

    You could create a script called spool_to_csv like this:
    set term off feedback off
    spool &1
    exec table_to_csv('&2')
    spool off
    set term on feedback on

    Then run it like this:

    @spool_to_csv emp.csv "select * from emp"

    The first parameter is the filename, the second is the query.

  6. #6
    Join Date
    Feb 2004
    Location
    india
    Posts
    23

    Re: helpppppp

    hi,
    Thanks for the reply.
    This works fine . But I have to execute each time for seperate tables - since want to export data from all the tables in scott to their seperate csv file at one shot.. Here I have to give each table name.
    Could you tell me how?

  7. #7
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: helpppppp

    Run this:

    select '@spool_to_csv ' || table_name || '.csv "select * from ' || table_name || '"'
    from user_tables

    Then run the output from that. Combine the operations into a script if you like.

  8. #8
    Join Date
    Feb 2004
    Location
    india
    Posts
    23

    Re: helpppppp

    hi,
    Thanks very much for your help. But I want the output to automatically go into a .csv file. How should I proceed?



    Originally posted by andrewst
    Here is a simple procedure (attached). It uses DBMS_OUTPUT, so is limited to 1 million bytes of output (not suitable for very large tables).

    Works like this:

    SQL> set serverout on size 1000000
    SQL> exec table_to_csv('select * from emp')
    "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","19-apr-2087 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","23-may-2087 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"

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •