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

3

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.

 

Share.

About Author

3 Comments

  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. Jorrit Nijssen on

    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).

View Mobile Site