This discussion is archived
1 2 Previous Next 22 Replies Latest reply: Mar 22, 2013 10:00 AM by rp0428 RSS

Create a CSV file

455054 Newbie
Currently Being Moderated
Is it possible to save the query result as a CSV?
  • 1. Re: Create a CSV file
    Warren Tolentino Expert
    Currently Being Moderated
    yes by using the SPOOL.

    example:
    SQL> describe emp;
     Name                                      Null?    Type
     ----------------------------------------- -------- ---------------------------
     EMPNO                                     NOT NULL NUMBER(4)
     ENAME                                              VARCHAR2(10)
     JOB                                                VARCHAR2(9)
     MGR                                                NUMBER(4)
     HIREDATE                                           DATE
     SAL                                                NUMBER(7,2)
     COMM                                               NUMBER(7,2)
     DEPTNO                                    NOT NULL NUMBER(2)
    employee.sql script contains:
    set pagesize 10000
    
    set feedback off
    set heading off
    set echo off
    
    spool r:\employee.csv
    
    select to_char(empno)||','||rtrim(ename)||','||rtrim(job)||','||to_char(hiredate,'dd-mon-yyyy')
      from emp;
    
    spool off
    
    set echo on
    set heading on
    set feedback on
    when the employee.sql script is run at the SQL*Plus it will create a employee.csv file.

    when the employee.csv is opened:
    7566,JONES,MANAGER,02-apr-1981                                                  
    7902,FORD,ANALYST,03-dec-1981                                                   
    7839,KING,PRESIDENT,17-nov-1981                                                 
    7698,BLAKE,MANAGER,01-may-1981                                                  
    7782,CLARK,MANAGER,09-jun-1981                                                  
    7369,SMITH,CLERK,17-dec-1980                                                    
    7499,ALLEN,SALESMAN,20-feb-1981                                                 
    7521,WARD,SALESMAN,22-feb-1981                                                  
    7654,MARTIN,SALESMAN,28-sep-1981                                                
    7788,SCOTT,ANALYST,09-dec-1982                                                  
    7844,TURNER,SALESMAN,08-sep-1981                                                
    7876,ADAMS,CLERK,12-jan-1983                                                    
    7900,JAMES,CLERK,03-dec-1981                                                    
    7934,MILLER,CLERK,23-jan-1982   
  • 2. Re: Create a CSV file
    455054 Newbie
    Currently Being Moderated
    How would you create a csv from this query?
    SELECT DISTINCT V.VENDOR, R.ADDRNUM, V.VNAMEL, R.AADDR1, R.ACITY, R.ASTATE,
    R.AZIPCODE, R.APHONE, MAX(P.DATEPUR)
    FROM VENDOR V,VENDADDR R,PLANHOLD P
    WHERE V.VENDOR = R.VENDOR
    AND P.VENDOR = R.VENDOR
    AND (P.DATEPUR >= TO_DATE('2002-01-01','YYYY-MM-DD')
    AND P.DATEPUR <= TO_DATE('2005-12-31','YYYY-MM-DD'))
    AND V.VOBSOLET = 'N'
    GROUP BY V.VENDOR, R.ADDRNUM, V.VNAMEL, R.AADDR1, R.ACITY, R.ASTATE,R.AZIPCODE, R.APHONE
    ORDER BY V.VENDOR;
  • 3. Re: Create a CSV file
    JensPetersen Oracle ACE
    Currently Being Moderated
    http://asktom.oracle.com/~tkyte/flat/index.html
  • 4. Re: Create a CSV file
    Warren Tolentino Expert
    Currently Being Moderated
    you can try this:
    set pagesize 10000
    
    set feedback off
    set heading off
    set echo off
    
    spool c:\vendor.csv
    
    SELECT DISTINCT RTRIM(V.VENDOR)||','||RTRIM(R.ADDRNUM)||','||RTRIM(V.VNAMEL)||','|| 
                    RTRIM(R.AADDR1)||','||RTRIM(R.ACITY)||','||RTRIM(R.ASTATE)||','||
                    RTRIM(R.AZIPCODE)||','||RTRIM(R.APHONE)||','||TO_CHAR(MAX(P.DATEPUR),'YYYY-MM-DD')
    FROM VENDOR V,VENDADDR R,PLANHOLD P
    WHERE V.VENDOR = R.VENDOR
    AND P.VENDOR = R.VENDOR
    AND (P.DATEPUR >= TO_DATE('2002-01-01','YYYY-MM-DD')
    AND P.DATEPUR <= TO_DATE('2005-12-31','YYYY-MM-DD'))
    AND V.VOBSOLET = 'N'
    GROUP BY V.VENDOR, R.ADDRNUM, V.VNAMEL, R.AADDR1, R.ACITY, R.ASTATE,R.AZIPCODE, R.APHONE
    ORDER BY V.VENDOR; 
    
    
    spool off
    
    set echo on
    set heading on
    set feedback on
    hope this helps.
  • 5. Re: Create a CSV file
    455054 Newbie
    Currently Being Moderated
    I will try it out and let you know the out come. thanks for response
  • 6. Re: Create a CSV file
    JensPetersen Oracle ACE
    Currently Being Moderated
    A similar solution would be
    alter session set nls_date_format='DD-MON-YYYY';
    set colsep ';'
    set lines 10000
    set pagesize 0
    set echo off
    set feedback off
    set trimspool on

    spool r:\employee.csv

    select empno,ename,job,hiredate
    from emp;

    spool off

    /*
    Which will end as
          7369;SMITH     ;CLERK    ;17-12-1980
          7499;ALLEN     ;SALESMAN ;20-02-1981
          7521;WARD      ;SALESMAN ;22-02-1981
          7566;JONES     ;MANAGER  ;02-04-1981
          7654;MARTIN    ;SALESMAN ;28-09-1981
          7698;BLAKE     ;MANAGER  ;01-05-1981
          7782;CLARK     ;MANAGER  ;09-06-1981
          7788;SCOTT     ;ANALYST  ;19-04-1987
          7839;KING      ;PRESIDENT;17-11-1981
          7844;TURNER    ;SALESMAN ;08-09-1981
          7876;ADAMS     ;CLERK    ;23-05-1987
          7900;JAMES     ;CLERK    ;03-12-1981
          7902;FORD      ;ANALYST  ;03-12-1981
          7934;MILLER    ;CLERK    ;23-01-1982
    */
    Message was edited by:
    Jens Petersen
  • 7. Re: Create a CSV file
    bsc7080oec Newbie
    Currently Being Moderated
    Here is something that is reusable and dynamic. Can be used to generate fixed width, character separated values, include headers or not, etc. Works great.

    FUNCTION dump_csv (
    p_query IN VARCHAR2
    , p_dir IN VARCHAR2
    , p_filename IN VARCHAR2
    , p_separator IN VARCHAR2
    , p_headers IN BOOLEAN DEFAULT FALSE
    , p_trailing_separator IN BOOLEAN DEFAULT FALSE
    , p_max_linesize IN NUMBER DEFAULT 32000
    , p_mode IN VARCHAR2 DEFAULT 'w'
    )
    RETURN NUMBER
    IS
    l_output UTL_FILE.file_type;
    l_thecursor INTEGER DEFAULT DBMS_SQL.open_cursor;
    l_columnvalue VARCHAR2 (4000);
    l_status INTEGER;
    l_colcnt NUMBER DEFAULT 0;
    l_cnt NUMBER DEFAULT 0;
    l_separator VARCHAR2 (10) DEFAULT '';
    l_line LONG;
    l_desctbl DBMS_SQL.desc_tab;
    v_sqlerrm VARCHAR2 (32000);
    l_mode CHAR (1) := 'w';
    BEGIN
    IF p_mode NOT IN ('w', 'a')
    THEN
    l_mode := 'w';
    ELSE
    l_mode := p_mode;
    END IF;

    l_output :=
    UTL_FILE.fopen (p_dir
    , p_filename
    , l_mode
    , p_max_linesize
    );
    DBMS_SQL.parse (l_thecursor
    , p_query
    , DBMS_SQL.native
    );
    DBMS_SQL.describe_columns (l_thecursor
    , l_colcnt
    , l_desctbl
    );

    FOR i IN 1 .. l_colcnt
    LOOP
    DBMS_SQL.define_column (l_thecursor
    , i
    , l_columnvalue
    , 4000
    );

    IF (l_desctbl (i).col_type = 2) /* number type */
    THEN
    l_desctbl (i).col_max_len := l_desctbl (i).col_precision
    + 2;
    ELSIF (l_desctbl (i).col_type = 12) /* date type */
    THEN
    /* length of my date format */
    l_desctbl (i).col_max_len := 20;
    ELSIF (l_desctbl (i).col_type = 8) /* LONG type */
    THEN
    l_desctbl (i).col_max_len := 2000;
    END IF;

    IF p_headers
    THEN
    UTL_FILE.put (l_output, l_separator
    || l_desctbl (i).col_name);
    l_separator := p_separator;
    END IF;
    END LOOP;

    IF p_trailing_separator
    THEN
    UTL_FILE.put (l_output, l_separator);
    END IF;

    IF p_headers
    THEN
    UTL_FILE.new_line (l_output);
    END IF;

    l_status := DBMS_SQL.EXECUTE (l_thecursor);

    LOOP
    EXIT WHEN (DBMS_SQL.fetch_rows (l_thecursor) <= 0);
    l_line := NULL;
    l_separator := '';

    FOR i IN 1 .. l_colcnt
    LOOP
    DBMS_SQL.column_value (l_thecursor
    , i
    , l_columnvalue
    );

    IF NVL (INSTR (l_columnvalue, ','), 0) = 0
    THEN
    NULL;
    ELSE
    l_columnvalue := '"'
    || l_columnvalue
    || '"';
    END IF;

    UTL_FILE.put (l_output, l_separator
    || l_columnvalue);
    l_separator := p_separator;
    END LOOP;

    IF p_trailing_separator
    THEN
    UTL_FILE.put (l_output, l_separator);
    END IF;

    UTL_FILE.new_line (l_output);
    l_cnt := l_cnt
    + 1;
    END LOOP;

    DBMS_SQL.close_cursor (l_thecursor);
    UTL_FILE.fclose (l_output);
    RETURN l_cnt;
    EXCEPTION
    WHEN NO_DATA_FOUND
    THEN
    DBMS_OUTPUT.put_line ('NO_DATA_FOUND');
    UTL_FILE.fclose (l_output);
    RETURN l_cnt;
    WHEN UTL_FILE.invalid_path
    THEN
    DBMS_OUTPUT.put_line ('UTL_FILE.INVALID_PATH');
    UTL_FILE.fclose (l_output);
    RETURN l_cnt;
    WHEN UTL_FILE.read_error
    THEN
    DBMS_OUTPUT.put_line ('UTL_FILE.READ_ERROR');
    UTL_FILE.fclose (l_output);
    RETURN l_cnt;
    WHEN UTL_FILE.write_error
    THEN
    DBMS_OUTPUT.put_line ('UTL_FILE.WRITE_ERROR');
    UTL_FILE.fclose (l_output);
    RETURN l_cnt;
    WHEN UTL_FILE.invalid_mode
    THEN
    DBMS_OUTPUT.put_line ('UTL_FILE.INVALID_MODE');
    UTL_FILE.fclose (l_output);
    RETURN l_cnt;
    WHEN UTL_FILE.invalid_filehandle
    THEN
    DBMS_OUTPUT.put_line ('UTL_FILE.INVALID_FILEHANDLE');
    UTL_FILE.fclose (l_output);
    RETURN l_cnt;
    WHEN UTL_FILE.invalid_operation
    THEN
    DBMS_OUTPUT.put_line ('UTL_FILE.INVALID_OPERATION');
    UTL_FILE.fclose (l_output);
    RETURN l_cnt;
    WHEN UTL_FILE.internal_error
    THEN
    DBMS_OUTPUT.put_line ('UTL_FILE.INTERNAL_ERROR');
    UTL_FILE.fclose (l_output);
    RETURN l_cnt;
    WHEN UTL_FILE.invalid_maxlinesize
    THEN
    DBMS_OUTPUT.put_line ('UTL_FILE.INVALID_MAXLINESIZE');
    UTL_FILE.fclose (l_output);
    RETURN l_cnt;
    WHEN VALUE_ERROR
    THEN
    DBMS_OUTPUT.put_line ('UTL_FILE.VALUE_ERROR');
    UTL_FILE.fclose (l_output);
    RETURN l_cnt;
    WHEN OTHERS
    THEN
    hum_do.default_exception ('ERROR in dump_csv : ');
    UTL_FILE.fclose (l_output);
    RETURN l_cnt;
    END dump_csv;
  • 8. Re: Create a CSV file
    455054 Newbie
    Currently Being Moderated
    I am fairly new to sql but i really like to work with your code b/s I might need to create a csv file more often in the future.Here is my query and i really appreciate if you can show me how i can alter the script to work with my sql script.Here is the script
    thanks


    SELECT DISTINCT V.VENDOR, R.ADDRNUM, V.VNAMEL, R.AADDR1, R.ACITY, R.ASTATE,
    R.AZIPCODE, R.APHONE, MAX(P.DATEPUR)
    FROM VENDOR V,VENDADDR R,PLANHOLD P
    WHERE V.VENDOR = R.VENDOR
    AND P.VENDOR = R.VENDOR
    AND (P.DATEPUR >= TO_DATE('1999-01-01','YYYY-MM-DD')
    AND V.VOBSOLET = 'N'
  • 9. Re: Create a CSV file
    JensPetersen Oracle ACE
    Currently Being Moderated
    Just replace the select statements in the script.
    In case you still have problems with the script, post it again along with the error.
  • 10. Re: Create a CSV file
    bsc7080oec Newbie
    Currently Being Moderated
    Not sure if you were responding to the reusable function DUMP_CSV or another part of the thread here. In case you were asking me....

    The inbound parameter of QUERY is the query you are submitting. Just submit your query string there. Its that simple. Remember to properly capture your quotes since you are passing a string and need to retain those quotes in the query operation.

    We utilize the DUMP_CSV in daily production rountines for several interfaces. Never have had a problem yet. I shouldn't have said that ... now I did it LOL. But seriously it works pretty well. Give it a try.
  • 11. Re: Create a CSV file
    534350 Newbie
    Currently Being Moderated
    I am running a following script to spool the output via toad but the output is generating extra spaces after the end of each row that's causing an error on my file submission. Is there any workaround on this when run through toad.
    ************************************************************************************
    spool spedstaff.csv
    set colsep ,
    set termout off
    set feedback off
    set pagesize 0
    set linesize 80
    select 'SEA SPECIAL EDUCATION STAFF,1,test.csv,4,2005-2006,'
    from dual
    /
    --Grand Total
    --***********
    select '18,01,,,TEACHSPED,,,,,Y,,' ||
    ltrim(to_char(round(nvl(sum(staff),0))))
    from spedstaff
    /
  • 12. Re: Create a CSV file
    341161 Newbie
    Currently Being Moderated
    Add "set trimspool on" to the start of your script.
  • 13. Re: Create a CSV file
    563108 Newbie
    Currently Being Moderated
    Hi,
    If my query is too long, i mean if the no of charac exceed 250, then the variable would not hold the string. what should i do in that case????

    Regards
    Shruti
  • 14. Re: Create a CSV file
    RadhakrishnaSarma Journeyer
    Currently Being Moderated
    Can you elaborate on what you exactly mean? It would be better to open a new thread and ask your question with details.

    Cheers
    Sarma.
1 2 Previous Next