Export Oracle data into Microsoft Excel

Microsoft Excelis probably the best solution for reports and data visualization due to the following reasons:

• easy-to-use intuitive interface
• formulas is powerful feature to manipulate the data
• wide range of graphics and charts

Early versions of Microsoft Excel had limitation on rows count (it must not exceed 65535) that prevented from using the application as front-end for large databases. However, starting from Excel 2007 this limit has been increased up to 1048576 rows. Now it is possible to convert medium size database tables into MS Excel worksheets splitting data into multiple sheets when it is necessary.

This article explains how to export Oracle data into Microsoft Excel format. Two of the most frequently used solutions for to this task are listed below.

1. Microsoft Data Access components. This approach required the following steps to be done:

• create new workbook in Microsoft Excel
• open menu item Data>External Data>From Other Resources> Data Connection Wizard
• in the dialog window select option “Microsoft Data Access – OLE DB Provider for Oracle” and push “Next” button
• enter Oracle server, username and password

1. Export via comma separate values (CSV) file. Oracle data may be exported into csv filesusing SPOOL command:

set pagesize 10000

set feedback off

set heading off

set echo off

spool d:\employee.csv

select to_char(empno)||’,’||rtrim(ename)||’,’||rtrim(job)||’,’||to_char(hiredate,’dd-mon-yyyy’) from emp;

spool off

set echo on

set heading on

set feedback on

After running these statements from SQL*Plus or any other Oracle client application, the specified columns (’empno’, ‘ename’, ‘job’, ‘hiredate’) of table ‘emp’ will be imported‘employee.csv’ file. Then the csv file can be opened into MS Excel and imported into spreadsheet.

1. Special tools. As any process relying on human factor, both methods listed above may cause errors or unexpected data in the conversion result. When the database contains a lot of tables, it is rational to use dedicated tools for converting Oracle data into Microsoft Excel format.

One of such tools is Oracle to Excel converter developed by Intelligent Converters, software company working in database migration field since 2001.The program provides high performance and has all necessary featured to make the export process as easy as possible:

• All versions of Oracle servers running on Windows and Linux/Unix platforms are supported
• Output format can be one the following MS Excel 2016/2013/2010/2007/2000/5.0
• MS Excel 2007/2010 .xlsx format is supported directly, without intermediate software components
• In order to bypass the format limitation, Oracle data can be splitted into multiple MS Excel files
• Option to filter data for export using SELECT-queries
• Command line support allows to automate Oracle to MS Excel export tasks
• Conversion settings can be stored into profile to simplify next export runs