利用官方工具将百万级cvs数据文件导入oracle数据库（Windows系统）

遇到的问题：需要跨服务器将数据处理到另一台数据库，数据为百万级，凭借代码获取导入时间过慢

处理方法：利用oracle提供的官方数据sqlldr

一、 安装官方工具

下载sqlldr官方工具链接

下载好以后解压，会得到一个命名为instantclient_19_17的文件夹，打开里面会有sqlldr.exe的文件

 二、 打开dos进入到该文件夹里，输入sqlldr测试是否安装完成

#此处我输入时提示【无法启动此程序，因为计算机中丢失VCRUNTIME140.dll】，于是还要下载Windows插件：vs_redist_x64下载地址

安装完成会显示如下图数据

 三、 使用sqlldr

1、 首先编辑执行脚本文件，文件名：load_csv_data.ctl

options(skip=1)
load data
CHARACTERSET UTF8
infile 'D:\dir1\dir2\20221205\part-001.csv'
append into table "table_name"
WHEN status = 'used'
fields terminated by '|'
TRAILING NULLCOLS
(id,name,age,address,col01,col02)

释义

options(skip=1)                                                #跳过第一行（一般用于文件首行为表头的情况）
load data
CHARACTERSET UTF8                                #设置编码格式（当导入数据库数据出现中文乱码时设置此行）
infile 'D:\dir1\dir2\20221205\part-001.csv'        #导入文件的路径

infile 'D:\dir1\dir2\20221205\part-002.csv'         #可多行
append into table "table_name"                        #对应的数据库表名

WHEN status = 'used'                                        #过滤数据条件
fields terminated by ''                                        #导入文件数据分隔符，看文件内容更换
TRAILING NULLCOLS                                        #遇到空值也作插入，没有此行大概率会报错
(id,name,age,col01,col02)                                        #对应的表中字段名，与导入文件数据列顺序需一一对应

part-001.csv（或part-002.csv）文件内样式为：

 2、 使用命令行执行脚本

sqlldr userid=test/1234@127.0.0.1:3363/dbname control=D:\脚本\load_csv_data.ctl log=log\load_csv_data.log

释义：

sqlldr userid=数据库登录用户名/登录密码@数据库服务器ip:端口/数据库名 control=ctl文件存放文职 log=日志打印位置

 在sqlldr.exe存放路径下执行上述命令就可以了~~~

四、 报错

记录 28616836: 被拒绝 - 表 "TABLE_NAME" 的列 ADDRESS 出现错误。
数据文件的字段超出最大长度

上述报错是指ADDRESS这个字段对应的导入数据内容超过缓存默认长度

因此需要指定该字段默认大小

options(skip=1)
load data
CHARACTERSET UTF8
infile 'D:\dir1\dir2\20221205\part-001.csv'
append into table "table_name"
WHEN status = 'used'
fields terminated by '|'
TRAILING NULLCOLS
(id,name,age,address CHAR(1024),col01,col02)

五、 进阶

动态更改导入文件路径，脚本执行自动获取文件并导入至oracle数据库中，使用bat脚本以及

1、 按照oracle表中字段以及引入文件数据对应位置，编写模板脚本，其中入参为可变项，即infile，自定义参数名，用于注入替换文本。

load_data_temp.ctl

options(skip=1)
load data
CHARACTERSET UTF8
infile 'datafilename'
append into table "table_name"
WHEN status = 'used'
fields terminated by '|'
TRAILING NULLCOLS
(id,name,age,address CHAR(1024),col01,col02)

 2、 （此步骤非必要，看自己对文件位置要求）设置执行脚本文件存放位置，即在你想要放执行文件的位置新建文件夹，此处我新建为tableName文件夹底下的tableNameCTLs文件夹存放生成的执行脚本，此路径在稍后的bat脚本编写时会用到，不设置则会在bat脚本执行文件夹下。

3、 编写执行脚本

getCSV&InsertDB.bat

setlocal enabledelayedexpansion
::下列步骤为生成yyyyMMdd格式字符串，根据实际情况是需要与否
::获取年
set yyyy=%date:~0,4%
::获取月
set mm=%date:~5,2%
::获取日
set dd=%date:~8,2%
::若天数为个位数数字，去掉前面的0，转换为数字进行后续的计算
if "%dd:~,1%"=="0"  set dd=%dd:~1%
::将天数强转为数字计算，此处减一为获取当前天数前一天的日期
set /a od=!dd!-1
::如果减一到最后变成0，则从头开始
if !od!==0 call :dd0
if !mm!==0 call :mm0
::如果生成的数字小于10为个位数数字则前面拼接一个0，拼接为正确格式
if !od! lss 10 (set od=0!od!)
if !mm! lss 10 (set mm=0!mm!)
::CURRENT_DATE_STRING为生成的符合要求格式的字符串
set CURRENT_DATE_STRING=!yyyy!!mm!!od!
::拼凑指定路径，按需拼凑
set DestPath=D:\dir1\dir2\%CURRENT_DATE_STRING%
::指定路径下文件格式
set DestExt=*.csv
::清空生成文件内容，防止拼接进垃圾字符串
type nul > D:\bats\tableName\tableNameCTLs\load_data_%CURRENT_DATE_STRING%.ctl
::指定初始位置，为指定循环次数位置进行操作做准备
set num=1
::开始按行循环模板文件，逐行拼接，并在指定行替换上述拼凑的链接
for /f "usebackq delims=" %%k in ("D:\bats\tableName\load_data_temp.ctl") do (
	::获取当前行，进行相应的处理后写入生成的新的执行文件中
	set "line=%%k"
	::要替换的文本位置在模板文件第四行，因此写死为第四行执行以下步骤
	if !num! == 4 (
		::，根据sqlldr文件性质，infile文件可以多行去执行多个文件的写入，所以打开存放需要导入至数据库的文件的路径，循环底下指定格式文件，并拼接写入生成的执行文件中
		for /f "usebackq delims=" %%i in (`dir /b/a-d/s  "%DestPath%\%DestExt%"`)  do (
			::重新获取需要替换的行，避免下一个循环需要替换的自定义字段已经被替换掉了
			set "line=%%k"
			::获取该行中自定义的datafilename字段并替换为此循环获取的文件下载链接位置
			set "line=!line:datafilename=%DestPath%\%%~nxi!"
			::遍历写进生成的新执行文件中
			echo !line!>>"D:\bats\tableName\tableNameCTLs\load_data_%CURRENT_DATE_STRING%.ctl"
		)
	) else (echo !line!>>"D:\bats\tableName\tableNameCTLs\load_data_%CURRENT_DATE_STRING%.ctl")
	::行数加一执行下一行
	set /a num+=1
)
::执行文件生成结束后，执行该命令写入数据库中，并在指定的log位置生成日志，注意：log位置文件夹需先设置
sqlldr userid=test/1234@127.0.0.1:3363/dbname control=D:\bats\tableName\tableNameCTLs\load_data_%CURRENT_DATE_STRING%.ctl log=log\tableName\load_csv_data_%CURRENT_DATE_STRING%.log

 将该文件放在sqlldr.exe文件下，进行后续定时任务

4、 定时任务设置（win10系统）

此路径下找到

 

 打开点击创建基本任务进行细节的定时任务设置操作

按需填写即可，注意两个地方

1） 操作步骤的起始于，需要填写路径为当前脚本存放路径，因为此路径默认为c盘的system路径

2）为防机器登录用户不同导致脚本执行中断，设置定时任务时，记得勾选最高权限执行。