Configure an ODBC DSN data source to export database data to Excel process records

First, preface

We may encounter this need at work: Query the information in the database and export the results to an Excel file. This would have been nothing, but when the amount of data is large, using Plsql, Toad Export Excel will be out of memory, and so on, using Odbc+microsoft query + Excel to export the results can be to some extent avoid these problems.

Environment: PC 8G memory, export 130W record without pressure.

Ii. Downloading and installing Oracle Database Client

Windows defaults to Oracle's ODBC driver and requires manual installation, and Oracle Database client contains the Oracle ODBC driver components.

1. Download

Address: Https://edelivery.oracle.com/osdc/faces/SearchSoftware

Enter Oracle database client in the search box, select the Oracle database client for the system platform for Windows 64, and then follow the prompts to download the component

To download the component, you may click Download, do not respond, move the mouse to the location of the software to be downloaded, and you can right-click on "Save Target as" to download the component to download

2. Installation

Third, download, install instant Client1, download

To open a link:

Http://www.oracle.com/technetwork/cn/database/features/instant-client/index-092699-zhs.html,

Select the instant client for the WINDOWS32-bit system to download the component

Select any of the two versions below to:

2. Installation

The component can be decompressed directly without the need for installation. Unzip to a specified directory, under the extracted file directory to create a subdirectory/network/admin, and copy the configured Tnsname.ora file to this directory (or manually create the file and configure the file, step slightly)

 

Set Environment variables:

Right-click Computers, Properties, Advanced tab, environment variables, new system variables or user variables (user variables are valid only for the current user, system variables are valid for all users) à set environment variable oracle_home, point to Extract Files directory. "OK", exit

Similarly, set the environment variable tns_admin, point to the network\admin directory under Instantclient, so that the ODBC data source can automatically get TNS information, as follows:

3, may encounter the problem: If you do not configure the environment variable Oracle_home, when using ODBC data source, will error "Unable to connect sqlstate=08004 [Oracle][odvc][ora]ora-12154:tns: Could not resolve the connect identifier specified ", this is also the case with tools such as instant with Oracle PowerDesigner components to connect to the database.

Iv. Configuring an ODBC data source (1) Open the ODBC Data source management tool

Way one: In Start-to-All programs or search ODBC, find the Microsoft ODBC Administrator program

Mode two: Control Panel--Administration tools--data source

(2) Add User DSN

User dsnà Add, select Oracle Driver (MySQL, SQL Server, etc. can be installed and select the corresponding driver), complete

(4) Fill in ODBC configuration information and test connectivity

Fill in the ODBC data source configuration information, the callout part is required, the data source name is any meaningful string, available in Chinese, TNS Server name format is "ip:port/database name", user ID is the username to access the database (port default is 1521, Please remember firewall and kill soft release 1521 port), the database name selects the value of service_name in the Tsnnames.ora file.

Note: The TNS Service name filling method is available in two ways:

First, fill in the full format: The full format of the TNS Service name is: Database hostname: Port number/DB instance name.

Second: If you configure a large number of data sources, you can follow the Oracle Instant Client, where the instant client is installed, and tns_admin environment variables are set, the TNS Service Under the name option, there will be a list of all DB instance names that have been set for TNS. This is handy when you are working with a database instance that is relatively long.

Complete, first test data source connectivity "Test Connection", enter the password, "OK"

If the connection is successful, the configuration is correct, "OK", "OK", exit the management program. Otherwise, examine and modify the configuration information.

 

Iv. Creating a temporary table

Executes the SQL statement, processing the query statement with the larger result set into a temporary table, as follows:

CREATE TABLE  as SELECT E.empno, E.ename, E.job, E.mgr, E.hiredate, E.sal   , D.dname, D.loc  from  WHERE= D.deptno;

V. Configure Excel to export data

(1) New Excel, data, from other sources, from Microsoft Query

(2) Select a configured data source, "OK"

(3) Fill in the corresponding password

(4) option to set the range of objects to be exported, go to "options"

(5) Table options can be set to export objects: tables, views, synonyms, etc., pay attention to select users, when there are a large number of users and tables, can greatly reduce the scope of the search

(6) In the table to be exported on the left, locate the field you want to export,> button to import the selected field into the column to be queried on the right.

Columns to filter to conditionally set the fields as needed

Sort order, select keywords to sort, and select multiple keywords

Export defaults, of course, you can also use Microsoft Query to edit queries, implement some complex queries, and export the results to Excel.

Select a location to import into Excel.

Examples of export results are as follows:

The original address of this article: http://www.cnblogs.com/chinas/p/6991311.html, reprint please indicate the source, thank you!!!

Configure an ODBC DSN data source to export database data to Excel process records