1 2 Previous Next 19 Replies Latest reply: Nov 5, 2012 11:42 AM by bencol Go to original post RSS
      • 15. Re: Saving SQL+ output to a text file
        AlbertoFaenza
        Hi,

        why don't you consider creating and HTML file which can be read easily with Excel.

        i.e.:
        SET SERVEROUTPUT ON SIZE UNLIMITED
        SET LIN 500
        
        SET DEFINE OFF
        SET UNDERLINE OFF
        SET TAB OFF
        SET FEEDBACK OFF
        SET VERIFY OFF
        SET TERMOUT OFF
        SET PAGES 9999
         
        SET MARKUP HTML ON TABLE "cellspacing=0 border=1" ENTMAP OFF
        
        spool myexcel.xls
        
        /* Replace the query below as you like */
        SELECT * FROM EMP;
        
        spool off
        
        EXIT
        The format is HTML but once you load in Excel you can easily save as the format you like.

        Regards.
        Al
        • 16. Re: Saving SQL+ output to a text file
          MrGibbage
          @Alberto,
          THANKS!! I do think this will work for me.

          One question: Some of my column headings are getting truncated. I can't find any rhyme or reason as to knowing why some get truncated and some don't. It seems that it is limited to columns containing text (CHAR) data, but I am not sure. It seems that the heading is getting truncated to the length of the longest piece of data in that column, but I have at least one column where that did not happen, so I am not really sure.

          In any case, I think I can live with this, but I'd rather see if I can fix it now.

          Skip
          • 17. Re: Saving SQL+ output to a text file
            AlbertoFaenza
            MrGibbage wrote:
            @Alberto,
            THANKS!! I do think this will work for me.

            One question: Some of my column headings are getting truncated. I can't find any rhyme or reason as to knowing why some get truncated and some don't. It seems that it is limited to columns containing text (CHAR) data, but I am not sure. It seems that the heading is getting truncated to the length of the longest piece of data in that column, but I have at least one column where that did not happen, so I am not really sure.

            In any case, I think I can live with this, but I'd rather see if I can fix it now.

            Skip
            Hi,

            apparently for VARCHAR2 output and DATE output is taking the maximum size of the data and truncating the header. Numbers seems to be ok.

            In this case you can format the column.

            Here below an example where I have defined the format for VARCHAR2 and DATE columns:
            SET SERVEROUTPUT ON SIZE UNLIMITED
            SET LIN 500
            
            SET DEFINE OFF
            SET UNDERLINE OFF
            SET TAB OFF
            SET FEEDBACK OFF
            SET VERIFY OFF
            SET TERMOUT OFF
            SET PAGES 9999
             
            SET MARKUP HTML ON TABLE "border=1" ENTMAP OFF
            
            COL "Heading very long for ename"    FORMAT A30
            COL "Heading very long for job"      FORMAT A30
            COL "Heading very long for hiredate" FORMAT A30
            
            
            spool myexcel.xls
            
            /* Replace the query below as you like */
            select empno     AS "Heading very long for empno"
                 , ename     AS "Heading very long for ename"  
                 , job       AS "Heading very long for job"  
                 , mgr       AS "Heading very long for mgr"    
                 , hiredate  AS "Heading very long for hiredate"
                 , sal       AS "Heading very long for sal"     
                 , comm      AS "Heading very long for comm"    
                 , deptno    AS "Heading very long for deptno"  
              from emp;
            
            spool off
            
            EXIT
            Consider the following limitation though:
            SET PAGES 9999
            sets the pagesize to 9999 lines.
            After that number of lines headers will be repeated.
            This number can be increased up to 50000, which is the maximum.
            If your query is extracting more than 50000 lines than you have better to disable this parameter without having header.
            This is done by setting
            SET PAGES 0
            There is also another solution in case you want to generate an HTML (also readable from Excel) file with more than 50000 lines and having headers.
            You can set pagesize to 0 and make a UNION ALL including headers and data. In this case you have to use TO_CHAR in all of your columns
            SET PAGES 0
            ...
            /* Replace the query below as you like */
            SELECT 'Heading very long for empno'
                 , 'Heading very long for ename'  
                 , 'Heading very long for job'  
                 , 'Heading very long for mgr'    
                 , 'Heading very long for hiredate'
                 , 'Heading very long for sal'     
                 , 'Heading very long for comm'    
                 , 'Heading very long for deptno'  
              from DUAL
            UNION ALL
            SELECT TO_CHAR(empno)                 AS "Heading very long for empno"
                 , ename                          AS "Heading very long for ename"  
                 , job                            AS "Heading very long for job"  
                 , TO_CHAR(mgr)                   AS "Heading very long for mgr"    
                 , TO_CHAR(hiredate,'DD-MM-YYYY') AS "Heading very long for hiredate"
                 , TO_CHAR(sal)                   AS "Heading very long for sal"     
                 , TO_CHAR(comm)                  AS "Heading very long for comm"    
                 , TO_CHAR(deptno)                AS "Heading very long for deptno"  
              FROM emp;
            Alternatively you can run 2 select statements:
            SET PAGES 0
            ...
            /* Replace the query below as you like */
            SELECT 'Heading very long for empno'
                 , 'Heading very long for ename'  
                 , 'Heading very long for job'  
                 , 'Heading very long for mgr'    
                 , 'Heading very long for hiredate'
                 , 'Heading very long for sal'     
                 , 'Heading very long for comm'    
                 , 'Heading very long for deptno'  
              from DUAL;
            
            select empno     AS "Heading very long for empno"
                 , ename     AS "Heading very long for ename"  
                 , job       AS "Heading very long for job"  
                 , mgr       AS "Heading very long for mgr"    
                 , hiredate  AS "Heading very long for hiredate"
                 , sal       AS "Heading very long for sal"     
                 , comm      AS "Heading very long for comm"    
                 , deptno    AS "Heading very long for deptno"  
              from emp;
            This last one has the inconvenience of creating 2 paragraphs between HTML tables so when you display it in Excel after the header you will have a second line which will be empty.

            You can fix this problem by removing {noformat}<{noformat}p{noformat}>{noformat} from your output file.

            Regards.
            Al

            Edited by: Alberto Faenza on Nov 2, 2012 9:05 AM

            Edited by: Alberto Faenza on Nov 2, 2012 10:42 AM
            • 18. Re: Saving SQL+ output to a text file
              Frank Kulash
              Hi,
              MrGibbage wrote:
              @Frank,
              This is close, and I might be able to make it work. I am struggling with the fact that the headings don't get delimited. When I import into Excel, I have to manually rework all of the headings. Do you have any suggestions for dealing with the headings?
              Don't use The SQL*Plus column headings at all. Say:
              SET  PAGESIZE  0
              before you start spooling to turn them off. This will also make sure there are no page breaks and additional headings in the middle of the output, no matter how long it is.

              Use the SQL*Plus PROMPT commnand after you start spooling, and right before the query, to output headers
              So your script will contain
              SET  PAGESIZE  0
              ...
              SPOOL  my_output.csv
              
              PROMPT  EName|Job...
              
              SELECT  ename
              ...
              • 19. Re: Saving SQL+ output to a text file
                bencol
                colsep?
                  1  with t (col1,col2,col3)
                  2  as (select 1 ,'ABC',sysdate from dual
                  3  union all select 2,'DEF',sysdate+1 from dual
                  4  )
                  5* select * from t
                
                      COL1 COL2                 COL3
                 --------- -------------------- -------------------
                         1 ABC                  05/11/2012 17:35:18
                         2 DEF                  06/11/2012 17:35:18
                
                set colsep |
                set und off
                
                /
                
                     COL1|COL2                |COL3
                        1|ABC                 |05/11/2012 17:35:2
                        2|DEF                 |06/11/2012 17:35:2
                1 2 Previous Next