Unwanted Decimal Format When Exporting to Excel

[Doug Volz]

Not sure why, but when I open up a new Report Window, run a SQL query (such as select * from mtl_parameters) and export to Excel (xlsx), the numeric columns are fine, no unwanted decimal points. But when I open up a new SQL Window, do the same query and export to Excel, the numeric columns all have two decimal formats (such as 12.00). Is there a formatting setting I am unaware of? Can't find it. When I view the output in the SQL window results there is no added two-digit decimal point. Happens with any query I export with the Report Window.

 

[Marco Kalter]

You can go to Tools > Preferences > SQL Windows and change the "Number layout" option:

• Left aligned - Values are displayed left aligned, without any formatting.
• Right aligned - Values are displayed right aligned, without any formatting.
• Formatted - Values are displayed right aligned, with a format that corresponds to the precision and scale of the field.
• Formatted with thousand separator - Similar to the previous option, but will also include a thousand separator (e.g. 1,277.65)

 

[Doug Volz]

Hi Marco, for the SQL Window preferences I had the setting as "Right aligned" and was getting the two-decimal issue as described. After reading your response I changed it to "Formatted" but still get a two-decimal format for numeric fields when exporting into Excel. Any ideas? Would this be an issue with my Excel setup? Am on WIN10, 64 bit but using the 32 bit version of PL/SQL Developer, version 1106. Many thanks, Doug

 

[Marco Kalter]

In Excel you will always get the scale and precision information of the underlying field. One option to avoid this is to apply a dummy "+0" calculation your query. For example:

select sal, sal + 0 as sal_no_format from emp

The first column will be formatted in Excel, the second will be not be formatted.

 

[Doug Volz]

Hi Marco,
I think it is something else ... all of the numeric fields export with two decimal points, with zero values for the decimal precision. For example, in an Oracle Vision Environment, when running "SELECT * from MTL_PARAMETERS", the results for the first few columns are:
ORGANIZATION_ID LAST_UPDATE_DATE LAST_UPDATED_BY
204.00 6/15/2016 3:49:52 PM 1068.00
207.00 8/17/2015 7:31:18 PM 1318.00
There should be no decimals after the ORGANIZATION_ID or the LAST_UPDATED_BY or for any other numeric field.
Best regards,
Doug

 

[Marco Kalter]

Can you let me know the table creation DDL for the MTL_PARAMETERS table? Can you also let me know your exact PL/SQL Developer version?

 

[Doug Volz]

Hi Marco,
Don't think it matters which table ... mtl_parameters is a standard table from the Oracle EBS Applications, Release 12.x. But I reproduced this issue with the following custom table creation statement and following insert statement. (I'm thinking I have somehow screwed up a laptop setting but can't see it ... using the latest version 11.0.6.1776 01.103095). Many thanks, Doug

-- | Description:
-- | Script to create the A/P Accrual comparison table.

