A day in the life of an Oracle Applications Consultant

Sunday, August 22, 2004

Export data directly to Excel by configuring the MIME Type Profile Option

This post outlines how to export your Oracle Applications data directly to Excel by configuring a profile option.

Typically when choosing to export the results of a query or search in Oracle you will choose the File > Export option on the Oracle Applications menu. The data you desire to be exported will by default be exported as a tsv (tab separated values) file which can easily be read using notepad or wordpad. Should you wish to, this file can also easily be imported into Excel by opening the file from within Microsoft Excel and simply clicking Finish when the text import wizard appears.

On a couple of Oracle Applications systems that I worked on I noticed that choosing the File > Export was exporting the results directly to an Excel spreadsheet embedded within a browser. This can often be a better route to take than exporting into a tsv file and then importing into Excel. Subsequent investigation as to why various systems I worked on behaved differently when executing file export revealed that this behaviour is largely controlled by the MIME Type profile option and the parameters that are specified herein.

At this point I believe it’s appropriate to define what MIME is so that readers can understand exactly what they are doing when configuring this profile option. According to the definition of MIME is as follows: -

Short for Multipurpose Internet Mail Extensions, a specification for formatting non-ASCII messages so that they can be sent over the Internet. Many e-mail clients now support MIME, which enables them to send and receive graphics, audio, and video files via the Internet mail system. In addition, MIME supports messages in character sets other than ASCII. In addition to e-mail applications, Web browsers also support various MIME types. This enables the browser to display or output files that are not in HTML format.

Essentially MIME ensures that there is an association between a non-ASCII file and the application that is needed to read it. In our particular instance, within the MIME Type profile option we need to specify a parameter that will tell our default browser to open the file in Excel, Word or another application you would like to see the results in. By default the MIME type profile option has the following parameter: text/tab-separated-values. Changing this value to application/excel or application/winword will result in any export of query results in Oracle being sent directly to Excel or Word respectively. The value of the second portion of this parameter (in this instance excel or winword) depends on what command you need to execute in Windows to run either of these applications. You can determine this by looking at what command you run to open an XLS (Excel) file or DOC (Word) file in Windows. In Windows Explorer, on the file menu, choose Tools > Folder Options > File Types Tab, select the DOC or XLS extension and then click on Advanced. Following on from this, click on the Open Action and then select Edit…The name of the command to run the application associated with this file type will be specified in the Application: box.

As a final test as to whether you have determined the correct command that needs to be used to run Excel or Word, from the Windows Start button choose Run and then type in the command (in this intance excel or winword) and click OK. Your Word or Excel application should run.