将SQL * Plus格式化为CSV输出格式问题 [英] Formatting of SQL*Plus to CSV output format issues

问题描述

我正尝试使用以下列格式将SQL查询的结果导出到.csv文件.

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;

该报告正以.csv格式完美生成，但是我遇到两列问题:credit_amount和debit_amount.

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

当我在Excel中打开.csv文件并选择几个单元格时，它应该显示所有选定单元格的总和，计数和平均值，这是根据内置Excel功能而定的.这不适用于这两列；只显示计数

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

Excel似乎将它们视为字符串而不是数字.我该如何解决此问题?

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

推荐答案

将这些值视为字符串，因为默认情况下，SQL * Plus使用选项卡来分隔结果，并且选项卡的存在使Excel假定它是实际上是一个字符串.如果仅用空格分隔值，则该值仍将被视为数字.

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.

您可以更改行为通过添加:

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;

这篇关于将SQL * Plus格式化为CSV输出格式问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！