使用批处理实现解析文本数据文件写入数据库表

本文实例讲述通过批处理解析文本数据文件写入数据库表的实现方法。分享给大家供大家参考。具体实现方法如下：

@echo off & setlocal enabledelayedexpansion
:param
    set data_path=
    set /p data_path=请输入卡交易文件存放目录:
    @echo 你输入的卡交易文件存放目录为:"%data_path%" 
    call :stringlenth "%data_path%" num
    ::@echo 字符串长度为：%num% 
    ::判断是否输入了文件存放路径，否则提示重新输入
    if %num% gtr 0 ( goto upload ) else goto param

:stringLenth
    ::字符串长度计算子程序 
    set thestring=%~1
    ::@echo 字符串 %thestring%
    ::参数%1 为字符串"%str%"，%~1则去掉"%str%"的双引号。 
    if not defined theString (
       ::设置如果字符串为空，长度为0
        set Return=0
        set %2=0
        goto :eof
    )
    set Return=0

:stringLenth_continue
    set /a Return+=1
    set thestring=%thestring:~0,-1%
    ::偏移量为 1,截取倒数第1位前的所有字符
    if defined thestring goto stringLenth_continue
    ::参数%2 为返回变量 num的名称，不能含空格或特殊字符
    if not "%2"=="" set %2=%Return%
    goto :eof 

:upload  
::读取config.ini配置文件
for /f  "tokens=1,2 delims==" %%b in (%~dp0conf\conf.ini) do (
  if "%%b"=="user" set user=%%c
  if "%%b"=="pass" set pass=%%c
  if "%%b"=="dbsid" set dbsid=%%c 
)
::循环处理卡交易文件
for /r %data_path% %%a in (R92EXTCI*) do (
 set houzui=%%~xa
 ::备份数据文件，先判断时候有后缀，由于sqlldr默认数据文件默认后缀为.dat
 if not defined houzui (
    copy %%a %%a.dat && move %%a %~dp0databak\prda\%%~na
    ::获取第一行的数据，用于后续截取一些数据
    set /p firstline=<%%a.dat
    set datafile=%%a.dat
 ) else (
    set /p firstline=<%%a
    set datafile=%%a
 )
 ::头记录编号1
 set headno1=!firstline:~28,16!
 ::头记录编号2
 set headno2=!firstline:~54,11!
 ::不带后缀文件名
 set filename=%%~na

 ::获取应该导入总条数
 for /f "delims=" %%i in (!datafile!) do set lastline=%%i
 set totalnum=!lastline:~60,3!

  ::实现动态把控制文件的headno1val、headno2val、filenameval替换为实际值
  REM for /f "delims=" %%i in ('type %~dp0ctl\prda\garance_prda_sqlldr_sample.ctl') do (
      REM set str=%%i
      REM 变量嵌套替换命令
      REM call, set str=%%str:headno1val=!headno1!%%
      REM call, set str=%%str:headno2val=!headno2!%%
      REM call, set str=%%str:filenameval=!filename!%%
      REM echo !str!
      REM echo !str!>> %~dp0ctl\prda\garance_prda_sqlldr.ctl
  REM )

 ::相关数据插入数据库表 
 sqlplus !user!/!pass!@!dbsid! @%~dp0sql\prda\upload_pada_head.sql !filename! !headno1! !headno2! !totalnum!

 sqlldr userid=!user!/!pass!@!dbsid! control=%~dp0ctl\prda\garance_prda_sqlldr.ctl data=!datafile! log=%~dp0log\garance_prda.log bad=%~dp0bad\garance_prda.bad

 ::开始进行检查
 if exist %~dp0data_temp\prda\checkexist.txt del %~dp0data_temp\prda\checkexist.txt
 %~dp0bin\sqluldr264.exe user=!user!/!pass!@!dbsid! sql=%~dp0sql\prda\garance_prda_checkexist.sql  file=%~dp0data_temp\prda\checkexist.txt head=no>nul
 for /f %%n in (%~dp0data_temp\prda\checkexist.txt) do ( 
    set isexist=%%n
 )

 ::计算实际导入笔数
 if exist %~dp0data_temp\prda\checknum.txt del %~dp0data_temp\prda\checknum.txt
 %~dp0bin\sqluldr264.exe user=!user!/!pass!@!dbsid! sql=%~dp0sql\prda\garance_prda_checknum.sql  file=%~dp0data_temp\prda\checknum.txt head=no>nul
  for /f %%n in (%~dp0data_temp\prda\checknum.txt) do ( 
    set checknum=%%n
 )

 if exist %~dp0data_temp\prda\checkexist.txt (
      if exist %~dp0data_temp\prda\checknum.txt (
         if !isexist! gtr 0  (
           echo "=================文件信息已经存在，请核对后再重新导入======================="
         ) else (       
                sqlplus !user!/!pass!@!dbsid! @%~dp0sql\prda\appendprda.sql
                echo "=================ATM文件%%a已经导入======================="         
                if !checknum! neq !totalnum! (
                    echo "=================应导入!totalnum!,实际导入!checknum!,请核对!!======================="
                )else (
                   echo "=================成功导入笔数!checknum!======================="
                )
         )
      ) else (
         echo =================检查成功导入笔数时出错=======================  
      ) 
 ) else (
     echo =================检查文件信息是否已存在时出错=======================  
 )
::等待一会，后续进行下个文件的解析导入
 ping 127.0.0.1 -n 8 >nul

)
pause