Exporting Numeric Data into excel is converting to Text

Toad for Oracle
1

Hi,

i am new to TOAD. I am facing an issue. I am exporting the data for query into excel which includes some numeric value but when I check in excel it is converted to text. I checked the column type and they are VarChar. Is there any way to store them as Number in excel while exporting?

Thanks,

2

Hi,

Make SQL based export. Example (simple one but enough to understand):
10:04:05 SQL>select to_number (‘1.1’) from dual;

TO_NUMBER(‘1.1’)

         1.1

Elapsed: 00:00:00.03

In a case of custom decimal point …error like:
10:04:19 SQL>select to_number (‘1,1’) from dual;
select to_number (‘1,1’) from dual
*
ERROR at line 1:
ORA-01722: invalid number

Elapsed: 00:00:00.06

Use this trick:
10:04:22 SQL>alter session set NLS_NUMERIC_CHARACTERS = ‘,.’;

Session altered.

Elapsed: 00:00:00.01
10:05:14 SQL>select to_number (‘1,1’) from dual;

TO_NUMBER(‘1,1’)

         1,1

Elapsed: 00:00:00.03
10:05:16 SQL>

In that way Toad is smart enough to export properly types…

If this doesn’t help, then your problem is connected with your local settings in Windows (and Excel is following it).
For that do not know the cure.

Brg

Damir

3

Toad exports with the datatype in the query. so if you were exporting the following select

select 1.23,‘4.56’ from dual;

The first column would be saved as numeric with the value of 1.23, the second column would store the string ‘4.56’ and would be formatted as a text string. Look at your select and determine the datatype of the column.

4

Are you running a query in Toad with F5 and then exporting the grid created from that? F5 runs as script, so we show data as text, like SQL*Plus), and then we process it after that.

Run your query with F9 instead and datatypes will be retained.

5

I am running as F9. The issue here is with column type Varchar, any Varchar type column if has numeric value while exporting using ‘Export Dataset’ into excel, it is exporting as Text instead of number. I have couple of columns which has Type as ‘Varchar(2)’ and they always store number and it is not possible to change now as the system is old and this configuration not sure how will it impact any other place.

6

Be sure “Use only “General” cell format” is not checked in the data export window

From: jainamit78 bounce-jainamit78@toadworld.com

Sent: Wednesday, July 04, 2018 2:16 PM

To: toadoracle@toadworld.com

Subject: [Toad for Oracle - Discussion Forum] Exporting Numeric Data into excel is converting to Text

Exporting Numeric Data into excel is converting to Text

Thread created by jainamit78

Hi,

i am new to TOAD. I am facing an issue. I am exporting the data for query into excel which includes some numeric value but when I check in excel it is converted to text. I checked the column type and they are VarChar. Is there any way to store them as Number in excel while exporting?

Thanks,

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or
Unsubscribe from Toad for Oracle Forum notifications altogether.

Toad for Oracle - Discussion Forum

Flag
this post as spam/abuse.

7

Oh, well if your table has numeric data in varchar columns and you want it to be a numeric datatype, then you can do that in your query:

select to_number(varchar_column_name) as varchar_column_name,
other_columns
from your_table

8

Thanks Jdorlon.. it worked.

9

If you are exporting to an excel instance there is nothing you can do other than what the others suggested (changing the query to convert to varchar2)

if you export as delimited text and then open excel (not use the delimited file to open excel) and then once in excel you insert the file you can change the data mappings have have excel load the numeric column as a string.