11 Replies Latest reply: May 2, 2013 12:16 PM by 883189 RSS

    spool output to .csv file - having issues with data display

    883189
      Hi,

      Need to deliver the output of a select query which has around 80000 records to a .csv file. A procedure is written for the select query and the procedure is being called in the spool script. But few of the columns have comma(,) in the values. For Example, there is a personal_name column in the select query which says the name as " James, Ed". Then output is displayed in different columns. Hence the data is being shifted to the right for the remaining columns.
      Could some one help fix this issue. I mainly used a procedure as the select query is about three pages and hence want the script to look clear.

      Script is,

      set AUTOPRINT ON ;
      set heading ON;
      set TRIMSPOOL ON ;
      set colsep ',' ;
      set linesize 1000 ;
      set PAGESIZE 80000 ;
      variable main_cursor refcursor;
      set escape /
      spool C:\documents\querys\personal_info.csv
      EXEC proc_personal_info(:main_cursor);
      spool off;
        • 1. Re: spool output to .csv file - having issues with data display
          rp0428
          >
          Need to deliver the output of a select query which has around 80000 records to a .csv file. A procedure is written for the select query and the procedure is being called in the spool script. But few of the columns have comma(,) in the values. For Example, there is a personal_name column in the select query which says the name as " James, Ed". Then output is displayed in different columns. Hence the data is being shifted to the right for the remaining columns.
          >
          I don't see any data 'shifted to the right'.

          If you are using a COMMA as the field delimiter and columns can have embedded COMMAs in them you at least need to enclose those fields in double quotes or the file will be useless anyway.

          See the FAQ thread for how to read/write Excel/csv files.
          SQL and PL/SQL FAQ
          • 2. Re: spool output to .csv file - having issues with data display
            sb92075
            880186 wrote:
            Hi,

            Need to deliver the output of a select query which has around 80000 records to a .csv file. A procedure is written for the select query and the procedure is being called in the spool script. But few of the columns have comma(,) in the values. For Example, there is a personal_name column in the select query which says the name as " James, Ed". Then output is displayed in different columns. Hence the data is being shifted to the right for the remaining columns.
            Could some one help fix this issue. I mainly used a procedure as the select query is about three pages and hence want the script to look clear.

            Script is,

            set AUTOPRINT ON ;
            set heading ON;
            set TRIMSPOOL ON ;
            set colsep ',' ;
            set linesize 1000 ;
            set PAGESIZE 80000 ;
            variable main_cursor refcursor;
            set escape /
            spool C:\documents\querys\personal_info.csv
            EXEC proc_personal_info(:main_cursor);
            spool off;
            I would use REPLACE() function
            http://docs.oracle.com/cd/E11882_01/server.112/e26088/functions153.htm#SQLRF00697
            to transform the comma to a space character
            • 3. Re: spool output to .csv file - having issues with data display
              883189
              For example, my proc has a select query as follows:

              select personal_name, address from personal_info

              Then the output will be appeared in four columns instead of two columns.
              If the address column has a value, Mercy Island, WA, 30040.
              Then this appears in three separate columns instead of one column.

              Tried to use " in the select query for such columns. But, it didnt work out.

              Was totally confused how to handle this issue ....
              • 4. Re: spool output to .csv file - having issues with data display
                883189
                The data is not constant to use replace function
                It will keep on changing
                We dont want to go back, check the data and search for each and every column with commas.

                REPLACE can be used for unwanted characters with good data. It is not at all helpful.

                Can some1 assist me in this ? I have been doing research since a day for this issue.
                • 5. Re: spool output to .csv file - having issues with data display
                  Marwim
                  Tried to use " in the select query for such columns. But, it didnt work out.
                  That's not really helpful. What did you try and why didn't it work?

                  Regards
                  Marcus
                  • 6. Re: spool output to .csv file - having issues with data display
                    883189
                    I tried using this query below in the proc by using quotes.
                    select "personal_name", "address" from personal_info

                    But its not working. Can anyone solve this issue ?
                    • 7. Re: spool output to .csv file - having issues with data display
                      sb92075
                      880186 wrote:
                      I tried using this query below in the proc by using quotes.
                      select "personal_name", "address" from personal_info

                      But its not working. Can anyone solve this issue ?
                      my car is not working.
                      tell me how to make my car go.

                      alternatively, you could use a different character, like pipe character("|") to delimit the columns
                      • 9. Re: spool output to .csv file - having issues with data display
                        AlbertoFaenza
                        Hi,
                        set PAGESIZE 80000 ;
                        is not valid and it will print header as default every 14 rows.
                        You can avoid printing the header in this way:
                        set AUTOPRINT ON ;
                        set heading ON;
                        set TRIMSPOOL ON ;
                        set colsep ',' ;
                        set linesize 1000 ;
                        set PAGESIZE 0 ;
                        set escape /
                        set feedback off
                        spool c:\temp\empspool.csv
                          SELECT '"'||ename||'"', '"'||job||'"' 
                          FROM emp;
                        spool off
                        The output will look like this in this case
                        "SMITH"     ,"CLERK"
                        "ALLEN"     ,"SALESMAN"
                        "WARD"      ,"SALESMAN"
                        "JONES"     ,"MANAGER"
                        "MARTIN"    ,"SALESMAN"
                        "BLAKE"     ,"MANAGER"
                        "CLARK"     ,"MANAGER"
                        "SCOTT"     ,"ANALYST"
                        "KING"      ,"PRESIDENT"
                        "TURNER"    ,"SALESMAN"
                        "ADAMS"     ,"CLERK"
                        "JAMES"     ,"CLERK"
                        "FORD"      ,"ANALYST"
                        "MILLER"    ,"CLERK"
                        You can also consider creating a unique column by concatenating the columns in this way:
                        spool c:\temp\empspool.csv
                          SELECT '"'||ename||'","'||job||'"' 
                        In this case the output will look without spaces between columns:
                        "SMITH","CLERK"
                        "ALLEN","SALESMAN"
                        "WARD","SALESMAN"
                        "JONES","MANAGER"
                        "MARTIN","SALESMAN"
                        "BLAKE","MANAGER"
                        "CLARK","MANAGER"
                        "SCOTT","ANALYST"
                        "KING","PRESIDENT"
                        "TURNER","SALESMAN"
                        "ADAMS","CLERK"
                        "JAMES","CLERK"
                        "FORD","ANALYST"
                        "MILLER","CLERK"
                        Regards.
                        Al

                        Edited by: Alberto Faenza on May 2, 2013 5:48 PM
                        • 10. Re: spool output to .csv file - having issues with data display
                          Marwim
                          select "personal_name", "address" from personal_info
                          When you put your column names inside double quotes you tell Oracle to treat them as case sensitive column names. So in this case you tell oracle to select from a column personal_name instead of PERSONAL_NAME.
                          As Alberto has shown in his post you need to concatenate the double quotes between each column value. In your example it would be
                          select '"'||personal_name||'", "'||address||'"'
                          Regards
                          Marcus
                          • 11. Re: spool output to .csv file - having issues with data display
                            883189
                            Alberto,

                            Thanks for your help. Code is working fine now when I used ||'"'
                            But, I am confused about using pagesize 80000.

                            The data will be about 80000 records and hence I used 80000 for page size
                            to avoid printing the header before 80000 records. It is working fine for me.
                            The header only appears once in the data of about 78850 records.

                            Can you explain why it would print header for every 14 rows ?

                            But thank you so much for helping me to solve the issue.