Formatting of SQL*Plus to CSV output format issues

Question

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 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
from abc_temp;

The report is getting generated perfectly in .csv format but I am facing an issue with two columns: credit_amount and debit_amount.

When I open the .csv file in Excel and select few cells it should display the sum, count and average of all the selected cells as per the in-built Excel feature. That is not working for these two columns; only the count is displayed

Only count is coming, average and total should also come

It seems like Excel is treating these as strings instead of numbers. How can I fix this behaviour?

Alex Poole · Accepted Answer · 2016-11-10 16:39:57Z

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 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
from abc_temp;

edited Nov 10, 2016 at 16:39

answered Nov 10, 2016 at 16:33

Alex Poole

192k11 gold badges198 silver badges349 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

piyush sachdeva Over a year ago

Thanks a lot Alex, it works like charm :D , i never realized setting tab off makes such a huge difference. I was struggling for so many days , googled almost every site. but here you are answered like a hero :) big thanks to you.

Collectives™ on Stack Overflow

Formatting of SQL*Plus to CSV output format issues

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related