Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2004
    Posts
    93

    Unanswered: spooling csv files

    I am doing the following:
    set echo off
    set pagesize 0
    set linesize 200
    set newpage 0
    set verify Off
    set feedback Off
    set trimspool on
    clear columns
    clear breaks
    clear computes



    spool c:\mirgration\categories.csv

    select Hierarchy_Item.Description||', '|| Hierarchy_Item.Hierarchy_Item_Id
    from hierarchy_item
    where hierarchy_item.hierarchy_id=3



    Spool off

    but when I go to my c;drive to check the CSV file , the following appears and not the actual data;
    this appears in the EXCEL?CSV file as opposed to all the data..where does all the data go then and how can I get it into files/csv files?
    SQL>
    SQL> select Hierarchy_Item.Description||' '|| Hierarchy_Item.Hierarchy_Item_Id
    2 from hierarchy_item
    3 where hierarchy_item.hierarchy_id=3
    4
    SQL> Spool off

    Cheers
    Cheers
    Etravels

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    To make that SQL statement run, you need to either terminate it with a semi-colon (, or follow it with a slash (/).

  3. #3
    Join Date
    Feb 2004
    Posts
    93
    yes, I have done that and I can see the data , (sorry I forgot to include it on the thread) but I still can't see the data in the csv file..
    I am now going to do the following:

    CONNECT sys/io@io AS SYSDBA
    CREATE OR REPLACE DIRECTORY EXTRACT_DIR AS 'c:\mirgration\extract';
    GRANT READ, WRITE ON DIRECTORY EXTRACT_DIR TO cc_152_rpc;
    CREATE OR REPLACE PROCEDURE stations_CSV AS
    CURSOR c_data IS
    SELECT a.productcode as station_name, a.productinfoa as committee,
    a.productinfob as Train_operating_company
    FROM product_x a;
    v_file UTL_FILE.FILE_TYPE;
    BEGIN
    v_file := UTL_FILE.FOPEN(location => 'EXTRACT_DIR',
    filename => 'stations_csv.txt',
    open_mode => 'w',
    max_linesize => 32767);
    FOR cur_rec IN c_data LOOP
    UTL_FILE.PUT_LINE(v_file,
    cur_rec.productcode || ',' ||
    cur_rec.productinfoa || ',' ||
    cur_rec.productinfob || ',' ||);

    END LOOP;
    UTL_FILE.FCLOSE(v_file);

    EXCEPTION
    WHEN OTHERS THEN
    UTL_FILE.FCLOSE(v_file);
    END;
    /
    Cheers
    Etravels

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    I suggest you paste the exact code you ran and the exact result. Your example script and output consistently show you not terminating the SQL (hence the lonely "4" in the output, where SQL Plus was expecting your input to continue). If you terminate properly, it does work:
    Code:
    set echo off
    set pagesize 0
    set linesize 200
    set newpage 0
    set verify Off
    set feedback Off
    set trimspool on
    clear columns
    clear breaks
    clear computes
    
    spool c:\tony\emps.csv
    
    select emp.ename||', '|| emp.empno
    from emp
    /
    
    Spool off
    Output file:
    Code:
    SMITH, 7369
    ALLEN, 7499
    WARD, 7521
    JONES, 7566
    MARTIN, 7654
    BLAKE, 7698
    CLARK, 7782
    SCOTT, 7788
    TURNER, 7844
    ADAMS, 7876
    JAMES, 7900
    FORD, 7902
    MILLER, 7934

  5. #5
    Join Date
    Feb 2004
    Posts
    93

    Red face and if I need to rename the colums how can I do that?

    Thanks for that

    My only concern is if I rename the columns it doesn’t work the same.

    spool c:\mirgration\stations.csv

    SELECT productcode || ','|| as Station_name,
    productinfoa || ','|| as Commitee, productinfob||',|| as Train_operating_company
    FROM product_x
    /


    Spool off

    It comes out all spaced out and not in the right format;!!
    Cheers
    Etravels

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    No, it can't work the same because now your query is returning 3 columns where it previously returned only 1. It would be make more sense to do this:
    Code:
    SELECT productcode || ','|| productinfoa || ',' || productinfob as "Station_name,Committee,Train_operating_company"
    FROM product_x
    /
    However, since you never see the column name in the output (thanks to pagesize 0) this is fairly pointless, isn't it?

  7. #7
    Join Date
    Jun 2003
    Location
    Ottawa
    Posts
    105

    why???

    I'm assuming from your other post that you are using these .csv files to migrate your data from Oracle to SQL Server.

    I would abondon using csv files all together if I were you.

    In SQL Server, create DTS packages.

    Look in SQL Server Enterprise Manager Help (F1) under "DTS Designer" ...How to copy data from....(simply change your source to an oracle connection object)

    just a suggestion.... ;-)

Posting Permissions

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