Microsoft Excel has become the perfect solution for creating reports from databases as a result of following features:
- easy-to-use intuitive interface
- data visualization (graphics and charts)
Ancient formats of MS Excel had restriction on max rows count 65535 and this situation prevented from using it as front-end for giant databases. Nevertheless, from Excel 2007 this kind of limit was increased approximately 1048576 rows that enables you to transform medium size database tables into MS Excel worksheets.
Therefore, how do you export Oracle data into MS Excel format? You will find multiple methods to this task, either manual or automated, here are couple of them.
- Work with Microsoft Data Access components.
- set up new workbook in Microsoft Excel
- select “Data” > “External Data” > “From Other Resources” > “Data Connection Wizard” menu item
- in the appeared dialog window select “Microsoft Data Access – OLE DB Provider for Oracle” option
- push “Next” button
- enter server, username and password
- Transfer through comma separate values (CSV) file. It is very straightforward to generate csv file with necessary Oracle data using SPOOL directive:
set pagesize 10000
set feedback off
set heading off
set echo off
select to_char(empno) || ‘,’ || rtrim(ename) || ‘,’ || rtrim(job) || ‘,’ || to_char(hiredate,’dd-mon-yyyy’) from emp;
set echo on
set heading on
set feedback on
After working with these statements from SQL*Plus, it is going to set up a employee.csv file with ’empno’, ‘ename’, ‘job’, ‘hiredate’ columns. The resulting csv file can be simply imported into MS Excel spreadsheet.
- Special software tools.
Just like any procedure depending on human factor, each method in the list above can cause errors or unpredicted data within the conversion result. So, if a person has numerous tables to transform, it’s rational to work with dedicated tools for converting Oracle data into Microsoft Excel format.
An example of such tool is Oracle-to-Excel provided by Intelligent Converters, a software company that develops solutions for database migration and synchronization since 2001. The product works with all versions of Windows and Linux/Unix Oracle servers and supports the following output formats: MS Excel 2016/2013/2010/2007/2000 (MS Excel 2007+ .xlsx format is supported directly, no middleware is necessary). Oracle-to-Excel supports command line and can store conversion settings into profile.