文本文件导入到数据库中的几种方法
直接在excel里面生成SQL脚本来导入数据:
="insert into income1(WORKER_ID,NAME,INCOME,DEPARTMENT,YEAR) values("&A2&",'"&B2&"',"&C2&",'"&D2&"',to_date('"&E2&"','yyyy'));"
"&A2&"即excel表的A2单元格，如果在数据库表里这个字段是字符串则要加上单引号'"&A2&"'
文本文件导入到数据库中的几种方法
2004-11-19 11:05:44
原创 计世网 冯杰
大型的数据库开发中常常遇到数据源是平面文件（如文本文件）的情况，对于这样的数据源，无法使用数据库对其数据进行有效的管理，另外也无法使用SQL语句对其进行查询和操作，所以当务之急就是将这些平面文件导
入到数据库中，然后就可以对其进行高效的操作了。
下面介绍几种常见的数据导入的方法，希望能够给大家启迪。另外，本文所涉及到的数据库均为ORACLE数据库，其实对于其他数据库而言，方法类似。
一、Sql*:Loader
该方法是Oracle数据库下数据导入的最重要的方法之一，该工具由Oracle客户端提供，
其基本工作原理是：首先要针对数据源文件制作一个控制文件，控制文件是用来解释如何对源文件进行解析，其中需要包含源文件的数据格式、目标数据库的字段等信息，一个典型的控制文件为如下形式：
LOAD DATA
INFILE '/ora9i/fengjie/agent/data/ipaagentdetail200410.txt'
TRUNCATE
INTO TABLE fj_ipa_agentdetail
fields terminated ","
trailing nullcols
( AGENT_NO char,
AGENT_NAME char,
AGENT_ADDRESS char,
AGENT_LINKNUM char,
AGENT_LINKMAN char
)
其中，INFILE '/ora9i/fengjie/agent/data/ipaagentdetail200410.txt'指明所要导入的源文件，其实源文件也可以直接通过命令行来输入获得 ，fj_ipa_agentdetail为目标表的名字，fields terminated ","是指源文件的各个字段是以逗号分隔，trailing nullcols表示遇到空字段依然写入到数据库表中，最后这５个字段是目标数据库表的字段结构。通过上面这个典型的控制文件的格式分析可知，控制文件需要与源文件的格式信息一致，否则导入数据会出现异常。
除了控制以外，sql*loader的还需要数据文件，即源文件。根据格式的不同，源文件可以分为固定字段长度和有分隔符这两大类，这里将分别说明这两种情况：
固定字段长度的文本文件
就是每个字段拥有固定的字段长度，比如：
602530005922 1012
602538023138 1012
602536920355 1012
602531777166 1012
602533626494 1012
602535700601 1012
有分隔符的文本文件
就是每个字段都有相同的分隔符分隔，比如：
1001,上海长途电信综合开发公司,南京东路34号140室
1002,上海桦奇通讯科技有限公司,武宁路19号1902室
1003,上海邦正科
技发展有限公司,南京东路61号903室
对于上述两种文件格式sql*loader均可以做处理，下面就前面那个固定长度的文本来举例说明：
由于该文本只有两个字段，一个为设备号，一个是区局编号，两者的长度分别为２０和５，那么可以编制控制文件如下：
LOAD DATA
INFILE '/ora9i/fengjie/agent/data/ipaagent200410.txt'
TRUNCATE
INTO TABLE fj_ipa_agent
( DEVNO POSITION(1:20) CHAR,
BRANCH_NO POSITION(21:25) CHAR
)
其中，'/ora9i/fengjie/agent/data/ipaagent200410.txt'为该文件的完全路径，POSITION(M:N)表示该字段是从位置M到位置N。
对于有分隔符的数据文件，前面已经有一个例子，这里就不再赘述了。总之，使用Sql*Loader能够轻松将数据文件导入到数据库中，这种方法也是最常用的方法。
二、 使用专业的数据抽取工具
目前在数据仓库领域中，数据抽取与装载（ETL）是一重要的技术，这一技术对于一些大的数据文件或者文件数量较多尤其适合。这里简单介绍目前一款主流的数据抽取工具 ――Informatica。
该工具主要采用图形界面进行编程，其主要工作流程是：首先将源数据文件的结构（格式）导入为Informatica里，然后根据业务规则对该结构进行一定的转换(transformation)，最终导入到目标表中。
以上过程仅仅只是做了一个从源到目标的映射，数据的实际抽取与装载需要在工作流（workflow）里进行。
使用专业的数据抽取工具，可以结合业务逻辑对多个源数据进行join,union,insect等操作，适合于大型数据库和数据仓库。
三、 使用Access工具导入
可以直接在Access里选择‘打开‘文本文件，这样按照向导来导入一个文本文件到Access数据库中，然后使用编程的方法将其导入到最终的目标数据 库中。
这种方法虽然烦琐，但是其对系统的软件配置要求相对较低，所以也是有一定的使用范围。
四、 小结
总之，平面文件转化为数据库格式有利于数据的处理，显然，数据库强大的数据处理能力比直接进行文件I/O效率高出很多，希望本文能够对该领域做一个抛砖引玉的作用。
学习Oracle SQL loader 的使用(1)
一：sql loader 的特点
oracle自己带了很多的工具可以用来进行数据的迁移、备份和恢复等工作。但是每个工具都有自己的特点。比如说exp和imp可以对数据库中的数据进行导出和导出的工作，是一种很好的数据库备份和恢复的工具，因此主要用在数据库的热备份和
恢复方面。有着速度快，使用简单，快捷的优点；同时也有一些缺点，比如在不同版本数据库之间的导出、导入的过程之中，总会出现这样或者那样的问题，这个也许是oracle公司自己产品的兼容性的
问题吧。
sql loader 工具却没有这方面的问题，它可以把一些以文本格式存放的数据顺利的导入到oracle数据库中，是一种在不同数据库之间进行数据迁移的非常方便而且通用的工具。缺点就速度比较慢，另外对blob等类型的数据就有点麻烦了。
二：sql loader 的帮助
C:\>sqlldr
SQL*Loader: Release 9.2.0.1.0 - Production on 星期六 10月 9 14:48:12 2004
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
用法: SQLLDR keyword=value [,keyword=value,...]
有效的关键字:
userid -- ORACLE username/password
control -- Control file name
log -- Log file name
bad -- Bad file name
data -- Data file name
discard -- Discard file name
discardmax -- Number of discards to allow (全部默认)
skip -- Number of logical records to skip (默认0)
load -- Number of logical records to load (全部默认)
errors -- Number of errors to allow (默认50)
rows -- Number of rows in conventional path bind array or between direct path data saves
（默认: 常规路径 64, 所有直接路径）
bindsize -- Size of conventional path bind array in bytes(默认256000)
silent -- Suppress messages during run (header,feedback,errors,discards,partitions)
direct -- use direct path (默认FALSE)
parfile -- parameter file: name of file that contains parameter specifications
parallel -- do parallel load (默认FALSE)
file -- File to allocate extents from
skip_unusable_indexes -- disallow/allow unusable indexes or index partitions(默认FALSE)
skip_index_maintenance -- do not maintain indexes, mark affected indexes as unusable(默认FALSE)
readsize -- Size of Read buffer (默认1048576)
external_table -- use external table for load; NOT_USED, GENERATE_ONLY, EXECUTE(默认NOT_USED)
columnarrayrows -- Number of rows for direct path column array(默认5000)
streamsize -- Size of direct path stream buffer in bytes(默认256000)
multithreading -- use multithreading in direct path
resumable -- enable or disable resumable for current session(默认FALSE)
resumable_name -- text string to help identify resumable statement
resumable_timeout -- wait time (in seconds) for RESUMABLE(默认7200)
date_cache -- size (in entries) of date conversion cache(默认1000)
PLEASE NOTE: 命令行参数可以由位置或关键字指定。前者的例子是 'sqlloadscott/tiger foo'; 后一种情况的一个示例是 'sqlldr
control=foouserid=scott/tiger'.位置指定参数的时间必须早于但不可迟于由关键字指定的参数。例如,允许 'sql
ldr scott/tiger
control=foo logfile=log', 但是不允许 'sqlldr scott/tiger control=foo log', 即使参数 'log' 的位置正确。
C:\>
三：sql loader使用例子
a）SQLLoader将 Excel 数据导出到 Oracle
1.创建SQL*Loader输入数
据所需要的文件,均保存到C:\，用记事本编辑：
控制文件：input.ctl，内容如下：
load data --1、控制文件标识
infile 'test.txt' --2、要输入的数据文件名为test.txt
append into table test --3、向表test中追加记录
fields terminated by X'09' --4、字段终止于X'09'，是一个制表符（TAB）
(id,username,password,sj) -----定义列对应顺序
a、insert，为缺省方式，在数据装载开始时要求表为空
b、append，在表中追加新记录
c、replace，删除旧记录，替换成新装载的记录
d、truncate，同上
在DOS窗口下使用SQL*Loader命令实现数据的输入
C:\>sqlldr userid=system/manager control=input.ctl
默认日志文件名为：input.log
默认坏记录文件为：input.bad
2.还有一种方法
可以把EXCEL文件另存为CSV（逗号分隔）(*.csv),控制文件就改为用逗号分隔
LOAD DATA
INFILE 'd:\car.csv'
APPEND INTO TABLE t_car_temp
FIELDS TERMINATED BY ","
(phoneno,vip_car)
b）在控制文件中直接导入数据
1、控制文件test.ctl的内容
-- The format for executing this file with SQL Loader is:
-- SQLLDR control=<filename> Be sure to substitute your
-- version of SQL LOADER and the filename for this file.
LOAD DATA
INFILE *
BADFILE 'C:\Documents and Settings\Jackey\桌面\WMCOUNTRY.BAD'
DISCARDFILE 'C:\Documents and Settings\Jackey\桌面\WMCOUNTRY.DSC'
INSERT INTO TABLE EMCCOUNTRY
Fields terminated by ";" Optionally enclosed by '"'
(
COUNTRYID NULLIF (COUNTRYID="NULL"),
COUNTRYCODE,
COUNTRYNAME,
CONTINENTID NULLIF (CONTINENTID="NULL"),
MAPID NULLIF (MAPID="NULL"),
CREATETIME DATE "MM/DD/YYYY HH24:MI:SS" NULLIF (CREATETIME="NULL"),
LASTMODIFIEDTIME DATE "MM/DD/YYYY HH24:MI:SS" NULLIF (LASTMODIFIEDTIME="NULL")
)
BEGINDATA
1;"JP";"Japan";1;9;"09/16/2004 16:31:32";NULL
2;"CN";"China";1;10;"09/16/2004 16:31:32";NULL
3;"IN";"India";1;11;"09/16/2004 16:31:32";NULL
4;"AU";"Australia";6;12;"09/16/2004 16:31:32";NULL
5;"CA";"Canada";4;13;"09/16/2004 16:31:32";NULL
6;"US";"United States";4;14;"09/16/2004 16:31:32";NULL
7;"MX";"Mexico";4;15;"09/16/2004 16:31:32";NULL
8;"GB";"United Kingdom";3;16;"09/16/2004 16:31:32";NULL
9;"DE";"Germany";3;17;"09/16/2004 16:31:32";NULL
10;"FR";"France";3;18;"09/16/2004 16:31:32";NULL
11;"IT";"Italy";3;19;"09/16/2004 16:31:32";NULL
12;"ES";"Spain";3;20;"09/16/2004 16:31:32";NULL
13;"FI";"Finland";3;21;"09/16/2004 16:31:32";NULL
14;"SE";"Sweden";3;22;"09/16/2004 16:31:32";NULL
15;"IE";"Ireland";3;23;"09/16/2004 16:31:32";NULL
16;"NL";"Netherlands";3;24;"09/16/2004 16:31:32";NULL
17;"DK";"Denmark";3;25;"09/16/2004 16:31:32";NULL
18;"BR";"Brazil";5;85;"09/30/2004 11:25:43";NULL
19;"KR";"Korea, Republic of";1;88;"09/30/2004 11:25:43";NULL
20;"NZ";"New Zealand";6;89;"09/30/2004 11:25:43";NULL
21;"BE";"Belgium";3;79;"09/30/2004 11:25:43";NULL
22;"AT";"Austria";3;78;"09/30/2004 11:25:43";NULL
23;"NO";"Norway";3;82;"09/30/2004 11:25:43";NULL
24;"LU";"Luxembourg";3;81;"09/30/2004 11:25:43";NULL
25;"PT";"Portugal";3;83;"09/30/2004 11:25:43";NULL
26;"GR";"Greece";3;80;"09/30/2004 11:25:43";NULL
27;"IL";"Israel";1;86;"09/30/2004 11:25:43";NULL
28;"CH";"Switzerland";3;84;"09/30/2004 11:25:43";NULL
29;"A1";"Anonymous Proxy";0;0;"09/30/2004 11:25:43";NULL
30;"A2";"Satellite Provider";0;0;"09/30/2004 11:25:43";NULL
31;"AD";"Andorra";3;0;"09/30/2004 11:25:43";NULL
32;"AE";"United Arab Emirates";1;0;"09/30/2004 11:25:43";NULL
33;"AF";"Afghanistan";1;0;"09/30/2004 11:25:43";NULL
34;"AG";"Antigua and Barbuda";7;0;"09/30/2004 11:25:43";NULL
35;"AI";"Anguilla";7;0;"09/30/2004 11:25:43";NULL
36;"AL";"Albania";3;0;"09/30/2004 11:25:43";NULL
37;"AM";"Armenia";3;0;"09/30/2004 11:25:43";NULL
38;"AN";"Netherlands Antilles";3;0;"09/30/2004 11:25:43";NULL
39;"AO";"Angola";2;0;"09/30/2004 11:25:43";NULL
40;"AP";"Asia/Pacific Region";2;0;"09/30/2004 11:25:43";NULL
41;"AQ";"Antarctica";8;0;"09/30/2004 11:25:43";NULL
42;"AR";"Argentina";5;0;"09/30/2004 11:25:43";NULL
43;"AS";"
American Samoa";6;0;"09/30/2004 11:25:43";NULL
44;"AW";"Aruba";5;0;"09/30/2004 11:25:43";NULL
45;"AZ";"Azerbaijan";1;0;"09/30/2004 11:25:43";NULL
46;"BA";"Bosnia and Herzegovina";3;0;"09/30/2004 11:25:43";NULL
47;"BB";"Barbados";5;0;"09/30/2004 11:25:43";NULL
48;"BD";"Bangladesh";1;0;"09/30/2004 11:25:43";NULL
49;"BF";"Burkina Faso";2;0;"09/30/2004 11:25:43";NULL
50;"BG";"Bulgaria";3;0;"09/30/2004 11:25:43";NULL
51;"BH";"Bahrain";1;0;"09/30/2004 11:25:43";NULL
52;"BI";"Burundi";2;0;"09/30/2004 11:25:43";NULL
53;"BJ";"Benin";2;0;"09/30/2004 11:25:43";NULL
54;"BM";"Bermuda";4;0;"09/30/2004 11:25:43";NULL
55;"BN";"Brunei Darussalam";1;0;"09/30/2004 11:25:43";NULL
56;"BO";"Bolivia";5;0;"09/30/2004 11:25:43";NULL
57;"BS";"Bahamas";7;0;"09/30/2004 11:25:43";NULL
58;"BT";"Bhutan";1;0;"09/30/2004 11:25:43";NULL
59;"BV";"Bouvet Island";5;0;"09/30/2004 11:25:43";NULL
60;"BW";"Botswana";2;0;"09/30/2004 11:25:43";NULL
61;"BY";"Belarus";3;0;"09/30/2004 11:25:43";NULL
2、执行导入命令
C:\>sqlldr userid=system/manager control=test.ctl
c）复杂格式的导入
如何使用SQL*Loader？
这个文章一定要看的。明天再看把，今天太累了！
如何使用SQL*Loader？
dragon2> sqlldr userid=c/c control=lc2.ctl log=lc2.log bad=lc2.bad
SQL*Loader: Release 9.2.0.1.0 - Production on Wed Jun 4 18:03:53 2003
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Commit point reached - logical record count 50
dragon2>
其中lc2.ctl内容为：
#--------------------------------------------
LOAD DATA
INFILE *
REPLACE
INTO TABLE lc2
FIELDS TERMINATED BY ","
(LCSH,LCZT,LCRQ,LCSJ,LCGH)
BEGINDATA
673969,1,20010205,174948,0000
673969,3,20010205,174709,0000
...
#--------------------------------------------
[06.05]：如何知道安装了哪些Oracle选项？
col PARAMETER for a35
col VALUE for a35
select * from v$option;
[06.04]：如何增加表空间的大小？
1、加大表空间中数据文件的大小：
ALTER DATABASE DATAFILE 'filename' RESIZE nn M;
2、给表空间添加新的数据文件：
ALTER TABLESPACE tablespacename
ADD DATAFILE 'filename' SIZE nn M;
如果你没有使用OMF，filename需要包含详细路径。
[06.03]：如何类似创建一个空表？
CREATE TABLE NEW_TABLE_NAME AS
SELEC
T * FROM OLD_TABLE_NAME WHERE 1=2;
[06.02]：9i exp blob ORA-01555错误？
导致9i exp blob时出现
ORA-01555: snapshot too old: rollback segment number with name "" too small
错误的原因是9i blob的bug。
一般来说exp ORA-01555可以通过下面方法来解决：
1）加大所有回滚段且不允许自动收缩（因为我们无法控制EXP具体会使用哪个回滚段）；
2）EXP使用consistent = n(只保证一个表内数据是同一时刻的)和direct = y参数；
3）安排在数据库任务最少的时候做EXP；
4）对于特大表可采用query子句分批EXP该表。
[05.30]：ORA-02049 via db_link？
hawk1:/oracle/oracle9$oerr ora 2049
02049, 00000, "timeout: distributed transaction waiting for lock"
// *Cause: exceeded INIT.ORA distributed_lock_timeout seconds waiting for lock.
// *Action: treat as a deadlock
很多时候DISTRIBUTED_LOCK_TIMEOUT参数是不设的，这个错误很多时候是程序引起的。当然有人建议使用DBMS_DEFFR.CALL和DBMS_DEFFR_SYS.PUSH来代替直接的两段式提交。
[05.29]：为何导致连接关闭？
昨天在做一项测试时，我在本地PC设置了NLS_DATE_FORMAT，但手误写错了，把hh24写成了24hh，当时因为SQL*PLUS有现成的连接在数据库上，所以也没发现有什么影响。此后重起PC后无论如何都连不上数据库了，每次SQL*PLUS都是一闪而过就消失了，我根本看不到它提示的是什么错误，弄的我一头雾水，查遍了我可以想到的原因就是没发现问题所在，后来尝试tnsping（因为我一直确信网络没有问题）告知日期格式错误，才猛然想起。
[05.28]：817 EXP 815报错EXP-00037？
直接使用817的EXP来导出815的数据会报EXP-00037错误。我们可以先使用815 EXP，然后使用817 IMP即可。
[05.26]：如何取得一列中第N大的值？
select * from
(select t.*,dense_rank() over (order by t2 desc) rank from t)
where rank = &N;