While exporting data from Oracle PLSQL to Excel, format issue is coming

Question

I am trying to export data from Oracle PLSQL to Excel. The data type of one of the column is VARCHAR2. The Column conatins value like 00798019859217. But after exporting, the value in excel is something like this 7.9802E+11.

PLease let me know how to resolve this and the reason for this format issue.

Thanks in advance.

What is the relevance of the reference to SQL Server in this question when you have tagged it Oracle and PLSQL? — Ian Carpenter
– Ian Carpenter, Commented Nov 26, 2014 at 11:43

ngrashia · Accepted Answer · 2014-11-26 10:50:46Z

1

Do the following:

Select the column with single quotes, say

SELECT ("'" || COLUMN_NAME) AS COLUMN_NAME, OTHER_COLUMNS FROM MY_TABLE

Output will be like:

'ABC0157976
'00798019859217

Export the output to an excel.In excel "A" column values will be

'ABC0157976
00798019859217   (Single quote will not be visible for number only values)

Select the entire "A" row and clear all single quotes with replace all option. You will get final excel as.
```
ABC0157976
00798019859217 
```

Since it is a text field and non-numeric characters are also expected to be present, the step#3 is required. If it is going to be only numeric characters, then step #3 can be ignored.

answered Nov 26, 2014 at 10:50

ngrashia

9,8945 gold badges45 silver badges58 bronze badges

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

3 Comments

karthika harisankar Over a year ago

Hi thanks for your response. For some rows the column value will be null. In that case single quote (') alone is coming in the excel. How to avoid that..

ngrashia Over a year ago

USE SELECT ("'" || NVL(COLUMN_NAME,'DUMMY_VALUE') AS COLUMN_NAME. After step#3, also clear all cells containing value as "DUMMY_VALUE"

ngrashia Over a year ago

@karthika: Please accept as answer by clicking the grey mark by the side if this answer helped you. Thank you!

Exhausted · Accepted Answer · 2014-11-26 10:30:51Z

0

This is happening due to excel where the number auto casted. As @HamidP said try exporting as csv and check with opening in notepad or text, and check whether the number displaying fully or not. If so then you can open the same in Excel with small options change such

Right click the cell and click format option and make it cell format as text and then save the excel.

answered Nov 26, 2014 at 10:30

Exhausted

1,8932 gold badges23 silver badges33 bronze badges

Collectives™ on Stack Overflow

While exporting data from Oracle PLSQL to Excel, format issue is coming

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related