Page 1 of 3 123 LastLast
Results 1 to 15 of 35
  1. #1
    Join Date
    Dec 2008
    Posts
    99

    Unanswered: exporting csv and formatting

    Hello,
    I export csv file from sqlplus spool.In the output I like to format numbers and column size,when I open the file I see numbers (1233+E) and columns overlapped.

    To tackle this, I tried to_char to solve number formatting and column columnname format A20 to adjust column size

    Is there a way to do that?

    Here is what I use

    set head off;
    set feed off;
    set echo off;
    set linesize 300;
    set trimspool on;
    set pagesize 0;
    spool c:\dir\my.csv


    SELECT
    'column1',',',
    'column2',','
    FROM DUAL;

    select column1||','||
    column2
    from mytable
    ;




    spool off
    exit;

    Thanks

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Not quite sure what the question is, as you apparently already know about SQL Plus column formatting and to_char options.

    For numbers, you could do (e.g.):
    Code:
    SQL> column numcol1 format 9999999999999999.999

  3. #3
    Join Date
    Dec 2008
    Posts
    99
    Quote Originally Posted by andrewst
    Not quite sure what the question is, as you apparently already know about SQL Plus column formatting and to_char options.

    For numbers, you could do (e.g.):
    Code:
    SQL> column numcol1 format 9999999999999999.999

    Hi,

    I run that code as .sql file.I run this code as a script on windows server.I tried column formatting however I donot see column is formatted even for numbers or chars.

    Do I miss anything?

    Thanks

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Quote Originally Posted by sunsail
    Do I miss anything?
    Well, I don't see any COLUMN commands or TO_CHAR functions in the script you posted originally, so where did you put them?

  5. #5
    Join Date
    Dec 2008
    Posts
    99
    Quote Originally Posted by andrewst
    Well, I don't see any COLUMN commands or TO_CHAR functions in the script you posted originally, so where did you put them>

    Hello,
    here it is how I use formatting

    set head off;
    set feed off;
    set echo off;
    set linesize 300;
    set trimspool on;
    set pagesize 0;
    spool c:\dir\my.csv
    column column1 format A50;
    column column2 format 99999;

    SELECT
    'column1',',',
    'column2',','
    FROM DUAL;

    select column1||','||
    column2
    from mytable
    ;




    spool off
    exit;


    Kind Regards

  6. #6
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Why don't you try with TO_CHAR function, such as
    Code:
    ... your settings here ...
    
    SELECT column1 ||','|| TO_CHAR(column2, '99999')
    FROM mytable;

  7. #7
    Join Date
    Dec 2008
    Posts
    99
    Quote Originally Posted by Littlefoot
    Why don't you try with TO_CHAR function, such as
    Code:
    ... your settings here ...
    
    SELECT column1 ||','|| TO_CHAR(column2, '99999')
    FROM mytable;

    Hi,
    actually mytable is a view of a huge query.In the sql of view I use "to_char"
    and it didnot work
    I can try your suggestion in the script though.
    How can I adjust column size in the script ?

    If the column formatting works in sqlplus interface why does it now work while script runs?

    Thanks

  8. #8
    Join Date
    Dec 2008
    Posts
    99
    Quote Originally Posted by Littlefoot
    Why don't you try with TO_CHAR function, such as
    Code:
    ... your settings here ...
    
    SELECT column1 ||','|| TO_CHAR(column2, '99999')
    FROM mytable;

    Hi
    I tried below it didnot work.I think I cannot user to_char in script.

    Thanks

    set head off;
    set feed off;
    set echo off;
    set linesize 300;
    set trimspool on;
    set pagesize 0;
    spool c:\dir\my.csv
    column column1 format A50;
    column column2 format 99999;

    SELECT
    'column1',',',
    'column2',','
    FROM DUAL;

    select column1||','|| to_char(column1,'99999')
    column2
    from mytable
    ;




    spool off
    exit;

  9. #9
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Oh, I see - you are concatenating the values. I missed that in the original, sorry. The COLUMN command relates to the names of the result columns, not to the table columns used to get the data. So for example you can do this:
    Code:
    column thetext format a100
    
    select column1||','||column2 as thetext
    from mytable;
    But that of course is formatting the result string, not column1 and column2. You can either do this:
    Code:
    select column1||','||to_char(column2,'99999') as thetext
    from mytable;
    or you can do this:
    Code:
    column column1 format a50
    column column2 format 99999
    set colsep ','
    
    select column1, column2 
    from mytable;

  10. #10
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Quote Originally Posted by sunsail
    I tried below it didnot work.I think I cannot user to_char in script.
    Yes you can. Explain "did not work".

  11. #11
    Join Date
    Dec 2008
    Posts
    99
    Quote Originally Posted by andrewst
    Yes you can. Explain "did not work".
    Hello,

    Save below commands as myscript.sql

    set head off;
    set feed off;
    set echo off;
    set linesize 300;
    set trimspool on;
    set pagesize 0;
    spool c:\dir\my.csv
    column column1 format A50;
    column column2 format 99999;

    SELECT
    'column1',',',
    'column2',','
    FROM DUAL;

    select column1||','|| to_char(column1,'99999')
    column2
    from mytable
    ;



    run this command

    sqlplus -s dbname/password@dbserver @c:\dirpath\myscript.sql

    as myscript1.cmd in the windows

    I couldnot make it work maybe I m missing something

    Kind Regards

  12. #12
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    please explain what you mean by "I couldnot make it work". That could mean:
    - it formatted it in a way you don't like
    - it gave an error message
    - your PC crashed and died
    - ...

  13. #13
    Join Date
    Dec 2008
    Posts
    99
    Quote Originally Posted by andrewst
    please explain what you mean by "I couldnot make it work". That could mean:
    - it formatted it in a way you don't like
    - it gave an error message
    - your PC crashed and died
    - ...
    Hi,

    I should say first of all I am not a native english speaking person.By saying that I meant I got error message while I was running the script.

    Kind Regards

  14. #14
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    And it would extremely help if you finally provide error code (such as ORA-00942, for example); actual copy-paste of your SQL*Plus session would also help.

  15. #15
    Join Date
    Dec 2008
    Posts
    99
    Quote Originally Posted by Littlefoot
    And it would extremely help if you finally provide error code (such as ORA-00942, for example); actual copy-paste of your SQL*Plus session would also help.
    Hello,

    I explained above in my previous messages I donot run sqlplus session I export csv file from .cmd files.It's just scripting.

    Here I use this method
    How Can I unload data to a flat file

    Thanks

Posting Permissions

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