sqlplus - Formatting of SQL*Plus to CSV output format issues -

i trying export result of sql query .csv file using column formatting below.

set head off set feedback off set pagesize 500 set linesize 2000; set colsep , set trimspool on set trimout on set trims on  column c1 heading posting_date format date column c2 heading company_code format a6 column c3 heading physical_account format a8 column c4 heading debit_amount format 99999.99  column c5 heading credit_amount format 99999.99   select  posting_date c1,   company_code c2,   physical_account c3,   debit_amount c4,   edit_amount c5 abc_temp; 

the report getting generated in .csv format facing issue 2 columns: credit_amount , debit_amount.

when open .csv file in excel , select few cells should display sum, count , average of selected cells per in-built excel feature. not working these 2 columns; count displayed

it seems excel treating these strings instead of numbers. how can fix behaviour?

the values being treated string because, default, sql*plus uses tabs space out results, , presence of tab makes excel assume in fact string. if has spaces separating values value still treated number.

you can change behaviour adding:

set tab off 

although tend construct each row concatenation, eliminates whitespace, , format dates (and numbers) explicitly::

set head off set 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_amount abc_temp;