10 Replies Latest reply: Jan 6, 2006 5:33 AM by bsc7080oec RSS

    Create a CSV file

    455054
      Is it possible to save the query result as a CSV?
        • 1. Re: Create a CSV file
          Warren Tolentino
          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
            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
              http://asktom.oracle.com/~tkyte/flat/index.html
              • 4. Re: Create a CSV file
                Warren Tolentino
                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
                  I will try it out and let you know the out come. thanks for response
                  • 6. Re: Create a CSV file
                    JensPetersen
                    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
                      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
                        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
                          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
                            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.