CREATE TABLE XXX_AP_ACCRUAL_RECONCILE
( LEDGER_ID NUMBER NOT NULL
, OPERATING_UNIT_ID NUMBER NOT NULL
, TRANSACTION_ORGANIZATION_ID NUMBER NOT NULL
, ITEM_MASTER_ORGANIZATION_ID NUMBER NOT NULL
, PERIOD_NAME VARCHAR2(15) NOT NULL
, ACCRUAL_ACCOUNT_ID NUMBER NOT NULL
, TRANSACTION_SOURCE_CODE VARCHAR2(25) NOT NULL
, ACCRUAL_CODE VARCHAR2(80)
, ACCOUNTING_LINE_TYPE VARCHAR2(30)
, DESTINATION_TYPE_CODE VARCHAR2(25)
, INVOICE_NUM VARCHAR2(50)
, RECEIPT_NUM VARCHAR2(30)
, INV_TRANSACTION_ID NUMBER
, TRANSFER_TRANSACTION_ID NUMBER
, RCV_TRANSACTION_ID NUMBER
, WIP_TRANSACTION_ID NUMBER
, AE_HEADER_ID NUMBER
, AE_LINE_NUM NUMBER
, ACCOUNTING_EVENT_ID NUMBER
, ENTITY_ID NUMBER
, SUB_LEDGER_ID NUMBER
, EVENT_TYPE_CODE VARCHAR2(30)
, EVENT_STATUS_CODE VARCHAR2(1)
, PROCESS_STATUS_CODE VARCHAR2(1)
, TRANSFERRED_TO_GL VARCHAR2(1)
, WRITE_OFF_ID NUMBER
, WRITE_OFF_FLAG VARCHAR2(1)
, INV_TRANSACTION_TYPE_ID NUMBER
, WIP_TRANSACTION_TYPE_ID NUMBER
, RCV_TRANSACTION_TYPE VARCHAR2(25)
, RETRO_PRICE_ADJUSTMENT VARCHAR2(1)
, TRANSACTION_DATE DATE NOT NULL
, INVENTORY_ITEM_ID NUMBER
, PRIMARY_UNIT_OF_MEASURE VARCHAR2(25)
, PO_UNIT_OF_MEASURE VARCHAR2(25)
, TRANSACTION_QUANTITY NUMBER
, NET_PO_LINE_QUANTITY NUMBER
, NET_PO_LINE_AMOUNT NUMBER
, EARLIEST_AGING_DATE DATE
, LATEST_AGING_DATE DATE
, PO_HEADER_ID NUMBER
, PO_NUM VARCHAR2(20)
, PO_LINE_NUM NUMBER
, PO_LINE_ID NUMBER
, PO_LINE_LOCATION_ID NUMBER
, PO_RELEASE_ID NUMBER
, PO_DISTRIBUTION_ID NUMBER
, VENDOR_ID NUMBER
, VENDOR_NAME VARCHAR2(240)
, TRANSACTION_UNIT_PRICE NUMBER
, INVOICE_ID NUMBER
, INVOICE_LINE_NUM NUMBER
, INVOICE_DISTRIBUTION_ID NUMBER
, AVG_RECEIPT_PRICE NUMBER
, WO_REASON_ID NUMBER
, WO_COMMENTS VARCHAR2(240)
, ENTERED_TRANSACTION_AMOUNT NUMBER
, ACCOUNTED_TRANSACTION_AMOUNT NUMBER
, CURRENCY_CODE VARCHAR2(15)
, CURRENCY_CONVERSION_TYPE VARCHAR2(30)
, CURRENCY_CONVERSION_RATE NUMBER
, CURRENCY_CONVERSION_DATE DATE
, LAST_UPDATE_DATE DATE
, LAST_UPDATED_BY NUMBER
, LAST_UPDATE_LOGIN NUMBER
, CREATION_DATE DATE
, CREATED_BY NUMBER
, REQUEST_ID NUMBER
, PROGRAM_APPLICATION_ID NUMBER
, PROGRAM_ID NUMBER
, PROGRAM_UPDATE_DATE DATE
, INSERT_REASON VARCHAR2(50)
, INSERT_SCRIPT VARCHAR2(30)
, UPDATED_FLAG VARCHAR2(1)
)

LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
NOMONITORING;

/* +================================================================+
-- | Douglas Volz Consulting, |
-- +================================================================+
-- |
-- | Original Author: Douglas Volz (doug@volzconsulting.com)
-- |
-- | Program Name: XXX_INSERT_RCV_ACCRUAL_TXNS.sql
-- |
-- | Parameters:
-- | P_TRANSACTION_DATE_FROM: < mandatory, DD-MON-YYYY >
-- | P_TRANSACTION_DATE_TO: < mandatory, DD-MON-YYYY >
-- | Description:
-- | Script to insert transaction entries into the A/P Accrual
-- | comparison table, XXX_AP_ACCRUAL_RECONCILE, for receiving
-- | transactions.
-- |
-- | Version Modified on Modified by Description
-- | ======= =========== ============== ===========================
-- | 1.0 22 Aug 2016 Douglas Volz Initial Coding
+==================================================================+*/

