Exporting Data from Oracle Applications

Purpose

This document describes how to export information from Oracle Applications into a PC application of your choice. For data that is displayed in Oracle after using a query function (e.g., Query Find), Oracle's Export feature may be used. For data that is displayed as a TEXT file in a second Web browser after a report request is submitted and completed, the Windows function "select/copy/paste" may be used.

Oracle Export is easily done when using MS Internet Explorer. Minimal setup is needed to use the Export functionality with Netscape Navigator.

Export Feature

The Oracle Export feature allows you to export information from any multi-row block, such as journal lines or query results, to a tab-delimited file that can be viewed using a spreadsheet, such as Microsoft Excel or Lotus 1–2–3. Export uses the records and format that has been queried in the window. Therefore, data to be exported can be controlled by using the query functions of the particular module being used. Refer to the work instructions Run a Query. Additionally, if you are exporting from a window with the Folder function, you can reorder columns or remove unwanted columns before you export.


Exporting Large Numbers of Records

Export automatically selects all records currently retrieved, as well as the remaining records associated with the current query. If the records retrieved exceed 100, you will be prompted to make a decision. The following table describes the choices available when very large numbers of records are retrieved:

Decision Button

Description

Stop

Limits the records available to be exported to those already selected by the export process. You will then be prompted to either cancel the export, or export only those records already selected. The number of records selected is included in the decision window.

Continue to End

Allows the export process to select all records retrieved in the form. When all records retrieved have been selected, the export process will automatically export the records.

Note: We recommend that you do not use the export feature for very large numbers of records, such as numbers that exceed 1000, because this is time consuming, and uses significant machine and network resources.

Continue

Will select the next 100 records. When all records retrieved have been selected, the export process will automatically export the records. Otherwise, you will continue to be prompted until all records are selected or you choose Stop or Continue to End.

Setup Needed on PC to Open Exported File

The following table describes the steps needed to set up your PC to open the file directly in an application. Oracle recommends using Excel as this is the application they support.

If you DO NOT want to automatically open your file in an application, do not follow these instructions. Instead, continue to use the Save option to save the file and open it in the application of your choice.

Step

Action

1

Open Windows Explorer (Right click the Start button and click Explore)

2

On the menu click Tools and Folder Options

3

Click the File Types tab and click the New button

4

To open the file in Excel, in the File Extension field add the letters “tsv” (without the quotes) and click the Advanced button. Choose Microsoft Excel Worksheet (to open in the web browser) OR Microsoft Excel Application (to open Excel outside of the web browser).

Note: It is important to pick Microsoft Excel Worksheet or Microsoft Excel Application instead of Microsoft Excel so that your file opens correctly.

You may also pick another application (e.g., Lotus Notes, Lotus 1-2-3, etc.) and it might work. However, Oracle does not support these applications.

5

Click OK two times when prompted in the Folder Options window. Your PC is now set up, and when you follow the export steps below you should have the option to click Open and have your file automatically open in your application of choice.

Windows 7 - Setup Needed on PC to Open Exported File - Associating .tsv files to excel

The following table describes the steps needed to set up your PC to open the file directly in an application. Oracle recommends using Excel as this is the application they support.

If you DO NOT want to automatically open your file in an application, do not follow these instructions. Instead, continue to use the Save option to save the file and open it in the application of your choice.

Step

Action

1

Double click the file you are exporting. You will get a dialog box titled Windows can't open this file.

2

Click the Select a program from a list of installed programs option.

3

If the program is not in that list, click the Browse Button. Navigate to the target programs executable file (program name.EXE) and select it. Click the Open Button.

4

Place a check mark in the Always use the selected program to open this kind of file option. Optionally, you can also add a description of this new file type.

5

Click OK.

6

That file type will now appear in the list of file types.

NOTE: The program that you select must support the format for the file type you are adding.

Export a Query from Internet Explorer

If you plan to export data from Oracle, we suggest you run Oracle Applications from Internet Explorer. The following table describes the steps to take to export a query:

Step

Action

1

Open your spreadsheet application and sign in to Oracle using Internet Explorer.

2

Query the records you want to export from Oracle.

Note: To reduce the time required to export your records, reduce the number of records returned by using explicit query criteria.

3

Ensure that your cursor is in the multi-row block that contains the records to be exported.

Note: If you are exporting information from a window with the Folder tool, you can use the folder tools to reorder columns, select which columns to display, and customize column prompts. These customizations will be maintained when you export the data.

4

Select Action >> Export... to go to the File Download window.

Note: If Export is grayed out, the feature is not available for this window and these records.

