When there are invalid characters (< chr(31) or > chr(127) in the result set, the export to excel will result in a not usable excel file.
I know this is more or less an Excel problem. But the exact same query result can be exported to excel with no problem using a very old oracle tool called "GOLDEN" by Benthic software.
I know I can also try to find the offending database field and fix the data albeit time consuming. But I cannot stop this from happening as users who input data into the database always find ways to insert invalid characters.
Any solution for this?
Two possible workarounds:
- If you are exporting to XLSX file, it might work exporting to XLS instead.
- Try using the "Excel Instance" option in Export Dataset.
I will try to reproduce this and fix.
- I am using xlsx.
- If I choose Excel instance, it won't let me create an output file. file browse option is grayed out.
I have never heard of the File Browse option being greyed out before. Does it always do that or just when this kind of data is present? I just tested Excel Instance with the following SQL and it worked for me. I was able to reproduce the problem with Excel File.
select chr(30) odd_data
from dual;
only happens if I choose Excel Instance as you suggested. The output box is all grayed out except for the check box for Allow empty file.
Oh, you mean in Toad. yes, you have to save the file in Excel if you choose that option.
The Excel Instance option in Toad sends the data to directly to the Excel application, not through a file.