-- ===================================================================
-- Procedures for INSERT_RCV_ACCRUAL_TXNS
-- ===================================================================
-- INSERT_RCV_ACCRUAL_TXNS-1: Insert RCV entries with Create Accounting
-- ===================================================================

INSERT INTO XXX_AP_ACCRUAL_RECONCILE (
LEDGER_ID,
OPERATING_UNIT_ID,
TRANSACTION_ORGANIZATION_ID,
ITEM_MASTER_ORGANIZATION_ID,
-- Revision for version 1.1
PERIOD_NAME,
ACCRUAL_ACCOUNT_ID,
TRANSACTION_SOURCE_CODE,
ACCRUAL_CODE,
ACCOUNTING_LINE_TYPE,
DESTINATION_TYPE_CODE,
INVOICE_NUM,
RECEIPT_NUM,
INV_TRANSACTION_ID,
TRANSFER_TRANSACTION_ID,
RCV_TRANSACTION_ID,
WIP_TRANSACTION_ID,
AE_HEADER_ID,
AE_LINE_NUM,
ACCOUNTING_EVENT_ID,
-- Revision for version 1.6
ENTITY_ID,
SUB_LEDGER_ID,
EVENT_TYPE_CODE,
EVENT_STATUS_CODE,
PROCESS_STATUS_CODE,
-- Revision for version 1.5
TRANSFERRED_TO_GL,
WRITE_OFF_ID,
WRITE_OFF_FLAG,
INV_TRANSACTION_TYPE_ID,
WIP_TRANSACTION_TYPE_ID,
RCV_TRANSACTION_TYPE,
RETRO_PRICE_ADJUSTMENT,
TRANSACTION_DATE,
INVENTORY_ITEM_ID,
PRIMARY_UNIT_OF_MEASURE,
PO_UNIT_OF_MEASURE,
TRANSACTION_QUANTITY,
NET_PO_LINE_QUANTITY,
NET_PO_LINE_AMOUNT,
EARLIEST_AGING_DATE,
LATEST_AGING_DATE,
PO_HEADER_ID,
PO_NUM,
PO_LINE_NUM,
PO_LINE_ID,
PO_LINE_LOCATION_ID,
PO_RELEASE_ID,
PO_DISTRIBUTION_ID,
VENDOR_ID,
VENDOR_NAME,
TRANSACTION_UNIT_PRICE,
INVOICE_ID,
INVOICE_LINE_NUM,
INVOICE_DISTRIBUTION_ID,
AVG_RECEIPT_PRICE,
WO_REASON_ID,
WO_COMMENTS,
ENTERED_TRANSACTION_AMOUNT,
ACCOUNTED_TRANSACTION_AMOUNT,
CURRENCY_CODE,
CURRENCY_CONVERSION_TYPE,
CURRENCY_CONVERSION_RATE,
CURRENCY_CONVERSION_DATE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATION_DATE,
CREATED_BY,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
INSERT_REASON,
INSERT_SCRIPT,
UPDATED_FLAG )
-- ==================================================================
-- INSERT_RCV_ACCRUAL_TXNS-1
-- Insert receiving accrual entries into the XXX_AP_ACCRUAL_RECONCILE
-- ==================================================================
SELECT gl.ledger_id, -- LEDGER_ID
caa.operating_unit_id, -- OPERATING_UNIT_ID
rt.organization_id, -- TRANSACTION_ORGANIZATION_ID
mp.master_organization_id, -- ITEM_MASTER_ORGANIZATION_ID
-- Revision for version 1.1
rrsl.period_name, -- PERIOD_NAME
xal.code_combination_id, -- ACCRUAL_ACCOUNT_ID
'RCV', -- TRANSACTION_SOURCE_CODE
rt.transaction_type, -- ACCRUAL_CODE
rrsl.ACCOUNTING_LINE_TYPE, -- ACCOUNTING_LINE_TYPE
pod.DESTINATION_TYPE_CODE, -- DESTINATION_TYPE_CODE
NULL, -- INVOICE_NUM
rsh.RECEIPT_NUM, -- RECEIPT_NUM
NULL, -- INV_TRANSACTION_ID
NULL, -- TRANSFER_TRANSACTION_ID
rt.transaction_id, -- RCV_TRANSACTION_ID
NULL, -- WIP_TRANSACTION_ID
xal.AE_HEADER_ID, -- AE_HEADER_ID
xal.AE_LINE_NUM, -- AE_LINE_NUM
rrsl.ACCOUNTING_EVENT_ID, -- ACCOUNTING_EVENT_ID
-- Revision for version 1.6
xte.ENTITY_ID, -- ENTITY_ID
rrsl.RCV_SUB_LEDGER_ID, -- SUB_LEDGER_ID
xe.EVENT_TYPE_CODE, -- EVENT_TYPE_CODE
xe.EVENT_STATUS_CODE, -- EVENT_STATUS_CODE
xe.PROCESS_STATUS_CODE, -- PROCESS_STATUS_CODE
-- Revision for version 1.5
decode(xah.GL_TRANSFER_DATE,
NULL,'N','Y'), -- TRANSFERRED_TO_GL
NULL, -- WRITE_OFF_ID
NULL, -- WRITE_OFF_FLAG,
NULL, -- INV_TRANSACTION_TYPE_ID
NULL, -- WIP_TRANSACTION_TYPE_ID
rt.transaction_type, -- RCV_TRANSACTION_TYPE
NULL, -- RETRO_PRICE_ADJUSTMENT
rrsl.ACCOUNTING_DATE, -- TRANSACTION_DATE
pol.item_id, -- INVENTORY_ITEM_ID
NULL, -- PRIMARY_UNIT_OF_MEASURE
pol.UNIT_MEAS_LOOKUP_CODE, -- PO_UNIT_OF_MEASURE
rrsl.SOURCE_DOC_QUANTITY, -- TRANSACTION_QUANTITY
NULL, -- NET_PO_LINE_QUANTITY
NULL, -- NET_PO_LINE_AMOUNT
NULL, -- EARLIEST_AGING_DATE
NULL, -- LATEST_AGING_DATE
rt.PO_HEADER_ID, -- PO_HEADER_ID
poh.segment1, -- PO_NUM
pol.LINE_NUM, -- PO_LINE_NUM
pol.PO_LINE_ID, -- PO_LINE_ID
pll.LINE_LOCATION_ID, -- PO_LINE_LOCATION_ID
pr.PO_RELEASE_ID, -- PO_RELEASE_ID
pod.PO_DISTRIBUTION_ID, -- PO_DISTRIBUTION_ID
poh.VENDOR_ID, -- VENDOR_ID
pv.VENDOR_NAME, -- VENDOR_NAME
nvl(pll.price_override, pol.unit_price), -- TRANSACTION_UNIT_PRICE
NULL, -- INVOICE_ID
NULL, -- INVOICE_LINE_NUM
NULL, -- INVOICE_DISTRIBUTION_ID
NULL, -- AVG_RECEIPT_PRICE
NULL, -- WO_REASON_ID
NULL, -- WO_COMMENTS
(nvl(rrsl.entered_dr,0) - nvl(rrsl.entered_cr,0)), -- ENTERED_TRANSACTION_AMOUNT,
(nvl(rrsl.accounted_dr,0) - nvl(rrsl.accounted_cr,0)), -- ACCOUNTED_TRANSACTION_AMOUNT,
rrsl.CURRENCY_CODE, -- CURRENCY_CODE
rrsl.USER_CURRENCY_CONVERSION_TYPE, -- CURRENCY_CONVERSION_RATE
rrsl.CURRENCY_CONVERSION_RATE, -- CURRENCY_CONVERSION_RATE
rrsl.CURRENCY_CONVERSION_DATE, -- CURRENCY_CONVERSION_DATE
sysdate, -- LAST_UPDATE_DATE
-1, -- LAST_UPDATED_BY
-1, -- LAST_UPDATE_LOGIN
sysdate, -- CREATION_DATE
-1, -- CREATED_BY
-1, -- REQUEST_ID
-1, -- PROGRAM_APPLICATION_ID
-1, -- PROGRAM_ID
sysdate, -- PROGRAM_UPDATE_DATE
'Receiving Txns-with SLA', -- INSERT_REASON
'INSERT_RCV_ACCRUAL_TXNS-1', -- INSERT_SCRIPT
NULL -- UPDATED_FLAG
FROM apps.rcv_transactions rt,
apps.rcv_receiving_sub_ledger rrsl,
apps.rcv_accounting_events rae,
apps.rcv_shipment_headers rsh,
apps.rcv_shipment_lines rsl,
apps.cst_accrual_accounts caa,
apps.po_headers_all poh,
apps.po_lines_all pol,
apps.po_releases_all pr,
apps.po_line_locations_all pll,
apps.po_distributions_all pod,
apps.po_vendors pv,
apps.mtl_parameters mp,
apps.HR_ORGANIZATION_INFORMATION hoi,
apps.hr_all_organization_units haou, -- inv_organization_id
apps.hr_all_organization_units haou2, -- operating unit
apps.gl_ledgers gl,
-- Synonym issue in Vision, had to use table owner
xla.xla_transaction_entities xte,
apps.xla_events xe,
apps.xla_distribution_links xdl,
apps.xla_ae_headers xah,
apps.xla_ae_lines xal,
apps.xla_ledger_options xlo
-- ========================================================
-- Transaction and organization joins
-- ========================================================
WHERE rrsl.rcv_transaction_id = rt.transaction_id
AND rrsl.accounting_event_id = rae.accounting_event_id
AND mp.organization_id = rt.organization_id
-- ========================================================
-- Transaction date joins
-- Use RCV_ACCOUNTING_EVENTS as transaction_date has an index
-- ========================================================
AND rae.transaction_date >= to_date('&P_TRX_FROM_DD_MON_YYYY','DD/MON/YYYY HH24:MI:SS') -- P_TRX_DATE_FROM
AND rae.transaction_date < to_date('&P_TRX_TO_DD_MON_YYYY','DD/MON/YYYY HH24:MI:SS')+1 -- P_TRX_DATE_TO
-- ========================================================
-- Purchase Order Joins
-- ========================================================
AND rt.po_header_id = poh.po_header_id
AND rt.po_line_id = pol.po_line_id
AND poh.po_header_id = pol.po_header_id
AND pol.po_line_id = pll.po_line_id
AND pll.line_location_id = pod.line_location_id
-- Revision for version 1.2
-- With split distributions RCV_TRANSACTIONS may be NULL
-- Have to use RCV_RECEIVING_SUB_LEDGER or RCV_ACCOUNTING_EVENTS
-- Used RCV_ACCOUNTING_EVENTS as this is a NUMBER column
-- AND pod.po_distribution_id = rt.po_distribution_id
AND pod.po_distribution_id = rae.po_distribution_id
AND rsl.shipment_header_id = rsh.shipment_header_id
AND rt.SHIPMENT_LINE_ID = rsl.SHIPMENT_LINE_ID
AND pod.po_release_id = pr.po_release_id (+)
AND poh.vendor_id = pv.vendor_id
-- ========================================================
-- using the base tables to avoid the performance issues
-- with org_organization_definitions AND hr_operating_units
-- ========================================================
AND hoi.org_information_context = 'Accounting Information'
AND hoi.organization_id = rt.organization_id
AND hoi.organization_id = haou.organization_id -- this gets the organization name
AND haou2.organization_id = to_number(hoi.org_information3) -- this gets the operating unit id
AND gl.ledger_id = to_number(hoi.org_information1) -- get the ledger_id
-- ========================================================
-- SLA table joins to get the exact account numbers
-- ========================================================
AND xte.entity_code = 'RCV_ACCOUNTING_EVENTS'
AND xte.application_id = 707
AND xe.application_id = xte.application_id
AND xe.event_id = xdl.event_id
AND xah.entity_id = xte.entity_id
AND xah.ledger_id = xte.ledger_id
AND xah.application_id = xal.application_id
AND xah.application_id = 707
AND xah.event_id = xe.event_id
AND xah.ae_header_id = xal.ae_header_id
AND xal.application_id = xte.application_id
AND xal.ledger_id = xah.ledger_id
AND xal.AE_HEADER_ID = xdl.AE_HEADER_ID
AND xal.AE_LINE_NUM = xdl.AE_LINE_NUM
AND xdl.application_id = xte.application_id
AND xdl.source_distribution_type = 'RCV_RECEIVING_SUB_LEDGER'
AND xdl.source_distribution_id_num_1 = rrsl.rcv_sub_ledger_id
AND xte.ledger_id = xlo.ledger_id
AND xte.application_id = xlo.application_id
AND xlo.capture_event_flag = 'Y'
-- ========================================================
-- Only pick up "Final" Create Accounting entries
-- ========================================================
AND xah.ACCOUNTING_ENTRY_STATUS_CODE = 'F' -- Final
-- ========================================================
-- Only get valid accrual account entries
-- ========================================================
AND xal.code_combination_id = caa.accrual_account_id
AND caa.operating_unit_id = haou2.organization_id
;

 

