odbc dsn mysql_配置ODBC DSN数据源,导出数据库数据到Excel过程记录

本文介绍了如何配置ODBC数据源,特别是针对Oracle和MySQL数据库,通过ODBC连接数据库,解决大容量数据导出到Excel时内存不足的问题。详细步骤包括下载和安装Oracle Database Client、Instant Client,配置环境变量,创建ODBC数据源,最后在Excel中使用Microsoft Query导出数据。
摘要由CSDN通过智能技术生成

一、前言

工作中我们可能遇到这样的需要:查询数据库中的信息,并将结果导出到Excel文件。这本来没什么,但数据量比较大时,用PLSQL、toad导出Excel会出现内存不足等情况,使用odbc+Microsoft query + Excel导出提数结果能在一定程度上避免这些问题。

环境:个人电脑8G内存,导出130W条记录毫无压力。

二、下载、安装Oracle Database Client

Windows默认没有按照Oracle的ODBC驱动,需要手动安装,Oracle Database Client包含了Oracle ODBC驱动组件。

1、下载

在搜索框中输入oracle database client,然后选择系统平台为Windows 64,选定oracle database client ,然后按照提示,下载该组件

20180110235707204626.png

20180110235707205602.png

20180110235707207556.png

下载该组件,可能单击 download,没有反应,鼠标移动到要下载的软件位置,可用右击“目标另存为”方式下载要下载的组件

20180110235707209509.png

2、安装

20180110235707210485.png

20180110235707211462.png

20180110235707213415.png

20180110235707214392.png

20180110235707215368.png

20180110235707217322.png

20180110235707218298.png

20180110235707220251.png

20180110235707221228.png

三、下载、安装Instant Client

1、下载

打开链接:

选择适用于Windows32位系统的instant client即可,下载该组件

20180110235707223181.png

20180110235707224158.png

选择下面两个版本中的任何一项均可:

20180110235707226111.png

2、安装

该组件直接解压即可,无需安装。解压到某个指定的目录下,在解压后的文件目录下创建子目录/network/admin,并将配置好的tnsname.ora文件拷贝到该目录下(或手动创建该文件并配置该文件,步骤略)

20180110235707227088.png

设置环境变量:

右击计算机—>属性—> 高级选项卡à环境变量—>新建系统变量或用户变量(用户变量只对当前用户有效,系统变量对所有用户有效)à设置环境变量ORACLE_HOME,指向解压文件目录。“确定”,退出

20180110235707229041.png

同样,设置环境变量TNS_ADMIN,指向instantclient下的network\admin目录,这样,odbc数据源就可以自动获取tns信息,如下:

20180110235707230017.png

3、可能遇到的问题:若不配置环境变量ORACLE_HOME,在使用ODBC数据源时,会报错“Unable to connect SQLState=08004 [Oracle][ODVC][Ora]ORA-12154:TNS:could not resolve the connect identifier specified”,这种情况同样发生在使用oracle instant组件的PowerDesigner等工具连接数据库的情况下。

20180110235707230994.png

四、配置odbc数据源

(1)打开odbc数据源管理工具

方式一:在开始-->所有程序中或搜索odbc,找到Microsoft ODBC管理员程序

20180110235707232947.png

方式二:控制面板-->管理工具-->数据源

20180110235707233924.png

(2)添加用户DSN

用户DSNà添加,选择Oracle驱动(MySQL、SQLserver等可安装并选择对应的驱动),完成

20180110235707243690.png

(4)填写ODBC配置信息并测试连通性

填写odbc数据源配置信息,标注部分为必填项,数据源名称为任意有含义的字符串,可用中文,TNS Server Name格式为“ip:port/数据库名称”,User ID 为访问数据库的用户名(port默认是1521,请记得防火墙和杀软放行1521端口),数据库名称选用tsnnames.ora文件中的SERVICE_NAME的值。

20180110235707255409.png

注意:TNS Service Name填写方式有两种方法:

第一种,填写完整格式:TNS Service Name的完整格式是: 数据库主机名:端口号/数据库实例名。

第二种:如果配置的数据源很多,可以按照Oracle instant Client,这里安装了instant client,并设置了TNS_ADMIN的环境变量,TNS Service Name选项下会有所有已经设置TNS的数据库实例名列表。当要操作的数据库实例比较多时,这种方法很方便。

填写完毕,先测试数据源连通性“Test Connection”,输入密码,“OK”

20180110235707263222.png

若提示连接成功,说明配置无误,“确定”,“OK”,退出管理程序。否则,检查并修改配置信息。

20180110235707271035.png

四、创建临时表

执行SQL语句,将结果集较大的查询语句加工到临时表中,如下:

CREATE TABLE TMP_TEST AS

SELECTE.EMPNO, E.ENAME, E.JOB, E.MGR, E.HIREDATE, E.SAL, D.DNAME, D.LOCFROMEMP E, DEPT DWHERE E.DEPTNO = D.DEPTNO;

五、配置Excel,导出数据

(1)新建Excel,数据—>自其他来源—>来自Microsoft Query

20180110235707272988.png

(2)选择配置好的数据源,“确定”

20180110235707273964.png

(3)填写对应的密码

20180110235707275918.png

(4)选项里可设置查找要导出的对象的范围,进入“选项”

20180110235707277871.png

(5)表选项里可设置要导出的对象:表、视图、同义词等,注意选择用户,当有大量用户和表时,可大大缩小查找范围

20180110235707278847.png

(6)在左侧要导出的表中,找到要导出的字段,>按钮可将选择好的字段导入到右侧的待查询列中。

20180110235707279824.png

待筛选的列,可根据需要对字段进行条件设置

20180110235707281777.png

排序顺序,可选择关键字排序,同时可选择多个关键字

20180110235707282754.png

导出默认即可,当然,也可以使用Microsoft query编辑查询,实现一些复杂的查询,并将结果导出到Excel中。

20180110235707284707.png

选择导入到Excel中的位置。

20180110235707285684.png

导出结果示例如下:

20180110235707287637.png

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值