Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1

    Unanswered: Exporting true comma-delimited file

    Trying to use SQL Plus to export a comma delimited file. The columns of the resulting file are comma delimited, but are also "fixed width", being padded with unnecessary spaces that are confusing our processing. Even numeric values are being padded to 22 characters.
    Do we need to use different parameters to get a true comma-delimited file? Here is the command we are using now:
    Code:
    sqlplus -s  > "$LOGFILE" <<-EOQ
    $esssbpwd
         set arraysize 50
         set echo off
         set newpage 0
         set wrap off
         set linesize 75
         set space 0
         set colsep ','
         set head off
         set feedback off
         set verify off
         set pagesize 0
         whenever sqlerror exit failure rollback ;
         whenever oserror exit failure rollback ;
    
    --Customer Seen - New and existing
    spool incent_ess_seen.txt
    SELECT   StringA,
             StringB,
             AmountA,
             AmountB
    FROM     DataTable
    /
    
    spool off;
    exit ;
    EOQ
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  2. #2
    Join Date
    May 2006
    Posts
    132
    Are you looking for something like this:

    Code:
    SQL> select empno, ename, job, sal from scott.emp;
    
         EMPNO ENAME      JOB              SAL
    ---------- ---------- --------- ----------
          7369 SMITH      CLERK            800
          7499 ALLEN      SALESMAN        1600
          7521 WARD       SALESMAN        1250
          7566 JONES      MANAGER         2975
          7654 MARTIN     SALESMAN        1250
          7698 BLAKE      MANAGER         2850
          7782 CLARK      MANAGER         2450
          7788 SCOTT      ANALYST         3000
          7839 KING       PRESIDENT       5000
          7844 TURNER     SALESMAN        1500
          7876 ADAMS      CLERK           1100
          7900 JAMES      CLERK            950
          7902 FORD       ANALYST         3000
          7934 MILLER     CLERK           1300
    
    14 rows selected.
    
    SQL> set pages 0 feed off
    SQL> select empno||','||ename||','||job||','||sal from scott.emp;
    7369,SMITH,CLERK,800
    7499,ALLEN,SALESMAN,1600
    7521,WARD,SALESMAN,1250
    7566,JONES,MANAGER,2975
    7654,MARTIN,SALESMAN,1250
    7698,BLAKE,MANAGER,2850
    7782,CLARK,MANAGER,2450
    7788,SCOTT,ANALYST,3000
    7839,KING,PRESIDENT,5000
    7844,TURNER,SALESMAN,1500
    7876,ADAMS,CLERK,1100
    7900,JAMES,CLERK,950
    7902,FORD,ANALYST,3000
    7934,MILLER,CLERK,1300
    Last edited by ebrian; 03-22-07 at 20:22.

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Yes, but surely there is a way to export a standard recordset in simple comma delimited form without having to manually concatenate the string?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by blindman
    Yes, but surely there is a way to export a standard recordset in simple comma delimited form without having to manually concatenate the string?
    As far as I know: not with SQL*Plus
    (but I'd love to be proven wrong!)

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Then what, exactly, does the "Plus" stand for?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Then what, exactly, does the "Plus" stand for?
    It stands for features it can do that ANSI SQL can not do.
    If you are so inclined to try Google, I know that others have posted a PERL script which does what you want.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  7. #7
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    If you are spooling to a spool file then "set trimspool off" will prevent it sticking blank padding at the end of each line.

    Alan

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by anacedent
    If you are so inclined to try Google, I know that others have posted a PERL script which does what you want.
    Good to know, but if it comes to that I'll concat in my sql code rather than toss another technology into the process.

    Quote Originally Posted by AlanP
    If you are spooling to a spool file then "set trimspool off" will prevent it sticking blank padding at the end of each line.
    I'll give this a shot when I am in the office next week, but will this trim only padding at the end of the "line", or will it also trim the padding between columns?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  9. #9
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by blindman
    Good to know, but if it comes to that I'll concat in my sql code rather than toss another technology into the process.
    Actually that's one of the reasons I have written my own SQL tool. I can create any export (CSV, TSV, ...) I like from any DBMS I like and always with the same syntax. And I can also import those files back without having to learn a new tool for each DBMS.

  10. #10
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Try the following function. The function is mostly from the asktom.oracle.com site with some additional error handling. This function will export to a folder on your database server only as it uses utl_file.

    Code:
    FUNCTION dump_csv( p_query     in varchar2,
                       p_separator in varchar2 default ',',
                       p_dir       in varchar2 ,
                       p_filename  in varchar2,
                       p_header    IN VARCHAR2,
                       p_endline   IN VARCHAR2 DEFAULT chr(13))
       -- This routine makes certain assumptions.
       -- 1) There must be a query and it can't be greater then 32K.
       -- 2) The separator must only be one character in length and can't be
       --    a CR, LF, binary 0, or null (easy to change).
       -- 3) If the p_dir parameter is null, the p_filename must contain the
       --    path and filename (/tmp/output.txt)
       -- 4) If the p_header parameter is not null, then insert it into the first
       --    row of the output file. If the p_separator parameter is not a comma,
       --    the comma's in the header string will be replaced with the new
       --    separator. so to add a header use 'NAME,FIRST_NAME,LAST_NAME' and if
       --    the separator is a tab, what is put into the file would be
       --    'NAME<tab>FIRST_NAME<tab>LAST_NAME'
       -- 5) The value of p_endline will be appended to the end of each line of the
       --    output file. It can be used to add a carriage return before the Line Feed
       --    is inserted by the NEW_LINE (unix). If the server is running on a windows
       --    machine, set this to null since the NEW_LINE will save a CR,LF pair anyway.
       --    This can also be used if you needed to put something at the end. For exanple
       --    "'|'||CHR(13)" which would put a vertical bar and CR,LF on each line on
       --    a unix machine.
       --
       -- The following are the returned error codes
       -- -1 The query is empty
       -- -2 The output filename is empty
       -- -3 The separator is invalid.
       -- -4 The filename only contains the path, no filename specified.
       -- -5 The output file can not be opened.
       -- -6 The query could not be parsed. It was illegal.
       --  0 The query returned NO records.
       -- >0 The number of records returned.
    
    return number
    is
        l_output        utl_file.file_type;
        l_theCursor     integer default dbms_sql.open_cursor;
        l_columnValue   varchar2(2000);
        l_status        integer;
        l_colCnt        number default 0;
        l_separator     varchar2(10) default '';
        l_cnt           number default 0;
        l_dir           VARCHAR2(500);
        l_filename      VARCHAR2(32);
        x_pnt           NUMBER(4);
        l_header        VARCHAR2(2000);
    begin
        -- sanity check the input
        IF p_query IS NULL THEN
           RETURN(-1);
        END IF;
        IF p_filename IS NULL THEN
           RETURN(-2);
        END IF;
        -- Do not allow CR, LF,binary 0, or null to be used as a separator. The length of the
        -- separator must be 1 if it exists.
        IF p_separator IS NULL OR
           p_separator IN (chr(13),chr(10),chr(0)) OR
           length(p_separator) > 1 THEN
           RETURN(-3);
        END IF;
        -- If the directory parameter is blank, assume that the directory
        -- is included in the filename.
        IF p_dir IS NOT NULL THEN
           l_dir := p_dir;
           l_filename := p_filename;
        ELSE
           x_pnt := instr(p_filename,'/',-1,1);
           -- If no path is specified or no filename is specified,
           -- the procedure will not work... get out.
           IF x_pnt = 0 OR x_pnt = length(p_filename) THEN
              RETURN(-4);
           END IF;
           l_dir := substr(p_filename,1,x_pnt-1);
           l_filename := substr(p_filename,x_pnt+1);
        END IF;
    
    
        -- Check to see if the file can be opened. If ANY error is
        -- encountered, exit with a count of -1;
        BEGIN
           l_output := utl_file.fopen( l_dir, l_filename, 'w', 32767 );
        EXCEPTION
           WHEN OTHERS THEN
              RETURN(-5);
        END;
    
        -- Check to see if the query can be processed. if ANY error is
        -- encountered, close the output file and exit.
        BEGIN
           dbms_sql.parse(  l_theCursor,  p_query, dbms_sql.native );
        EXCEPTION
           WHEN OTHERS THEN
              utl_file.fclose( l_output );
              RETURN(-6);
        END;
    
        -- If the p_header parameter is not null, then insert the line as
        -- the first line in the output file. This is used if the user wants
        -- to insert column headings. Make sure to use a comma in your header
        -- line and the routine will replace all comma;s with the specified
        -- separator.
        l_header := NULL;
        IF p_header IS NOT NULL THEN
           l_header := p_header;
           IF p_separator <> ',' THEN
              l_header := REPLACE(l_header,',',p_separator);
           END IF;
        END IF;
    
        -- Loop through all the parameters for the select. To support
        -- unknown querys, the assumption is that the query will return
        -- all columns as varchar2 columns where the data is correctly
        -- formatted for inport. A maximum of 255 columns are supported
        -- in the query. Each column can't be greater then 2000
        -- characters in length.
    
    
    
        for i in 1 .. 255 loop
            begin
                dbms_sql.define_column( l_theCursor, i, l_columnValue, 2000 );
                l_colCnt := i;
            exception
                when others then
                    if ( sqlcode = -1007 ) then exit;
                    else
                        raise;
                    end if;
            end;
        end loop;
        -- This define_column insures that at least one column is defined for the
        -- routine.
        dbms_sql.define_column( l_theCursor, 1, l_columnValue, 2000 );
    
        -- Fire the query.
    
        l_status := dbms_sql.execute(l_theCursor);
    
        -- Loop through all the rows returned by the query. Build up the output file
        -- by looping through the defined columns.
    
        loop
            exit when ( dbms_sql.fetch_rows(l_theCursor) <= 0 );
            IF l_cnt = 0 AND l_header IS NOT NULL THEN
               utl_file.put(l_output, l_header);
               utl_file.put(l_output, p_endline);
               UTL_FILE.NEW_LINE (l_output,1);
               l_cnt := 1;
            END IF;
            l_separator := '';
            for i in 1 .. l_colCnt loop
                dbms_sql.column_value( l_theCursor, i, l_columnValue );
                utl_file.put( l_output, l_separator || l_columnValue );
                l_separator := p_separator;
            end loop;
           utl_file.put(l_output, p_endline);
           UTL_FILE.NEW_LINE (l_output,1);
           l_cnt := l_cnt+1;
        end loop;
    
        -- Processing done. close the cursor and output file.
    
        dbms_sql.close_cursor(l_theCursor);
        utl_file.fclose( l_output );
    
        -- Return the number of rows built in the csv file.
    
        return l_cnt;
        -- If Any error occures outside of the errors checked above, then raise
        -- and error and blow out the procedure.
        EXCEPTION
           WHEN OTHERS THEN
              RAISE;
    end dump_csv;
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  11. #11
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Probably a silly suggestion but why can't you use a replace (or similar) command ... Change double spaces into singles..?
    George
    Home | Blog

  12. #12
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Because that would still need to be run through a loop until all the single spaces but one were eliminated. Also, that would affect strings where I might WANT double spaces.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  13. #13
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Pfft, nobody wants double spaces
    George
    Home | Blog

  14. #14
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Dude!

    I already gave you code, albiet fixed width to export.

    You should not use spool for any data of any signifigance..you will blow out the buffer
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  15. #15
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Dude! When did you give me this code? This is you first reply to this thread!
    And what kind of code? Just to concatenate the columns to a single string? That's what I was trying to avoid, but I can handle it if needs be.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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