[Marco Kalter]

I cannot reproduce this. Number fields that have no specific scale result in Excel cells with a "General" format. Can you verify that this is the case for you as well?

 

[Doug Volz]

Hi Marco, I must have a local issue on my laptop ... what I'll do is install PL/SQL Developer on another laptop and see if I can reproduce this error. Thanks, Doug

 

[mtone]

Hi,

I think I am also getting the same problem with all integer fields I encounter. At its simplest expression:

select 2016 as y,
cast(2016 as integer) as y2
from dual

This query shows "2016" in the SQL window results, but 2016.00 (cell format: Number with 2 decimals) for both fields after exporting to Excel. Changing right-aligned vs formatted in SQL window preferences, or the xls vs xlsx export format doesn't seem to affect the excel export.

Running 32-bit v11.0.6.1776 on 32-bit Windows 7.

Thanks.

 

[Marco Kalter]

That's odd. I get 2 cells with format "General", which results in no decimal places. Can you let me know your Excel version?

 

[mtone]

Thanks for the reply. I'm using Excel 2007 SP3 (32-bit).

Other things I can think of:
- I also see the decimals if opening the file in google docs.
- My Windows regional settings is English (Canada)
- NLS session language is American
- Tried changing Windows Format to Oracle Format in NLS options
- Oracle 11g 11.2.0.1.0

