1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

how to export data into csv file

Discussion in 'SQL PL/SQL' started by karthikeyanc2003, Oct 9, 2009.

  1. karthikeyanc2003

    karthikeyanc2003 Active Member

    Messages:
    24
    Likes Received:
    0
    Trophy Points:
    100
    hi folks,
    i am having a table with 5 colulmns and 1 million record how to export the data into a csv or sql file with some delimiter


    thanks in advance
    karthikeyan c
     
  2. debasisdas

    debasisdas Active Member

    Messages:
    46
    Likes Received:
    3
    Trophy Points:
    90
    Location:
    Bangalore, India
    try this manually
    Code (Text):
    spool data.csv

    select c1,c2,c3,c4,c5 from table_name;

    spool off
    That can be easily done by using any tool like toad or plsql developer.
     
  3. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    That query won't produce a CSV file. The one in this example will, however:

    Code (SQL):
    CREATE TABLE mytable(
    c1 NUMBER,
    c2 varchar2(40),
    c3 varchar2(80),
    c4  NUMBER,
    c5 varchar2(8));

    BEGIN
         FOR i IN 1..1000 loop
             INSERT INTO mytable
             VALUES (i, 'Test '||i, 'Data for test row '||i, MOD(i,43)+1, 'Final');
         END loop;

         commit;

    END;
    /

    SELECT c1||',"'||c2||'","'||c3||'",'||c4||',"'||c5||'"'
    FROM mytable

    spool DATA.csv
    /
    spool off
     
    Tom Kyte also has examples at asktom.oracle.com which are really worth examining.