Exporting data from Oracle to Excel with ApEx – and vice versa.

Patrick Sinke 3
0 0
Read Time:1 Minute, 16 Second

If you’re ever asked to export some data from the database into an Excel file (or vice versa), do check if you have ApEx installed on your server. There are some handy utilities available there, especially for end-user tasks.

One of these utilities is export to file.

To get there, do the following:

Login to  Oracle ApEx and click the Utilities tab or button.

ApEx utilities

Then, press the data load/unload button, press Data unload and finally Unload to Text.

A wizard starts:

ApEx data unload wizard

From here it’s very staight-forward. Select your schema, then the desired table, and next the columns in the selection and a where-clause to restrict the selection. The final step has a small catch. You can provide a separator here. Usually you would use a comma or semi-colon as seperator. The disadvantage is, that you can’t open the file straight away but have to import it. An alternative is to press TAB key in Notepad or another editor and copy-paste this in the separator field.

When you press Unload data you can immediately open the presented file in Excel.

apex unload to excel parameters

The other way around might be handy to convert all those user-managed Excel sheets to decent Oracle tables. You can upload csv or text files or even copy-paste some comma- or tabdelimited data into an input field.

You might have noticed on the way that there is also the possibility to create output in XML format. This is just as straightforward as the excel-method.

 

Happy
Happy
0 %
Sad
Sad
0 %
Excited
Excited
0 %
Sleepy
Sleepy
0 %
Angry
Angry
0 %
Surprise
Surprise
0 %

Average Rating

5 Star
0%
4 Star
0%
3 Star
0%
2 Star
0%
1 Star
0%

3 thoughts on “Exporting data from Oracle to Excel with ApEx – and vice versa.

  1. I have no statistics how this export performs in comparison with other tools. But keeping in mind that Excel only supports 64k rows, I can imagine that the performance differences between various types of export can hardly be noticable. Internally this export probably no more than a select which formats the data in a csv-format, which is opened in Excel.

  2. Does it support non-printable characters? How is the performance?
    I am in doubt it will be faster than fastreader wisdomforce

  3. According to the help for the field separator (the prompt Separator is a link to the help) you can also use backslash followed by t (\t).

Comments are closed.

Next Post

Publications written in 2006

Name EJB 3.0 Persistence – Nieuwe Industriestandaard voor Java/Database communicatie date Wed, 11 Jan 2006 EJB 3.0 Persistence komt eraan: een nieuwe standaard voor Java applicaties om met relationele databases te communiceren. EJB 3.0 Persistence wordt gesteund door alle belangrijke partijen die zich bezig houden met de mapping tussen Java […]
%d bloggers like this: