Good afternoon TOAD team,
Please role back the change, numbers and dates should not be quoted in Excel exports.
Larry
Hi Larry.
That code hasn't been touched in quite a while. There is no such change to roll back.
My guess is that you are exporting from a script output grid (F5). In that case, grid data is represented internally as a string, which is why it is sent to Excel as a string. If you run your SQL with F9 that won't happen.
-John
Good morning John,
Yes, I have 13 scripts in the file, I use F5 so they are executed close to each other. I’ve been doing this for years, to create the monthly audit log spreadsheet.
Thank you for the work around. I believe you told me that in the past, too.
Something changed, the export is now ay the top of the list. As a packrat, older TOAD is installed, but the following is a new annoyance, Excel search and replace does not even find the ‘ at the beginning of the cell. In Toad for Oracle Xpert (64-bit) 17.0.353.2906 the numbers are not quoted, see attached. But neither are the numbers in the current TOAD. This is a laptop only issue, which is at home, and I’m in DC today using my desktop. I plan to be working from home on Wednesday… I was able to reproduce the issue on TOAD Beta on my Azure VDT in TOAD 25.2.100.5114. Yellow highlight added for issue cells.
(Attachment Application Summary.xlsx is missing)
error in TOAD Beta
Working on latest TOAD production on my Ddesktop:
HI Larry,
This is Toad 17.0. I ran the SQL with F5, right-clicked in the grid and chose "Export Dataset".
Excel has the values as strings. You can tell by the little green triangle in the corner.
Here are my Export Dataset settings.
I get the same result in 24.2
Please give me exact steps to reproduce the problem, with a "select from dual" SQL so I can reproduce it.
Thanks.
John
Ah. Are you using the "Excel Instance" Export format (as opposed to Excel File)? If so, I believe you need to uncheck "Treat string fields as strings"
Thank you John!
Unchecking that option fixes the issue.
code you asked for:
/* demo for TOADworld.sql
*/
set define off;
set linesize 4096;
SET PAGESIZE 50000;
select 'Normal string output line1' String1, 3.0 NumberField from dual
union
select 'Normal string output line2' String1, 4.0 NumberField from dual;
Larry
Sorry, I closed too soon, when a timestamp field is in the list it is still quoted:
The issue does not happen in the editor, only in the Schema Browser. Change the SQL in the Schema Browser to the following to see and recreate, the checkbox is still not checked but Timestamps are quoted.
/* demo for TOADworld.sql
set define off;
set linesize 4096;
SET PAGESIZE 50000;
*/
select 'Normal string output line1' String1, 3.0 NumberField, TO_TIMESTAMP('1/13/2025 2:55:20.620488 AM', 'fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM') TimeStampFielda from dual
union
select 'Normal string output line2' String1, 4.0 NumberField, TO_TIMESTAMP('1/12/2025 2:55:20.392410 AM', 'fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM') TimeStampFielda from dual;
Larry
From the Schema Browser (or with F9 in the Editor), these fields are sent as strings because Excel doesn't know what to do with timestamps.
If anything, I would say this is a bug when exporting with F5. This is what I see in the Editor after exporting with F5.
Good morning John,
If you change the Excel format to Custom and use this format mm/dd/yyyy_hh:mm:ss.000 AM/PM you get the best Excel has to offer, three decimal places after the period on seconds, but you need to remove the quote. Fair call.
I also notice that TOAD adds 3 extra zeros, Timestamp only supports 6 digits after the period on seconds, TOAD displays 9 digits.
