Exporting data from Oracle to Excel with ApEx – and vice versa.
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.
Then, press the data load/unload button, press Data unload and finally Unload to Text.
A wizard starts:
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.
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.