Formatting Of Sql*plus To Csv Output Format Issues

I am trying to export the result of my SQL query to a .csv file using column formatting as below. SET head OFF SET feedback OFF SET pagesize 500 SET linesize 2000; SET colsep , set

Solution 1:

The values are being treated as string because, by default, SQL*Plus uses tabs to space out results, and the presence of a tab makes Excel assume it is in fact a string. If it only has spaces separating the values then the value would still be treated as a number.

You can change the behaviour by adding:

set tab off

Although personally I tend to construct each row with concatenation, which eliminates any whitespace, and to format dates (and sometimes numbers) explicitly::

set head offset pages 0

prompt Posting date,Company code,Physical account,Debit amount,Credit amount

select to_char(posting_date, 'YYYY-MM-DD')
  ||','|| company_code
  ||','|| physical_account
  ||','|| debit_amount
  ||','|| credit_amountfrom abc_temp;