5

Select Open or Save.

6

If you saved the file you can now find and open it with the application of your choice (usually Notepad, Wordpad or Excel).

7

If you set up your PC to automatically open the file in an application, once you click Open the file will open in the registered application.

Troubleshooting: If this procedure does not work in Internet Explorer, ensure that the text/tab-separated-values file type is not registered in MS Windows. To check this, in Windows Explorer, choose View >> Options, click the File Types tab, and scroll the list until you see the text/tab-separated-values type. If it is listed, remove it.

Set Up Netscape for Export

In order to use the Netscape Navigator browser with Oracle's Export function, the browser preferences must be edited to recognize the Oracle file that is created after a query. The following table describes the steps to take to set up a new "helper application" in Netscape:

Note: You must know where your spreadsheet application resides to complete this set up.

Step

Action

1

Open Netscape Navigator.

2

Select Edit >> Preferences....

3

In the Preferences window, select Navigator >> Applications in the Category list.

4

Select New Type... to go to the New Type window.

5

Enter the these values into the displayed fields:

Field

Value

Description of type

tab separated values

File extension

tsv

MIME Type

text/tab-separated-values

Application to use

Note: Select Browse to find the location of the spreadsheet application you use. When you open the executable file (e.g., excel.exe), the path will display in the New Type window.

6

Ensure that the box is checked before "Ask me before opening downloaded files of this type." Click OK to go back to the Preferences (Applications) window.

7

Click OK to close the Preferences window.

Export a Query from Netscape Navigator

If you run Oracle Applications from Netscape Navigator, you will need to export the queries as files and then open the files in your spreadsheet. The advantage of doing this is that you don't need to have your spreadsheet application open while running Oracle applications. The following table describes the steps to take to export a query:

Note: Before attempting to export, ensure that Netscape is set up to handle tab-separated .tsv files. Refer to the section Set Up Netscape for Export.

Step

Action

1

Query the records you want to export from Oracle.

Note: To reduce the time required to export your records, reduce the number of records returned by using explicit query criteria.

2

Ensure that your cursor is in the multi-row block that contains the records to be exported.

Note: If you are exporting information from a window with the Folder tool, you can use the folder tools to reorder columns, select which columns to display, and customize column prompts. These customizations will be maintained when you export the data.

3

Select Action >> Export... to go to warning window (e.g., Warning... or Unknown File Type).

Note: If Export is grayed out, the feature is not available for this window and these records.

4

Select "Save it to disk" and click OK to go to the Save as... window.

5

Rename the fndvfile.tsv file and then select a place to store and save the file.

6

Close the second Navigator browser window that opened.

7

Open the spreadsheet application, ensure that cell A1 is chosen, and open the saved file into the spreadsheet.

Note: To open the file, select File >> Open to go to the Open window. To find the file in the directory in which it was saved, change the Files of type: field to All Files (*.*). Click Open to go to the Text Import Wizard window. Click Finish. Some column widths may need to be increased in order for the data to be fully displayed. Select File >> Save As to name the file and save it with the spreadsheet's extension.

Export a TEXT Report Request

A variety of Oracle standard reports as well as Research Foundation custom reports can be run from each business area in Oracle. A person's sign-on responsibility determines which reports are accessible. For more information, refer to the Reports/Queries resource area in EPSS and the Run a Report work instruction in most business areas.

A report may be saved as .txt file and then opened in a word processor or spreadsheet application. Also, selected areas of the displayed report may be copied and pasted into an application, such as a spreadsheet. The following table describes the steps to take to select, copy, and paste report data from Oracle into a spreadsheet:

Note: You must know where your Notepad application resides to complete this procedure.

Step

Action

1

Open the spreadsheet application.

2

Before running the report, ensure that the TEXT version has been selected from the list of Reports. Submit the request.

3

After the report has completed normally, view the output.

4

Either mark the area of the report you wish to copy, or select Edit >> Select All to mark the entire report.

5

Select Edit >> Copy.

6

Open the Notepad tool available in Windows.

7

Select Edit >> Paste. Select Edit >> Select All. Select Edit >> Copy.

8

Switch to the spreadsheet application. Select Edit >> Paste.

Note: A significant amount of reformatting may need to be done in order for the data to be fully displayed. Select File >> Save As to name the file and save it with the spreadsheet's extension. Close the Notepad application without saving.

Change History

 

 

Feedback
Was this document clear and easy to follow? Please send your feedback to webfeedback@rfsuny.org.

Copyright © 2011 The Research Foundation of State University of New York