Export data from Oracle SQL Developer to Excel .xlsx

Question

I have a small project where data from Oracle SQL Developer needs to be exported to Excel (using commands rather than tools in SLQ Developer), then create a graph. Using "spool" I can export to csv fine (but cant make a graph in csv) but when I try to export to xlsx it corrupts the whole excel sheet saying

"Excel cannot open the file "ExcelFile.xlsx" because the file format or file extention 
       is not valid. Verify that the file has not been corrupted and that the 
       file extension mathces the format of the file."

Here is the code I used in SQL Developer.

spool FileLocation\ExcelFile.xlsm
SELECT * FROM Table;
spool off;

Is there any way I can stop the data from becoming corrupted or is there another way to export data to a .xlsx file?

Why "using commands rather than SQL Developer tools"? The requirement should tell you what needs to be done, not HOW to do it. Is this some sort of homework? If it is, then say so. If there is some other reason, explain it first, so that we won't offer solutions that will not work. — user5683823
– user5683823, Commented Jun 23, 2017 at 15:59
Also, if you want to export to xlsx, why are you spooling to a file with the extension xlsm? That is a different file extension, for Excel files with macros. Perhaps that's what Excel is complaining about. — user5683823
– user5683823, Commented Jun 23, 2017 at 16:02
@mathguy quick overview, its not homework, just a small project for a friend. needs to make a program (i'm using java as its the only language i really know) which takes data from a table and manipulates it then saves in another table then export to produce a graph. so using commands rather than tools is so it stay automatic.The way i have programmed the java is so the java program is linked to SLQDeveloper and it inputs the SQL commands. I have tried xlsx and xlsm (thanks for pointing that out) but both still give the error — IAmRapson
– IAmRapson, Commented Jun 23, 2017 at 16:24
The issue, as I see it, is that spooling creates a CSV file, not an Excel file of any kind. You then still need to open Excel and IMPORT the CSV file; CSV is not an EXCEL file type. — user5683823
– user5683823, Commented Jun 23, 2017 at 16:34

thatjeffsmith · Accepted Answer · 2017-06-23 22:06:56Z

5

Nooooooo.

set sqlformat csv
spool c:\file.sql
select * from table;
spool off;

then open the file in excel.

OR

Run your query interactively.

Right click on the grid, Export > XLSX. Open the file.

Spool only writes the query output to the file, it doesn't look at the file extension and figure out HOW to write the output at that point.

So you either have to code it yourself via the query, or use one of the format outputs we support

SET SQLFORMAT
  CSV
  JSON
  DELIMITED
  XML
  HTML
  INSERT
  LOADER

Use 'help set sqlformat' for help.

answered Jun 23, 2017 at 22:06

thatjeffsmith

22.7k6 gold badges44 silver badges140 bronze badges

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

1 Comment

Morez Over a year ago

If column contains CLOB column then CSV may break. Excel will work properly

kpazik · Accepted Answer · 2017-06-23 15:27:27Z

3

Hi sql developer from what I know for exporting is using sqlplus(code is same) so perhabs there are other ways but this one should be good enough

I would try changing first line to look like this:

spool ExcelFile.xls

Probably you also need to turn on

SET MARKUP HTML ON

http://www.orahow.com/2015/09/spool-sqlplus-output-to-excel-format.html

Anyway there is workaround - you can just generate .CSV file and then open it in excel and save as .xlsx file

answered Jun 23, 2017 at 15:27

kpazik

1952 silver badges13 bronze badges

1 Comment

thatjeffsmith Over a year ago

"sql developer from what I know for exporting is using sqlplus(code is same)" - no it's not the same at all

Nishika Gangrade · Accepted Answer · 2020-06-29 15:11:36Z

0

I was also facing the same problem then applied below code and it exported successfully..

import xlsxwriter
from xlsxwriter import Workbook
import cx_Oracle
import datetime
from datetime import date

dsn_tns = cx_Oracle.makedsn('HOST', 'PORTNO', sid='BGRDB') 
    
db = cx_Oracle.connect(user=r'username', password='password', dsn=dsn_tns)
cursor = db.cursor()

workbook = xlsxwriter.Workbook('C:/Path/outfile.xlsx')
sheet = workbook.add_worksheet()


cursor.execute("select * from TABLENAME")
for r, row in enumerate(cursor.fetchall()):
         for c, col in enumerate(row):
                sheet.write(r, c, col)

workbook.close()
cursor.close()

answered Jun 29, 2020 at 15:11

Nishika Gangrade

32 bronze badges

Collectives™ on Stack Overflow

Export data from Oracle SQL Developer to Excel .xlsx

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related