Edit/Update:
- Right-click + Copy to Excel opens a tmp001.xls where the cell format is General (with no decimals).
- Right-click + Export to CSV/HTML file, or to clipboard in CSV format, also doesn't show decimals

 

[oracletask]

Hello Marco,

i have the same problem like the others. Can you please take a closer look on the issue..

running software:
- windows 7 64 bit
- Office 2010 32 bit
- plsqldeveloper 32-bit v11.0.6.1776

im really lookin' forward to hear from you.

Kind Regards
oracletask

 

[Marco Kalter]

We'll change the format from general to number. That will hopefully fix it.

 

[Leonid]

Came here in 2018 looking for solution of the same problem.
Found out it's not a bug in general: Excel uses different format based on value datatype.

select 2018 as implicit_number,
cast(2018 as integer) as explicit_integer,
cast(2018 as number(9)) as number9,
cast(2018 as number(10)) as numer10
from dual

IMPLICIT_NUMBER EXPLICIT_INTEGER NUMBER9 NUMER10
2018.00 2018.00 2018 2018.00

It's clear that number(10) cannot be stored as integer ( 2^32 has 9 decimal digits), so it's saved as float, hence decimal point. The only question is why integer formatted like this. The reason is connected with Oracle representation of INTEGER - it's just a synonym for NUMBER(38,0) so stored as float too.