Practical Methods for exporting ORACLE Data to Excel, txt, and HTML

 

 

A. Use PL/SQL Developer to display ORACLE Data in Excel, tsv (text), xml, and csv formats.

 

Environment: ORACLE10.1 and PL/SQL Developer Version 7.0.1.1066

Open PL/SQL Developer, File --> New --> Report Window -->

In SQL, insert the SQL statement to be exported.

For example, select * from T2

Select the rows (Execute (F8 ))

After checking the results, the results are exported to the Edit Report in HTML format.

1. In the response box, click Save as HTML... to Save the result as HTML.

2. in the displayed dialog box, click "Export Results... "The results can be saved as CSV File, TSV File (text form txt), XML File, and Excel.

3. In addition, you can generate a Graph worker through Graph Wizard.

 

B. use Excel's external data import function to directly retrieve data from ORACLE through ODBC Data sources

The method is as follows:

1. (Windows 2003) "Control Panel" --> "Administrative Tools" --> "Data Source (ODBC)" --> select the system DSN from ODBC data source Manager

Add a system data source, such as ORACLE10, and complete the Configuration in Oracle ODBC Driver Configuration. Ensure that the Test Connection succeeded successfully (Connection Successful ).

 

2. Open Excel2003, "data volume" --> "Incoming external data volume" --> "incoming data volume (D )..."

Select "data source selection" --> "connect to new data source" --> enter "data source connection" and select odbc dsn --> "Next"

Select an ODBC Data Source (for example, ORACLE10) that has been created and completed successfully. Next, import the password to the database and select a table.

 

3. Finally, you can import the password of the ORACLE database to the specified cell.

 

C. Use the SQL * PLUS command to export data to text (txt, csv)

Example: www.2cto.com

1. upload an export. SQL statement (path d: \) with the following content:

Set feedback off heading off verify off trimspool off

Set pagesize 0 linesize 1000

Define fil = 'd: \ ORACLE_output.txt'

Prompt *** Spooling to & fil

Spool & fil

Select ID | ',' | '"' | NAME | '"' | ', '|' "'| ADDRESS |'" '|', '| to_char (Time ,'

Yyyy-MM-dd HH24: MI: ss') from T2;

 

Spool off;

 

Use (,) as the delimiter/field to separate them.

2. Ignore rows

SQL> @ d: \ export. SQL

3. view results

SQL> host

Microsoft Windows [version 5.2.3790]

(C) Copyright 1985-2003 Microsoft Corp.

 

C: \ Documents ents and Settings \ Administrator> type D: \ ORACLE_output.txt

The result is displayed. OK, and the output text (or csv can be merged) has been completed.

 

From Qingshui tea