home Technology Move Oracle data into Microsoft Excel

Move Oracle data into Microsoft Excel

Why Excel Is Still Essential to Data Analytics | Boost Labs

Microsoft Excel is perhaps the most effective solution for creating reports from databases because of the following features:

  • easy-to-use intuitive interface
  • formulas
  • data visualization (graphics and charts)

Previously, formats of MS Excel had limit on max rows count 65535 and this fact restricted it from making use of it a front-end for large databases. Nevertheless, beginning from Excel 2007, this restriction has been increased up to 1048576 rows that permits the conversion of medium size database tables into MS Excel worksheets.

So, in what way can you export Oracle data into MS Excel format? You’ll find multiple techniques to achieve this process, let’s examine few of them.

  1. Use Microsoft Data Access components.
  • create new workbook in Microsoft Excel
  • navigate to Data > External Data > From Other Resources > Data Connection Wizard menu
  • in the appeared dialog select Microsoft Data Access – OLE DB Provider for Oracle option
  • push “Next” button
  • enter server, username and password
  1. Export through comma separate values (CSV) file. 

It is quite easy to generate csv file with necessary Oracle data using SPOOL directive:

set pagesize 10000

set feedback off

set heading off

set echo off

spool d:\employee.csv

select to_char(id)||’,’||rtrim(name)||’,’||rtrim(phone)||’,’||to_char(hire_date,’dd-mon-yyyy’) from emp;

spool off

set echo on

set heading on

set feedback on

As soon as you are done running these statements from SQL*Plus, it will develop a employee.csv file with ‘id’, ‘name’, ‘phone’, ‘hire_date’ columns. The completed csv file can be swiftly imported into MS Excel spreadsheet.

  1. Unique tools.

Just like any procedure depending on human factor, both techniques listed above may possibly cause errors or sudden data loss in the conversion result. So, if anyone possesses lot of tables to transform, it is rational to use reputable tools for transforming Oracle data into Microsoft Excel format.

One of these tool is Oracle to Excel migration utility of Intelligent Converters. The program offers direct support for MS Excel 2007 and higher format (.xlsx), so it will not need ODBC or any kind of middleware. Command line support permits you to use the script and plan the procedure of export Oracle data into Microsoft Excel format.

Other functionality:

  • All versions of Linux/Unix and Windows Oracle servers are supported
  • Available output formats: MS Excel 2016/2013/2010/2007/2000/5.0
  • Data can be exported into multiple MS Excel files as workaround to file size limitation
  • The data to convert can be filtered using SELECT-queries
  • Option to convert selected tables
  • Conversion settings can be stored into profile for next run

If there’s no direct access to Oracle server, Intelligent Converters will also provide a solution to export data from Oracle dump files (generated by exp or expdp tools) into MS Excel format.